SELECT & AND

[ririe44]

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? 

 

 

[ngreenwood6]

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

[ririe44]

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!

[kristofferlc]

You sure it has to be `pd_size` = '$size' and not `pd_size` < '$size' or something?

[ririe44]

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!

[ririe44]

Or maybe there is a way to say:  if $filter_category = "Any Category" then it equals all results/everything...?