1,105,625 Community Members

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

lewis92
Newbie Poster
3 posts since Apr 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
Unverified Member
 
0
 

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.

Member Avatar
diafol
Where are my eyes?
12,977 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,848 [?]
Skill Endorsements: 92 [?]
Moderator
Featured
Sponsor
 
2
 

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
Newbie Poster
3 posts since Apr 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
Unverified Member
 
0
 

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?

Member Avatar
diafol
Where are my eyes?
12,977 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,848 [?]
Skill Endorsements: 92 [?]
Moderator
Featured
Sponsor
 
2
 

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
Newbie Poster
3 posts since Apr 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
Unverified Member
 
0
 

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.

Member Avatar
diafol
Where are my eyes?
12,977 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,848 [?]
Skill Endorsements: 92 [?]
Moderator
Featured
Sponsor
 
0
 

Yes as I said you must clean the var first.

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: