Select dependent PHP AJAX MYSQL

0

I'm working with 2 select, the second depends on the data that is selected in the first one. I have not managed to make it work, so far the only thing it does is load the data corresponding to each select but separately.

**//Carga los datos de los departamentos en el Select//**
<div>
  <select class="form-control" id="idDep" name="idDep" required>
    <option value="">-- Escoja una opción --</option>
      <?php foreach (departamentoData::getAll() as $departamento):?>
      <option value="<?php echo $departamento->idDep; ?>"><?php echo $departamento->nombre; ?></option>
      <?php endforeach; ?>
  </select>
</div>

**//Carga los datos de los municipios en el Select//**
<div>
  <select class="form-control" id="idMun" name="idMun" required>
    <option value="">-- Escoja una opción --</option>
      <?php foreach (municipioData::getAll() as $municipio):?>
      <option value="<?php echo $municipio->idMun; ?>"><?php echo $municipio->nombre; ?></option>
      <?php endforeach; ?>
  </select>
</div>

//====================================================//

//Esta función es la que me permite cargar los departamentos
<?php
class departamentoData {
	
	public static $tablename = "departamento";

	public function departamentoData(){
	}

	public static function getAll(){
		$sql = "select * from ".self::$tablename;
		$query = Executor::doit($sql);
		return Model::many($query[0],new departamentoData());
	}
}
?>

//====================================================//

//Esta función es la que me permite cargar los municipios
<?php
class municipioData {
	
	public static $tablename = "municipio";

	public function municipioData(){
	}

	public static function getAll(){
		$sql = "select * from ".self::$tablename;
		$query = Executor::doit($sql);
		return Model::many($query[0],new municipioData());
	}
}
?>

//====================================================//

//Scrip para cargar los datos del select municipios

<script language="javascript">
$(document).ready(function(){
   $("#idDep").change(function () {
           $("#idDep option:selected").each(function () {
            idDep = $(this).val();
            $.post("municipioData.php", { idDep: idDep }, function(data){
                $("#idMun").html(data);
            });            
        });
   })
});
</script>

The problem is that I load all the municipalities so select a department, I do not know what part is wrong, or if the code to load the municipalities is correct.

Can someone give me a little help?

In the municipalityData.php file, this is.

<?php
class municipioData {
	
	public static $tablename = "municipio";

	public function municipioData(){
	}

	public static function getAll(){
		$sql = "select * from ".self::$tablename;
		$query = Executor::doit($sql);
		return Model::many($query[0],new municipioData());
	}
}
?>

The following image is how I have the structure of the project.

Updating the question

I think the problem is in the file buscar.php because I do not know how it is executed or how the request is made to the database, should it include or require something? so that you can connect to the database and can you find the municipality of the selected department?

Code file search.php

<?php
if (isset($_POST["idDep"])){
    /*Aquí si hace falta habrá que incluir la clase municipios con include*/
    $intId=$_POST["idDep"];

    /*Usamos un nuevo método que habremos creado en la clase municipio: getByDepartamento*/
    $json=json_encode(municipioData::getByDepartamento($intId));        
}else{
    $json=json_encode(array('error'=>'No se recibió un valor de id departamento para filtar'));     
}
    print_r($json);
    ?>
    
asked by Horus 09.01.2018 в 22:07
source

1 answer

1

Since the comments show that you do not know how Ajax works, I have written this somewhat detailed answer. From the outset I will say that it is not easy to respond to your problem, among other things because you ask for data from a model and you do not know how the model returns those data. Therefore, you can not know how to treat these data when you receive them. The normal thing is to use the JSON format, that's why I wrote the code assuming that your model returns an array of data that are converted to JSON for the answer.

I will try to explain part by part.

1. File where select dependent will be

1.1 PHP / HTML code

The departments will be loaded by default, as you have it now:

/*Carga los datos de los departamentos en el Select*/
<div>
  <select class="form-control" id="idDep" name="idDep" required>
    <option value="">-- Escoja una opción --</option>
      <?php foreach (departamentoData::getAll() as $departamento):?>
      <option value="<?php echo $departamento->idDep; ?>"><?php echo $departamento->nombre; ?></option>
      <?php endforeach; ?>
  </select>
</div>

Then, there will be another select where the municipios will be displayed, depending on the departamento that has been selected.

VERY IMPORTANT: That select will not carry data by default, it will be filled based on the department, searching the data on the server via Ajax.

/*CargaRÁ los datos de los municipios en el Select*/
<div>
  <select class="form-control" id="idMun" name="idMun" required>
    <option value="">-- Escoja una opción --</option>
  </select>
</div>

1.2. Javascript / jQuery code

This code will listen to the changes of select of departments and will send an Ajax request to find the municipalities of that department.

VERY IMPORTANT: In the example, the Ajax request will be sent to a file that I have called buscar.php . It is an auxiliary file that will be responsible for creating an instance of your class Municipios if necessary, to invoke the method that brings the municipalities filtered by departments.

$(function() {

    $( "#idDep" ).on( "change", function() {
        var intId=$(this).val();
        var datos = { idDep: intId };
        var url='buscar.php';

        var request = $.ajax
        ({
            url: url,
            method: 'POST',
            data: datos,
            dataType: 'json'
        });

        /*
            *Esta es una parte muy importante, aquí se  tratan los datos de la respuesta
            *se asume que se recibe un JSON correcto con dos claves: una llamada id_municipio
            *y la otra llamada municipio, las cuales se presentarán como value y datos de cada option 
            *de tu select PARA QUE ESTO FUNCIONE TU MODELO DEBE SER CAPAZ DE DEVOLVER UN JSON VÁLIDO
            *dado que no tengo control de lo que ocurre en tu modelo 
            *ni sé que tipo de dato devuelve ni como lo devuelve, no puedo hacer más que advertirlo aquí
        */
        request.done(function( respuesta ) 
        {
            if(!respuesta.hasOwnProperty('error')){
                $.each(respuesta, function(k, v) {
                    $('#idMun').append('<option value="' + v.id_municipio + '">' + v.municipio + '</option>');
                    });
            }else{

                //Puedes mostrar un mensaje de error en algún div del DOM
            }
        });

        request.fail(function( jqXHR, textStatus ) 
        {
            alert( "Hubo un error: " + textStatus );
        });

    });

});

3. File code buscar.php

It is the file that will serve as an auxiliary to the Ajax request.

if (isset($_POST["idDep"])){
    /*Aquí si hace falta habrá que incluir la clase municipios con include*/
    $intId=$_POST["idDep"];

    /*Usamos un nuevo método que habremos creado en la clase municipio: getByDepartamento*/
    $json=json_encode(municipioData::getByDepartamento($intId));        
}else{
    $json=json_encode(array('error'=>'No se recibió un valor de id departamento para filtar'));     
}
    print_r($json);

4.Code of the class municipioData modified

The municipality class with a method that seeks to apply a filter

<?php
class municipioData {

    public static $tablename = "municipio";

    public function municipioData(){
    }

    public static function getAll(){
        $sql = "select * from ".self::$tablename;
        $query = Executor::doit($sql);
        return Model::many($query[0],new municipioData());
    }


    public static function getByDepartamento($intId){
        /*
            *OJO: Aquí asumo que en la tabla municipio hay un campo llamado id_departamento
            *que sirve para identificar a que departamento pertenece el municipio
            *si no se llama así debes cambiarlo por como se llame
            *Asumo también que hay un campo llamado id_municipio y otro llamado municipio
        */
        $sql = "select id_municipio, municipio from ".self::$tablename. " where id_departamento=."$intId;
        $query = Executor::doit($sql);

        /*
            *OJO: No sé lo que retorna esta llamada, 
            *es importante saberlo para tratar los datos en la respuesta
            *asumiré que el modelo devuelve un array con los datos
        */
        return Model::many($query[0],new municipioData());
    }




}
?>  
  

NOTE: This query: $sql = "select id_municipio, municipio from ".self::$tablename. " where id_departamento=."$intId; is vulnerable to   SQL Injection Once the code works, you should make use of   queries prepared to avoid that vulnerability. I have written it like this   for reasons of brevity and because the answer tries to explain the   other aspects already mentioned above. If we enter the field of   prepared queries now would make you more mess.

    
answered by 10.01.2018 в 02:17