picaluga picaluga - 7 months ago 53
PHP Question

Get value from dropdown list to use in MySQL query

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.

Answer

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>