ririe44 Posted November 3, 2009 Share Posted November 3, 2009 Hey friends... I'm trying to have multiple filters effect the retrieval from my database. So, for now, I want to be able to retrieve all the rows that have the specified category AND size... $pd_data = mysql_query("SELECT * FROM `$tbl_name` WHERE `pd_category` = '$category' AND `pd_size` = '$size'") or die(mysql_error()); Right now, this is resulting in nothing... is there a better way to do this? Quote Link to comment Share on other sites More sharing options...
ngreenwood6 Posted November 3, 2009 Share Posted November 3, 2009 Are you sure the data is there? Have you tried doing just pd_category and just pd_size to see if they result in common values? It looks like your query is fine Quote Link to comment Share on other sites More sharing options...
ririe44 Posted November 3, 2009 Author Share Posted November 3, 2009 You're right... I had tried it with just `pd_category`, but I hadn't tried it with just `pd_size`, which didn't return anything. So, here's more of my code, because obviously I'm having problems getting the results from `pd_size` (however, my select box does come up with all the options from my table) <? $option_cat = mysql_query("SELECT * FROM `$tbl_name`") or die(mysql_error());?> <? $option_size = mysql_query("SELECT * FROM `$tbl_name`") or die(mysql_error());?> <td><select name="filter_category"> <option>Select a Category!</option> <? while($row=mysql_fetch_array($option_cat)){?> <option value=<?=$row['pd_category']?>><?=$row['pd_category']?></option> <? } ?> </select></td> <td><select name="filter_size"> <option>Select a Size!</option> <? while($row2=mysql_fetch_array($option_size)){?> <option value=<?=$row2['pd_size']?>><?=$row2['pd_size']?></option> <? } ?> </select></td> <td><input type=submit name="submit" value="Go!" /></td> </tr> </table> </form></p> <? if ($_POST['submit']=="Go!") { $filter_category = $_POST['filter_category']; $filter_size = $_POST['filter_size']; $pd_data = mysql_query("SELECT * FROM `$tbl_name` WHERE `pd_category` = '$filter_category' AND `pd_size` = '$filter_size'") or die(mysql_error()); Do you see where my problem is? Thanks for the help! Quote Link to comment Share on other sites More sharing options...
kristofferlc Posted November 3, 2009 Share Posted November 3, 2009 You sure it has to be `pd_size` = '$size' and not `pd_size` < '$size' or something? Quote Link to comment Share on other sites More sharing options...
ririe44 Posted November 3, 2009 Author Share Posted November 3, 2009 K, I solved it, but I'm not totally sure what I did to fix it... I just used another one of my successful attempts and gave it a try... <td width="880"><p><div> <form method="post"> <table align="center"> <tr> <th colspan="3">Product Filter </th> </tr> <? $option_cat = mysql_query("SELECT * FROM `$tbl_name`") or die(mysql_error());?> <? $option_size = mysql_query("SELECT * FROM `$tbl_name`") or die(mysql_error());?> <tr> <td> <select name="filter_category"> <option>Select a Category!</option> <? while($row = mysql_fetch_array($option_cat)){ echo '<option value="'.$row['pd_category'].'">'.$row['pd_category'].'</option>'; } ?> </select> </td> <td> <select name="filter_size"> <option>Select a Size!</option> <? while($row2 = mysql_fetch_array($option_size)){ echo '<option value="'.$row2['pd_size'].'">'.$row2['pd_size'].'</option>'; } ?> </select></td> <td><input type=submit name="submit" value="Go!" /></td> </tr> </table> </form></p> <? if ($_POST['submit']=="Go!") { $filter_category = $_POST['filter_category']; $filter_size = $_POST['filter_size']; $filter_data = mysql_query("SELECT * FROM `$tbl_name` WHERE `pd_category` = '$filter_category' AND `pd_size` = '$filter_size'") or die(mysql_error()); Print "<table border=0 cellpadding=3 width=650 align=center>"; Print "<tr>"; Print "<th> </th> <th>Description</th> <th>Price</th> <th> </th></tr>"; while($filter_info = mysql_fetch_array( $filter_data )) { Print "<tr>"; Print "<td valign='top'>".$filter_info['pd_thumbnail'] . "</td> "; Print "<td valign='top'>".$filter_info['pd_description'] . "</td>"; Print "<td valign='top'>".$filter_info['pd_price'] . "</td>"; Print "<td valign='top'>".$filter_info['pd_btn_link'] . "</td>"; } Print "</table>"; } elseif (isset($_POST['submit'])) { die(mysql_error()); } ?> </td> So, now I would like to add an option "Any Category" and "Any Size" to my two different select tags: <select name="filter_category"> <option>Select a Category!</option> <option value="Any Category">Any Category</option> <? while($row = mysql_fetch_array($option_cat)){ echo '<option value="'.$row['pd_category'].'">'.$row['pd_category'].'</option>'; } ?> </select> </td> <td> <select name="filter_size"> <option>Select a Size!</option> <option value="Any Size">Any Size</option> <? while($row2 = mysql_fetch_array($option_size)){ echo '<option value="'.$row2['pd_size'].'">'.$row2['pd_size'].'</option>'; } ?> </select></td> So, what I'm not sure how to do... is somehow put into my code that if "Any Size" or "Any Category" were selected, then it would bring up all rows of my table as an option... $filter_category = $_POST['filter_category']; $filter_size = $_POST['filter_size']; $filter_data = mysql_query("SELECT * FROM `$tbl_name` WHERE `pd_category` = '$filter_category' AND `pd_size` = '$filter_size'") or die(mysql_error()); So... would something like this work? (well, it doesn't yet, so I need your help) <? if ($_POST['submit']=="Go!") { $filter_category = $_POST['filter_category']; $filter_size = $_POST['filter_size']; if ($filter_category == "Any Category" AND $filter_size =! "Any Size") { $filter_data = mysql_query("SELECT * FROM `$tbl_name` WHERE `pd_size` = '$filter_size'") or die(mysql_error()); } elseif ($filter_category =! "Any Category" AND $filter_size == "Any Size") { $filter_data = mysql_query("SELECT * FROM `$tbl_name` WHERE `pd_category` = '$filter_category'") or die(mysql_error()); } elseif ($filter_category =! "Any Category" AND $filter_size =! "Any Size") { $filter_data = mysql_query("SELECT * FROM `$tbl_name` WHERE `pd_category` = '$filter_category' AND `pd_size` = '$filter_size'") or die(mysql_error()); } elseif ($filter_category == "Any Category" AND $filter_size == "Any Size") { $filter_data = mysql_query("SELECT * FROM `$tbl_name` WHERE `pd_category` = '$filter_category' AND `pd_size` = '$filter_size'") or die(mysql_error()); } Print "<table border=0 cellpadding=3 width=650 align=center>"; Print "<tr>"; Print "<th> </th> <th>Description</th> <th>Price</th> <th> </th></tr>"; while($filter_info = mysql_fetch_array( $filter_data )) { Print "<tr>"; Print "<td valign='top'>".$filter_info['pd_thumbnail'] . "</td> "; Print "<td valign='top'>".$filter_info['pd_description'] . "</td>"; Print "<td valign='top'>".$filter_info['pd_price'] . "</td>"; Print "<td valign='top'>".$filter_info['pd_btn_link'] . "</td>"; } Print "</table>"; } elseif (isset($_POST['submit'])) { die(mysql_error()); } ?> Thanks! Quote Link to comment Share on other sites More sharing options...
ririe44 Posted November 3, 2009 Author Share Posted November 3, 2009 Or maybe there is a way to say: if $filter_category = "Any Category" then it equals all results/everything...? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.