Dropdown Menu to Query Database

Question

First of I apologize if this is a dumb question - I'm just starting to pick up my php/mysql skills. I'm making a dropdown form with 3 dropdowns. I want to be able to trigger a query from the form. You select Part Type, Make, Model hit submit and a table of results is displayed.

I have my form populated with 3 arrays and when you hit submit, I can echo the key of each selected item to the page:

    echo '<form action="dbBrowse.php" method="post">';

    $mak = array (0 => 'Make', 'Ford', 'Freightliner', 'Peterbilt', 'Sterling', 'Mack', 'International', 'Kenworth', 'Volvo');
    $mod = array (0 => 'Model', 'Argosy', 'Midliner');
    $p = array (0 => 'Part', 'Radiator', 'Charge Air Cooler', 'AC');                        



    echo '<select name="drop1">';
    foreach ($p as $key => $value) {
    echo "<option value=\"$key\">
    $value</option>\n";

    }


    echo '</select>';




    echo '<select name="drop2">';
    foreach ($mak as $key => $value) {
    echo "<option value=\"$key\">
    $value</option>\n";



    }

    echo '<select/>';



    echo '<select name="drop3">';
    foreach ($mod as $key => $value)  {
    echo "<option value=\"$key\">
    $value</option>\n";



    }

    echo '<select/>';
    echo '</form>';


            //echo keys of selection
    echo $_POST['drop1'];
    echo "<br />";
    echo $_POST['drop2'];
    echo "<br />";
    echo $_POST['drop3'];
            //these echo something like 1, 1, 3 etc. to my page

Where I'm getting lost is I'm looking to take the selected options and insert them into a query something like this:

     $dropSearch = mysql_query('SELECT * FROM parts WHERE part= "$partTypeVar" . AND  WHERE make = "$makeTypeVar" . AND WHERE model = "$modelTypeVar"');    

            $partTypeVar being the corresponding value to the key that is being returned from the array.

I'm driving myself crazy trying to figure out how to make that happen. Eventually I want to expand this further but just being able to create a mysql statement with the values selected would make my day right now. I understand the concept of what needs to happen but I'm unsure of how to accomplish it. Any help or pushes in the right direction would be greatly appreciated.

Possible duplicate: [How to make a cascading drop-down list in PHP using jQuery](http://stackoverflow.com/questions/6857287/cascading-drop-down-list) — Robert Harvey, Jun 04 '12 at 19:51
Where do you specify the variable's $drop1, $drop2, $drop3 ? — stUrb, Jun 04 '12 at 19:55

Zuul · Answer 1 · 2012-06-04T20:06:15.887

If I've understood your question, when the form is submitted, you want to query the database with the selected values.

Example using AND:

// Prepare the Query
$query = sprintf(
           "SELECT * FROM parts WHERE part='%s' AND make='%s' AND model='%s'",
           mysql_real_escape_string($_POST['drop1']),
           mysql_real_escape_string($_POST['drop2']),
           mysql_real_escape_string($_POST['drop3'])
         );

// Execute the Query
mysql_query($query);

This will select all rows from the table parts that match those three values.

Example using OR:

// Prepare the Query
$query = sprintf(
           "SELECT * FROM parts WHERE part='%s' OR make='%s' OR model='%s'",
           mysql_real_escape_string($_POST['drop1']),
           mysql_real_escape_string($_POST['drop2']),
           mysql_real_escape_string($_POST['drop3'])
         );

// Execute the Query
mysql_query($query);

This will select all rows from the table parts that match any of those three values.

You can read more about this:

mysql_query

mysql_real_escape_string

MySQL 5.6 Reference Manual :: 12 Functions and Operators :: 12.3 Operators

score 0 · Accepted Answer · answered Jun 04 '12 at 19:58

0

First of all, you have to delete the . char in your SQL query, there's no need to use it for now and of course assign the correct values to the variables.

$partTypeVar = mysql_real_escape_string($_POST['$drop1']);
$makeTypeVar = mysql_real_escape_string($_POST['$drop2']);
$modelTypeVar = mysql_real_escape_string($_POST['$drop3']);

$dropSearch = mysql_query('SELECT * FROM parts WHERE part= "$partTypeVar" AND WHERE make = "$makeTypeVar" AND WHERE model = "$modelTypeVar"');

I am assuming that's the correct order of your variables.

I hope this help!

answered Jun 04 '12 at 19:58

Vic Abreu

500
4
12

thanks for the help. I made some adjustments and got a basic version of what Im trying to do. Man, what do you think the best way to learn php/mysql? Just getting in and doing small projects? Thats what Ive started doing but I always run into things I want to do and questions that arent in a book or discussed in tutorial. Any suggestions? – watercolor12 Jun 04 '12 at 23:09
Just read tutorials and best practices. Ask, ask and ask once and once again. :P When you have problems with some functions, just go to http://php.net/ and read about it. – Vic Abreu Jun 05 '12 at 04:27

FilhoDumMocho · Answer 3 · 2014-01-08T17:29:12.177

<select name="myFilter">
<option value="Chooseafilter" name="default">Choose a filter...</option>
<option value ="Lastname" name="opLastName">Last Name</option>
<option value="Firstname" name="opFirstName">First Name</option>
<select>
<li><!--TEXT SEARCH INPUT--><input type="text" name="search_filter" /></li> 
...
dbconnection();#assume that all connection data is here        
...
$filter = $_POST['myFilter']; #
...

switch($filter)
    {
        case "Lastname":
        $selectedoption = "profile_name";
        break;

        case "Firstname":
        $selectedoption="profile_first_name";
        break;

        case "Chooseafilter":
        $selectedoption = "";
        break;  
    }           

$result = pg_query($db_con, "SELECT * FROM profile WHERE ".$selectedoption." LIKE'%$_POST[search_filter]%'"); 
$row = pg_fetch_assoc($result);  
if (isset($_POST['submit']))
    {   
        while($row = pg_fetch_assoc($result))
        {
        echo"<ul>  
                <form name='update' action='' method='POST'>
                <li>Guest Last Name:</li>
                <li><input type='text' name='profile_name_result' value='$row[profile_name]' /></li>  
                <li>Guest First Name:</li>
                <li><input type='text' name='profile_first_name_result' value='$row[profile_first_name]' /></li>  
                <li><input type='submit' value='Update' name='update'></input></li>
    ...

Dropdown Menu to Query Database

3 Answers3