Jump to content

PHP/SQL code requiring action to refresh a double drop down select option


Dancode

Recommended Posts

I have a nested drop down choice where, basically, the second select input should update its content depending on first selected option.

I get it working ok when it open the page but did not exactly understood how to update the variable once the page is loaded. As a matter of fact if the last option of the SQL table in the first option match the name of one or more record in the second input those may correctly be selected but - changing the option - the second input box doesn’t update.

I tried some java but hadn’t succeeded, also thought about AJAX. Can any one help me with that?

 

My code is following, the $namev variable is the one, I guess, should need some correct action to be handled toward second input box:

 

 

 

<html>

 

                <head>

               

…..

                                              

                                              

                                </head>

                               

                               

                <body>

               

…..

                               

                                               <form>

                                              

…….

                                                              

             <div class="divFLOATleft">

              <label>voce</label>
                     <?php
                           $result = $conn->query("select id_voce,id_gruppo_voce,gruppo_voce,voce FROM voci");

                           echo "<select name='voce'><option value='' disabled selected>scegli voce</option>";

                               
                               while ($row = $result->fetch_assoc()) {                                                                                                                                                unset($idv, $namev);

                                                                                                                                                                                                                                                                                $idv = $row['id_voce'];

                                                                                                                                                                                                                                                                            $namev = $row['voce'];

                                                                                                                                                                                                                                                                            $id_gv = $row['id_gruppo_voce'];

                                                                                                                                                                                                                                                                            $id_v = $row['gruppo_voce'];                                                                                                                                                                                                                                                             

                                                                                                                                                                                                                                                                            echo '<option value="'.$idv.'">'.$namev.'</option>';

                

                                                                                                                                                                                                                                                            }
         echo "</select>";

 

 

 

                echo"</div>";
                                            
               echo"<div class='divFLOATleft'>";
                                                                                                                                             echo"<label>categoria</label>";

                                                                                                                             

                                                                                                             
                 $result = $conn->query("select id_voce,id_gruppo_voce,gruppo_voce,voce FROM voci WHERE gruppo_voce LIKE '$namev'");

                 echo "<select name='voce'><option value='' disabled selected>scegli categoria</option>";

                               

                      while ($row = $result->fetch_assoc()) {

 

                                                                                                                                                                                                                                                                           unset($id, $name);

                                                                                                                                                                                                                                                                            $idc = $row['id_voce'];

                                                                                                                                                                                                                                                                            $namec = $row['voce'];

                                                                                                                                                                                                                                                                            $id_gc = $row['id_gruppo_voce'];

                                                                                                                                                                                                                                                                            $id_c = $row['gruppo_voce'];

                                                                                                                                                                                                                                                                            echo '<option value="'.$idc.'">'.$namec.'</option>';

                

                                                                                                                                                                                                                                                            }

                       echo "</select>";
                                                                                             ?>

 

                                                               </div>                                                                 

 

                                                                              

                                               </form>

…..

               

               

                </body>

 

</html>

 

 

 

 Thanks to anyone helping with that!

 

 

 

 

Link to comment
Share on other sites

Here's an example for you.

DATA

 TABLE: country                               TABLE: city
+------------+--------------+                 +---------+------------+------------+
| country_id | country_name |                 | city_id | cityname   | country_id |
+------------+--------------+                 +---------+------------+------------+
|          1 | England      |                 |       1 | London     |          1 |
|          2 | Scotland     |                 |       2 | Birmingham |          1 |
|          3 | Wales        |                 |       3 | Manchester |          1 |
+------------+--------------+                 |       4 | Edinburgh  |          2 |
                                              |       5 | Glasgow    |          2 |
                                              |       6 | Aberdeen   |          2 |
                                              |       7 | Cardiff    |          3 |
                                              |       8 | Swansea    |          3 |
                                              |       9 | Llandudno  |          3 |
                                              +---------+------------+------------+

example1.php

<?php
include('db_inc.php');
$db = pdoConnect("test");                // connect to "test" database

$ctrycode = $_GET['country'] ?? '';
$citycode = $_GET['city'] ?? '';


/***************************************************
** if we have received an ajax request, process it
****************************************************/    
        if (isset($_GET['ajax'])) {
            switch ($_GET['ajax']) {
                case 'city':
                    exit(cityOptions($db, $ctrycode));
            }
        }
        

function countryOptions($db, $current='')
{
    $result = $db->query("SELECT country_id, country_name FROM country");
    $opts = "<option value=''>- Choose country -</option>\n";
    foreach ($result as $row) {
        $sel = $row['country_id']==$current ? 'selected' : '';
        $opts .= "<option $sel value='{$row['country_id']}'>{$row['country_name']}</option>\n";
    }
    return $opts;
}

function cityOptions($db, $country, $current='')
{
    $stmt = $db->prepare("SELECT city_id
                               , cityname 
                          FROM city
                          WHERE country_id = ?");
    $stmt->execute([$country]);
    $opts = "<option value=''>- Choose city -</option>\n";
    foreach ($stmt as $row) {
        $sel = $row['city_id']==$current ? 'selected' : '';
        $opts .= "<option $sel value='{$row['city_id']}'>{$row['cityname']}</option>\n";
    }
    return $opts;
}
?>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="generator" content="PhpED 18.0 (Build 18044, 64bit)">
<meta name="creation-date" content="11/03/2018">
<title>Example</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script type='text/javascript'>
    $().ready( function() {
        
         $("#country").change( function() {
             $.get (
                "",                                              // blank url - send to same page
                { "ajax": "city",
                  "country": $(this).val() 
                },
                function(resp) {
                    $("#city").html(resp)
                },
                "TEXT"
             )
         })
    })
</script>
</head>
<body>
<form>
     Country <select name='country' id='country'>
        <?=countryOptions($db, $ctrycode)?>
        </select>
     <br>   
     City  <select name='city' id='city'>
        <?=cityOptions($db, $ctrycode, $citycode) ?>
        </select>
     <br>
     <input type="submit" name="btnSub" value="Submit">
</form>
</body>
</html>

 

Link to comment
Share on other sites

Barand, once againg your explaination are great, I just went out the whole W3C testing and arrangement but your way is clearly good!!

Thanks to Requinix too, I already had a look to https://www.w3schools.com/js/js_ajax_intro.asp and particularly to https://www.w3schools.com/php/php_ajax_database.asp

 

It seems to work ok, now just have to nest other few input boxes.

 

Do you think there is a solution to hide the dropdown item if no match are returned from SQL query?

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.