Two linked selections (Country and Province) that do not link

4

I have two selections, one I charge the countries, in the other the provinces that "reacts" according to the country that was selected first. Just consulted how to bring from a BD the code of a country and make that select countries show me the country that comes from the BD, but I also have a province that I bring from the BD. Now, applying that load, the "select" of provinces "does not react" since it is not loaded according to the selected select by means of the data of the BD. The country selection is loaded with:

<select id="pais" class="ui fluid search dropdown" name="pais">
        <option value="0"></option>
        <?php
            $conexion = new Conexion();
            $stmt = $conexion -> prepare("SELECT paiscod, paisnom FROM paises ORDER BY paisnom");
            $stmt->execute();

            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            if ( $row['paiscod'] == $pais ) { ?>
                <option value = "<?php echo $row['paiscod']?>" selected><?php echo ($row['paisnom'])?></option>
            <?php } else { ?>
                <option value = "<?php echo $row['paiscod']?>"><?php echo ($row['paisnom'])?></option>
            <?php }
            } 
        ?>
    </select>


    <select id="provincias" class="ui fluid search dropdown" name="selectProvincias">
        <option value=""></option>
    </select>



    $id_pais = $_POST['id_pais'];

    $conexion = new Conexion();
    $stmt = $conexion -> prepare("SELECT provincod, provinnom FROM provincia WHERE paiscod = :valor");
    $stmt->bindParam(':valor', $id_pais);
    $stmt->execute();

    if ($stmt->rowCount() > 0) {
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {   
            $html .= '<option value = "'.$row['provincod'].'">'. $row['provinnom'].'</option>';
        }
    }
    echo $html;


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

QUESTION 1: Why does not the js recognize the change in the country select when it takes the value from the BD?

QUESTION 2: How I pass the code of the province that comes by BD to show me the selected province?

    
asked by MNibor 29.07.2017 в 15:42
source

1 answer

2

I will allow myself to comment on some things about your code, in parts.

I wanted to tell you in comment, but space would not give me.

You do not say it in your question, but I guess this code is in a different file than provincias.php

Let's say the file is called paises.php .

Code in paises.php :

1. Do not prepare something already prepared

Prepared queries are used when data is involved from another party. It is not the case here. The query is already prepared , there is no risk of sending it directly using the query method:

$stmt = $conexion -> prepare("SELECT paiscod, paisnom FROM paises ORDER BY paisnom");
$stmt->execute();

Therefore, this would be enough:

$stmt =$conexion->query("SELECT paiscod, paisnom FROM paises ORDER BY paisnom");

2. The whole while procedure you have can be simplified like this:

//Crear un array con los datos
//PDO::FETCH_ASSOC  se usa con fetchAll, no con fetch
$arrDatos = $stmt->fetchAll(PDO::FETCH_ASSOC);

//Verificar si hubo datos. El uso de rowCount para esto es sólo un mito 
//en PDO basta con verificar la respuesta del fetchAll

if ($arrDatos)
{
    //Implementamos una forma más limpia de hacer las cosas
    //Una variable que recoja todo y vaya concatenado

    $strHtml='';
    foreach ($arrDatos as $row)
    {
        if ( $row['paiscod'] == $pais ) 
        { 
            $strHtml.='<option value = "'.$row["paiscod"].' selected>'.$row["paisnom"].'</option>';
        } else {
            $strHtml.='<option value = "'.$row["paiscod"].'>'.$row["paisnom"].'</option>';
        }
     }
     //Fuera del bucle imprimimos la variable concatenada
     echo $strHtml;

 }else{
       echo "No se encontraron países";
 }

3. The script must be in paises.php :

You can improve it, as has been said in comments. I imagine that in HTML you have a select whose id is provincias .

The script, although it may work, is using obsolete code: $(document).ready(function() {... is obsolete from jQuery3. It is recommended to use $(function() {... .

Similarly, Ajax requests are recommended to have at least the ability to handle done in case of success, and fail in case of error ( see here ).

  <script language="javascript">
    $(function() {
        $("#pais").change(function() {
            $("#pais option:selected").each(function() {
                id_pais = $(this).val();
                $.post("provincias.php", {
                    id_pais: id_pais
                }, function(data) {
                    $("#provincias").html(data);
                });
            });
        })
    });
    </script>

The request that is made through Ajax / jQuery must be directed to a different file. In this case it is the file indicated in the url of the request provincias.php .

Code in provincias.php :

$id_pais = $_POST['id_pais'];

$conexion = new Conexion();
$stmt = $conexion -> prepare("SELECT provincod, provinnom FROM provincia WHERE paiscod = :valor");
$stmt->bindParam(':valor', $id_pais);
$stmt->execute();

//Es un error común usar fetch combinado con 'PDO::FETCH_ASSOC'
//Así no se obtiene nada, debes usar fetchAll
//por eso no obtenías las provincias

//Como en países, almacenamos los datos en un arrego
$arrDatos=$stmt->fetchAll(PDO::FETCH_ASSOC))

//Desechamos el mito del rowCount, verificando los datos directamente
//como hicimos en países

if ($arrDatos)
{
    $strHtml="";
    foreach ($arrDatos as $row)
    {

        $strHtml .= '<option value = "'.$row['provincod'].'">'. $row['provinnom'].'</option>';
    }

    echo $strHtml;

}else{

    echo "No se encontraron provincias";

}
    
answered by 29.07.2017 / 18:03
source