Populate Drop Down Menus Through PHP From MySql

Question

I wonder whether someone may be able to help me please.

I have a MySql database containing several tables. My question revolves around the use of data from three of these, 'userdetails', 'detectors' and 'searchheads'.

What I would like to do is to populate a drop down menu on my HTML form that shows the 'detectors' that are user specific and, in turn, another drop down menu that again is user specific but also only shows the 'searchhead' applicable to the 'detector' selection made in the first drop down menu.

All three tables have a 'userid' field and the tables for the 'detectors' and 'search heads' have a 'detectorid' field.

Could someone perhaps please show me how I would go about setting this up because I must admit I haven't a clue where to start.

Many thanks and regards

Chris.

UPDATED JS CODE

<script type="text/javascript" language="javascript">
            $(document).ready(function(){     
            $.fetch_data    = function (what) {         
            var send    = {             
            "what"  : what,             
            "data"  : $("select[name=detectors]").val()         
            };         
            $.post ("dropdownmenus.php", send, function(data){             
            $("select[name=" + what + "]").html(data);         
            });     
            };     
            $.fetch_data ("detectors");     
            $("select[name=detectors]").live("change", function(){         
            // reset the searchhead as new data will be implemented         
            $("select[name=detectorsearchheads]").html("");         
            $.fetch_data ("detectorsearchhead");     
            }); 
            }); 
        </script> 

UPDATED PHP CODE

<?php     
$request    = array (        
"detector"      => @$_POST["detector"],         
"detector"      => @$_POST["detector"]     );     
// clean $request.     
$build_query    = "select * from " . $request["what"] . " "                     
. "where userid = \"" . $user_id . "\"";    
$build_results  = mysql_query ($build_query);     
$return_results = "";     
foreach ($build_results as $index => $data) {         
$return_results .= "<option value=\"detector" . $data["id"] . "\">" . $data["text"] . 
"</option>";     }     
echo $return_results; ?>

Answer 1

This is more Javascript than complex MySQL queries.

form.php

This page is where your form is located. I have created an Ajax function which grabs necessary data. I haven't completed everything but I gave you an outline of what you need to complete. The data collected from the select inputs are transferred via an ajax post request in jQuery. The data is then manipulated and formatted into html data which can be understood by the select input.

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script> 
<script>
$(document).ready(function(){
    $.fetch_data    = function (what) {
        var send    = {
            "what"  : what,
            "data"  : $("select[name=detectors]").val()
        };
        $.post ("url/to/ajax.php", send, functio(data){
            $("select[name=" + what + "]").html(data);
        });
    };
    $.fetch_data ("detectors");
    $("select[name=detectors]").live("change", function(){
        // reset the searchhead as new data will be implemented
        $("select[name=searchhead]").html("");
        $.fetch_data ("searchhead");
    });
});
</script>
<form>
    <label>Detectors</label>
    <select name="detectors">

    </select>
    <label>Search Heads</label>
    <select name="searchead">

    </select>
</form>

url/to/ajax.php

Here you will have to make adjustments to how your data is manipulated. You could use a PHP switch depending on the what variable, which would change the query depending on what's requested.

<?php
    $request        = array (
        "what"      => @$_POST["what"],
        "data"      => @$_POST["data"]
    );
    // clean $request.
    $build_query    = "select * from " . $request["what"] . " "
                    . "where userid = \"" . $user_id . "\"";
    $build_results  = mysql_query ($build_query);
    $return_results = "";
    foreach ($build_results as $index => $data) {
        $reurn_results .= "<option value=\"" . $data["id"] . "\">" . $data["text"] . "</option>";
    }
    echo $return_results;
?>