Get value from dropdown list to use in MySQL query

Question

So I'm having this issue with geting a value from a dropdown list in HTML into a variable so that I can do the mysql query. This will be a little tricky to explain but I will do my best. (Do not be shy in correcting my english or my expressions so that the question becomes more concrete and easy to understand).

So I have this dropdown list that gets his values from a mysql query.

<td>Designação atual :</td> <td><select name="desig_act" id="desig_act">
<?php
while ($row2 = mysql_fetch_assoc($result4)) {
echo "<option size=30 value=".$row2['new_name_freg'].">".$row2["new_name_freg"]."</option>";
}   
?>
</select>

This "connects" with this query:

$sql4 = ("SELECT DISTINCT new_name_freg FROM freguesias WHERE codg_cc = '$pesq'");
$result4 = mysql_query($sql4, $link);

This query will populate the dropdown list with values. What I'm seeking to do is to populate another dropdown list. For examples I select a list of countrys, and when I select on country it should appear all it's citys in the other dropdown list.

I have been searching guys. Belive me I have.

P.s: Please do not get mad if I change the question a couple of times when I see that you guys show me a way to explain it better. Sorry if my english isn't perfect. Thank you guys for the help.

I was trying not to due to the fact that I never worked with either one of those. Is it possible to do it with javascript? Although I'm not familiarized with the language, I'm more confortable working on it. — picaluga, Dec 29 '15 at 10:33
don't use `mysql_*` functions as they are deprecated and in PHP 7.0 deleted use [mysqli](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php) instead. Also when handling user input use [prepared statements](http://stackoverflow.com/q/60174/5396496) — BRoebie, Dec 29 '15 at 10:34
I'm using them as some sort of a test. When I'm done with making all the functions and so on, I will change everything to mysqli but thank you for notifying me. — picaluga, Dec 29 '15 at 10:35
No problem but don't learn yourself something that is not safe because it will be harder to unlearn it after. But to get to the question, if I understand it correctly you are trying to get values out of the database and into an HTML select, right? — BRoebie, Dec 29 '15 at 10:38
I already populated a dropdown list with values. As you can see, I do a select to get the values from the database. I get them succesufully. But when I need to access a second time to the database so that, with the value from the first dropdown, I can query the database for the values to populate the second dropdown list. A bit confusing I know I'm sorry. — picaluga, Dec 29 '15 at 10:43
`$sql5 = ("SELECT DISTINCT old_name_freg FROM freguesias WHERE new_name_freg = '$desg'"); $result5 = mysql_query($sql5, $link);` — picaluga, Dec 29 '15 at 10:57

score 3 · Answer 1 · answered Dec 29 '15 at 10:56

You can do it with ajax and jquery. I try to write little example

<!-- index.php -->
<select name="desig_act" id="desig_act">
<?php while ($row2 = mysql_fetch_assoc($result4)): ?>
   <option value="<?=$row2['new_name_freg']?>"> 
      <?=$row2["new_name_freg"]?>
   </option>
<?php endwhile; ?>
</select>
<!-- select tag for countries -->
<select name="country" id="country"></select>

write a little script to return countries as json

<?php //ajax-countries.php
$link = mysql_connect(); // connect as usual
$query = ("SELECT * FROM countries");
$result = mysql_query($query, $link);
$json = array();
while ($row = mysql_fetch_assoc($result)) $json[] = $row;
echo json_encode($json);
?>

Then you can have some sort of script like:

// script.js
$("#desig_act").change(function(){
  $.getJSON( "ajax-countries.php", function( data ) {
    $.each( data, function(key, val) {
      $("#desig_act").append("<option val='" + key + "'>" + val + "</option>");
    });
});

I hope it can be useful

Thanks a lot for the help. There are somethings that I can surely use it that last script. I will guide my self by that script. Thank you a lot! — picaluga, Dec 29 '15 at 11:02
You are welcome. I suggest you 2 improvments: use the mysqli function (http://php.net/manual/en/book.mysqli.php) and replace the build of query like a simple string with prepare statements for better security. — gafreax, Dec 29 '15 at 11:13

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

1: Create a PHP script to return the data

Essentially just generate the value based off the $_GET input.

2: Create a json request in jquery

Calls the PHP file which will return the data and you will use that data to add more values to the select.

<?php
//Step 1 - The posted ajax data that will return our json request.
if(isset($_GET['fetchrow'])) //Is our ajax request called on page load? If yes, go to this code block
{
    //Other stuff like DB connection
    $pesq = mysql_escape_string($_GET['fetchrow']); //Put our variable as the variable sent through ajax
    $sql4 = ("SELECT DISTINCT new_name_freg FROM freguesias WHERE codg_cc = '$pesq'"); //Run our query
    $result4 = mysql_query($sql4, $link); //Please change from mysql_* to mysqli
    $data = array(); //The array in which the data is in
    while($row = mysql_fetch_assoc($result4)) //Look through all rows
    {
        array_push($data, $row); //Put the data into the array
    }
    echo json_encode($data); //Send all the data to our ajax request in json format.
    die; //Don't show any more of the page if ajax request. 
}

?>

<html>
    <head>
        <script type='application/javascript' src='https://cdnjs.cloudflare.com/ajax/libs/jquery/3.0.0-alpha1/jquery.min.js'></script> <!--Include jquery -->
        <script>
        //Step #2:
        //The jquery script calls ajax request on change of the first select
        $( "#desig_act" ).change(function() {
            $.getJSON('thisfilename.php', {fetchrow:$("#desig_act").val()}, function(data){ //Get the json data from the script above
                var html = '';
                var len = data.length;
                for (var i = 0; i< len; i++) { //Loop through all results
                    html += '<option value="' + data[i].new_name_freg + '">' + data[i].new_name_freg + '</option>'; // Add data to string for each row
                }
                $('#otherselect').html(html); //Add data to the select.
            });
        });
        
        </script>
    </head>
    <body>
    <!-- Your html code -->
    <td>Designação atual :</td> <td><select name="desig_act" id="desig_act">
    <?php
    while ($row2 = mysql_fetch_assoc($result4)) {
    echo "<option size=30 value=".$row2['new_name_freg'].">".$row2["new_name_freg"]."</option>";
    }   
    ?>
    </select>
    </td>
    <!-- The new select -->
    <select name='otherselect' id='otherselect'>
    </select>
    <!-- Rest of html code -->
    </body>
</html>

You sir were very helpfull. You really explained every step of the way. Thank you a lot for the help. I will ask you no more given that you have already helped me so much. Now it's up to me to make this work on my code. Thank you a lot. — picaluga, Dec 29 '15 at 11:22

Get value from dropdown list to use in MySQL query

2 Answers2

1: Create a PHP script to return the data

2: Create a json request in jquery