Filter results in a table.

[lelliott92]

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:

 

<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);  
} 	
?>

[ignace]

Do not write your PHP code in your HTML, but organize them in functions and use those in your HTML. This separates your core app functionality from your HTML and makes it easier to edit/re-use later on.

 

functions.php

function get_monthy_sales_for_year($year, $mysql_link) {
    $sql = 'SELECT * FROM MonthlySales WHERE Year = %d';
    $sql = sprintf($sql, $year);
    return mysql_fetch_all($sql, $mysql_link, MYSQL_ASSOC);
}

function get_monthly_sales($mysql_link) {
    return mysql_fetch_all('SELECT * FROM MonthlySales', $mysql_link, MYSQL_ASSOC);
}

function mysql_fetch_all($sql, $mysql_link, $mode = MYSQL_ASSOC, $callback = null) {
    $rows = array();
    $result = mysql_query($sql, $mysql_link);
    if ($result && mysql_num_rows($result)) {
        while ($row = mysql_fetch_array($result, $mode)) {
            if (is_callable($callback) && ($crow = call_user_func($callback, $row))) {
                $row = $crow;
            }
            $rows[] = $row;
        }
    }
    return $rows;
}

/**
* @param array|ArrayAccess $array
*/
function get_param($array, $name, $default = null) {
    if (!isset($array[$name])) {
        return $default;
    }
    return $array[$name];
}

function get_year_range() { // this may be used at several places but changing the actual range is done here.
    return range(2000/*start*/, 2001/*end*/);
}

 

As you can see this looks much cleaner than your example. The below code assumes you have $link = mysql_connect('localhost', 'root', 'root'); somewhere.

 

monthly_sales.php

<?php include 'functions.php'; ?>

<form action="<?php /*leave empty if processed on same page*/ ?>" method="post" name="form_filter">
  <select name="year">
    <option value="all">All</option>
    <?php foreach (get_year_range() as $year): ?>
    <option value="<?php print $year; ?>"><?php print $year; ?></option>
    <?php endforeach; ?>
  </select>
  <input type="submit" value="Filter">
</form>

<table>
<?php if (!get_param($_POST, 'year')) {
  <?php foreach (get_monthly_sales($link) as $sale): ?>
  <tr>
    <td><?php print $sale['Id']; ?></td>
    <td><?php print $sale['ProductCode']; ?></td>
    <td><?php print $sale['Month']; ?></td>
    <td><?php print $sale['Year']; ?></td>
    <td><?php print $sale['SalesVolume']; ?></td>
  </tr>
  <?php endforeach; ?>
<?php else: ?>
  <?php foreach (get_month_sales_for_year(get_param($_POST, 'year'), $link) as $sale): ?>
  <tr>
    <td><?php print $sale['Id']; ?></td>
    <td><?php print $sale['ProductCode']; ?></td>
    <td><?php print $sale['Month']; ?></td>
    <td><?php print $sale['Year']; ?></td>
    <td><?php print $sale['SalesVolume']; ?></td>
  </tr>
  <?php endforeach; ?>
<?php endif; ?>
</table>