Creating a filter on a query

[slaterino]

Hey I have created a query that displays all records within a table. I am hoping to create some links next to this query that will allow the user to filter these records. How would I go about doing this? This is my query below. I was hoping to have links that would be www.mysite.com/master.php?Genus=Narcissus&Division=4 where master.php is the page I am currently working on and Genus and Division are fields in the table. I know this is possible but just don't know how to start the process! Here is my current query:

 

<?php
include '../library/config.php';
include '../library/opendb.php';

$query  = "SELECT Product_ID, Genus, Common_name, Description FROM products ORDER BY Product_ID DESC ";
$result = mysql_query($query) or die('Error, query failed');
$numofrows = mysql_num_rows($result);

for($i = 0; $i < $numofrows; $i++) {
    $row = mysql_fetch_array($result); 
    if($i % 2) { 
        echo "<tr bgcolor='lightgrey'>";
    } else { 
        echo "<tr bgcolor='white'>";
    }
    echo "<td>".$row['Product_ID']."</td><td>".$row['Genus']."</td><td>".$row['Common_name']."</td><td>".substr($row['Description'], 0, 45)."</td>";
    echo "</tr>";
}
?>

 

Thanks

Russ

[Adam]

Using MySQL's "WHERE" ..

 

SELECT Product_ID, Genus, Common_name, Description FROM products
  WHERE Genus = 'Narcissus' AND Division = 4
ORDER BY Product_ID DESC

 

Obviously you'll want to use variables instead of fixed values - just be sure to secure your variables before using them in queries!

 

A

[Lumio]

<?php
$where = '';
if (array_key_exists('Genus', $_GET))
{  //Checking Genus
    $genus = mysql_escape_string($_GET['Genus']); //To avoid SQL-Injection use mysql_escape_string for user-input
    $where = "`Genus` = '$genus'";
}
if (array_key_exists('Division', $_GET))
{  //Checking Division
    $division = intval($_GET['Division']) //Get an integer out - so don't allow Hello1 or something like that
    if ($where != '') $where .= ' AND ';
    $where .= "`Division` = $division";
}
if ($where != '') $where = ' WHERE '.$where;

$sql = "SELECT Product_ID, Genus, Common_name, Description FROM products$where ORDER BY Product_ID DESC ";
//echo $sql; something like that:
//SELECT Product_ID, Genus, Common_name, Description FROM products WHERE `Genus` = 'something' AND `Division` = 1 ORDER BY Product_ID DESC
?>