SQL Query based on dropdown menu selection

Question

I create a dropdown like this:

<header>
    <h3 align="center"> Highway State </h3>
</header>
<section>
    <article>
        <hgroup>
        </hgroup>
        <p align="center">
        <form style="text-align:center" method="post" action="">

            <select name="dropdown1">
                <?php echo query1(); ?> 
            </select>

        <input type="submit" value="Submit" />
        </form> 
        </p>
    </article>

where query1() is

function query1()
{
    $myHTMLData = '';
    $c=connect();
    $myData=mysqli_query($c, "SELECT DISTINCT highway_state FROM highways_highway ORDER BY highway_state");
    while($record = mysqli_fetch_array($myData))
    {
        $myHTMLData .= '<option value="' . $record['highway_state'] . '">' . $record['highway_state'] . '</option>' ;
    }
    return $myHTMLData;
}

So what I am doing is populating the dropdown menu with states.

My question is now how do I do an sql query like SELECT ID, exit_name FROM highways_exit WHERE highway_ID = (SELECT ID FROM highways_highway WHERE highway_state = *dropdown1*)

The *dropdown1* is where I want the query to be based on the selection in dropdown1. How do I do this?

ALSO: How do I display this query on the webpage when the person hits the submit button?

My attempt at a solution (written in index.php)

    <header>
        <h3 align="center">Exit Name</h3>
    </header>
    <section>

        <table border='1' align="center">
            <tr>
            <th>Exit Name</th>
            <th>Exit ID</th>
            </tr>
<?php   
        $c=connect();
        $hstate = $_POST['dropdown1'];
        $hname = $_POST['dropdown2'];
        $hdir = $_POST['dropdown2pt5'];

        $result=mysqli_query($c, "SELECT exit_name, id FROM highways_exit WHERE highway_ID = (SELECT id FROM highways_highway WHERE highway_state = ('" . $hstate . "') AND highway_name = ('" . $hname . "') AND highway_dir = ('" . $hdir . "')) ");  
            while($row = mysqli_fetch_array($result))
            {
                $arrexit = $row['exit_name'];
                $arrid = $row['id'];
                echo "<tr><td>$arrexit</td><td>$arrid</td></tr>";
            }
  ?>
        </table>

    </section>
    <footer>
        <f1 align="center"></f1>
    </footer>

Is this correct for using menu items? This and the menu object is all on index.php, is there a way to get this to work such that it will take in the menu items and produce this table on refresh/submit?

score 2 · Accepted Answer · answered Apr 26 '13 at 01:18

2

You can get the value by using $_POST['dropdown1'] Then you can use this to query the db.

answered Apr 26 '13 at 01:18

ysrb

6,563
2
27
29

so would something like the following work? $myVal= $_POST['dropdown1']; newquery($myVal); Then function newquery($myval) { $myHTMLData = ''; $c=connect(); $myData=mysqli_query($c, "SELECT ID, exit_name FROM highways_exit WHERE highway_ID = (SELECT ID FROM highways_highway WHERE highway_state = $myVal)"); } – Kyle Grage Apr 26 '13 at 01:24
You can do something like $sql = "SELECT ID, exit_name FROM highways_exit WHERE highway_ID = (SELECT ID FROM highways_highway WHERE highway_state = '" . $_POST['dropdown1'] + "'"; But please be aware of SQL injection. You need to sanitize the input before injecting it to the query. – ysrb Apr 26 '13 at 01:29
Is there a reason that 'dropdown1' would cause an injection if it is simply a dropdown menu populated by the database itself? – Kyle Grage Apr 26 '13 at 01:34
Malicious users can change the posted values (as easy as using firebug) and post. – ysrb Apr 26 '13 at 01:35
Have a look at http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php and http://stackoverflow.com/questions/4461759/parameterized-queries – ysrb Apr 26 '13 at 01:36

SQL Query based on dropdown menu selection

1 Answers1