Building a html table dynamically and filtering it?

[lelliott]

My db is called localDB, and the table in that db is called MonthlySales. I want to display the table in a table in php. But to be able to filter the table by the column year, so if 2000 is selected only the values with 2000 are shown. Is there also a better way to populate the drop down menu?

My script looks like:

[php]<form action=’<?php echo $_SERVER['PHP_SELF']; ?>’ method=‘post’ name=‘form_filter’ >

<select name="value"> 
    <option value="all">All</option> 
    <option value="2000">2000</option> 
    <option value="2001">2001</option> 
</select> 
 
<input type='submit' value = 'Filter'> 

</form> 


<?php
$link = mysql_connect('localhost', 'root', 'root');
if (!$link) {
die('Could not connect: ' . mysql_error());
}

 $db_selected = mysql_select_db('localDB', $link);
if (!$db_selected) {
    die (mysql_error());
}

// process form when posted 
if(isset($_POST['value'])) 
{ 
	if($_POST['value'] == '2000') 
	{  
		$query = "SELECT * FROM MonthlySales WHERE Year='2000'";   
	}   
	elseif($_POST['value'] == '2001') 
	{   
		$query = "SELECT * FROM MonthlySales WHERE Year='2001'";   
	} 
	else 
	{   
		$query = "SELECT * FROM MonthlySales";   
	}   
	$sql = mysql_query($query);   
	 
	while ($row = mysql_fetch_array($query))
	{  
		$Id = $row["Id"];  
		$ProductCode = $row["ProductCode"];  
		$Month = $row["Month"];  
		$Year = $row["Year"]; 
		$SalesVolume = $row["SalesVolume"]; 
			 
		echo "<tr>";
		echo "<td>" . $row['Id'] . "</td>";
		echo "<td>" . $row['ProductCode'] . "</td>";
		echo "<td>" . $row['Month'] . "</td>";
		echo "<td>" . $row['Year'] . "</td>";
		echo "<td>" . $row['SalesVoulme'] . "</td>";
		echo "</tr>";
	} 
	mysql_close($con);  
} 	
?>[/php]

[ErnieAlex]

Well, you didn’t mention how many years are in your drop-down. But, one easy way would be to pull all the years actually from the database itself. You pull the data with a query, something in general like:

SELECT DISTINCT year from MonthlySales

This pulls all of the years but basically just one of each. (2001,2002,2003,etc)
Then, you can load the drop-down with a while or foreach…

Hope that helps… Good luck!