Stuck Writing an Advanced Search PHP

PHP
1

Greetings from Sweden, where I’m stuck on something that the sharper among you will correctly recognize as pretty basic! I appreciate very much your help.

Here’s a simplified version of the problem. On one page, there’s a simple advanced search function where users can specify one, both or neither of two fields, each with two drop-down menu options:

<html>
<form action=“results.php” method=“POST”>
<select name=“size”>
<option value></option>
<option value=“small”>small</option>
<option value=“large”>large</option>
</select>
<select name=“color”>
<option value></option>
<option value=“green”>green</option>
<option value=“blue”>blue</option>
</select>
</form>
</html>

Some records in the database have only a size specified; some have only a color specified; some have both; and importantly, some have neither.

Where I’m stuck is in writing code that returns results as follows:

If the user selects a size, all returned results should have that size.
If the user selects a color, all returned results should have that color.
If the user selects both size and color, all returned results should have both that size and that color.
If the user selects neither size nor color, then there should be no results, even those records that themselves have neither size nor color specified.

I suspect that success will come from a well written query line, which the following disaster of mine clearly is not:

$query=“SELECT * FROM table WHERE size=‘$size’ OR color=‘$color’”;

I have read through these forum posts and have tried to better understand PHP operators, but truly am stuck. Thank you for reading!:slight_smile:

2

it will be better if you write two different queries as


if ($size != '')
       $query="SELECT * FROM table WHERE size='$size'";
if ($color!= '')
       $query="SELECT * FROM table WHERE color='$color'";

then u can merge or return the two results

3

Thank you, pbsonawane, so very much for your suggestion! I see your point. But please, I think there’s a problem with that. Consider a case where the user selects “small” for size and “green” for color. The returned results from the code you suggest would include all records where the size is “small,” including those where the color is “blue.” Please, isn’t that right?

4

@kjm7267 ;
Why not post your codes here.
So everyone can see.

By the way your problem is just all about using conditions if or else or where etc…
Are you using a framework or just pure PHP?

5

try this


$query="SELECT * FROM table WHERE 1=1";	
if ($size != '')
       $query .= " AND size='$size'";
if ($color!= '')
       $query .= " AND color='$color'";
6

Thank you, solidcodes, for your comment. I am using pure PHP. And you’re right, my issue does seem to be just getting the conditional statements right. In English, what I want the code to say is:

If only size is selected, return records with that size exactly;
If only color is selected, return records with that color exactly;
If both a size and a color are selected, return only records with both that size and that color exactly; and
If neither a size nor a color are selected, return no records.

The trick, I think, is not returning records that don’t have any size defined when the user doesn’t select a size, but selects instead only color.

Thank you solidcodes!

7

Thank you so much, pbsonawane ! Your suggestion worked perfectly, and the problem is solved. I’m grateful for your help. Thanks again pbsonawane ! :tup: