Populating a table from a selection in a drop down menu?

[lewis92]

Hi I populate a drop down menu like this:

<select name="mySelect"> 
        <?php $result= mysql_query('SELECT DISTINCT Year FROM MonthlySales'); ?> 
        <?php while($row= mysql_fetch_assoc($result)) { ?> 
            <option value="<?php echo htmlspecialchars($row['Year']);?>"> 
                <?php echo htmlspecialchars($row['Year']); ?> 
            </option> 
        <?php } ?> 
    </select> 

Then build a HTML table like this:

<?php

    while( $row = mysql_fetch_row($results) )   {
        echo "<tr>";
        foreach($row as $cell)        //Display all data in this row of user data...
        echo "<td>$cell</td>";
        echo "</tr>\n";
    }

    echo "</table>";
    mysql_close($conn);

    ?>

My connection details look like so:

 <?PHP
        $conn = mysql_connect('localhost', 'root', 'root');
        @mysql_select_db('localDB');

        $query="SELECT * FROM MonthlySales";
        $results = mysql_query ( $query, $conn);

        echo "<table border='1'>";
        echo "<tr><td>ID</td><td>ProductCode</td><td>Month</td><td>Year</td><td>SalesVolume</td></tr>";
    ?>   

Now I want to only display the rows in the table with the year that the user has selected from my drop down box, I'm not sure how to link both the drop down box and the table together, can someone explain please.

[diafol]

For the select:

<?php 
$result= mysql_query('SELECT DISTINCT Year FROM MonthlySales');
$sel = "\n<select name=\"mySelect\">"; 
while($row= mysql_fetch_assoc($result)) {
      $op = htmlspecialchars($row['Year']);
      $sel .= "\n\t<option value=\"$op\">$op</option>"; 
}
$sel .= "\n</select>\n"; 
?>

Then just place the $selvariable into your html table

[lewis92]

Thank you for your response, it's appreciated.

I don't follow what I'm doing with that new variable though, does it go in my while loop?

edit: I'm having trouble implementing that, my drop down box is empty once I add that?

[diafol]

The dropdown will need to be submitted from its form. You pick this up in a different page (the form handler) or the same page if you must by the variable $_POST['mySelect'].

Clean this variable with mysql_real_escape_string() or intval() if you're passing an integer (year) and then use it in your SQL that retrieves the html table for that selected year.

Your form (e.g.):

<form method="post">
<label for="mySelect">Choose the year:</label>
<?php echo $sel;?>
<input type="submit" value="Filter Results" name="submityear" id="submityear" />
</form>

[lewis92]

Thanks, I'm guessing I use the WHERE command in my sql to return the rows with that certain year. Can I assign

$_POST['mySelect'] 

to another value to be passed into that SQL, sorry for all the questions, I don't normally work with sql and php.

[diafol]

Yes as I said you must clean the var first.