Home / Questions / 91486
Accepted
MNibor
Asked: 2020-07-30 05:42:02 +0800 CST 2020-07-30 05:42:02 +0800 CST

Two linked <select> (Country and Province) that are not linked

  • 772

I have two select, one I load the countries, in the other the provinces that "reacts" according to the country that was selected first. I recently consulted how to bring a country code from a DB and make that country select show me the country that comes from the DB, but I also have a province that I bring from the DB. Now, applying this load, the select of provinces "does not react" since it is not loaded according to the select selected by means of the database data. The select of countries 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 doesn't the js recognize the change in the select country when it takes the value from the DB?

QUESTION 2: How do I pass the code of the province that comes through BD so that it shows me the selected province?

php

1 Answers

  1. Best Answer

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

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

    You don't say it in your question, but I assume this code is in a different file thanprovincias.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 from elsewhere is involved. Not the case here. The query is already prepared , there is no risk of sending it directly through the method query:

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

    Therefore, this would suffice:

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

    2. The whole procedure whileyou 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 the HTML you have a select whose id is provincias.

    The script, while it may work, is using deprecated code: $(document).ready(function() {...it is deprecated as of jQuery3. It is recommended to use $(function() {....

    Similarly, Ajax requests are recommended to have at least the ability to handle doneon success, and failon failure ( 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 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";

}
    • 2