[Resolved] Selecting the right MYSQL statement

[djwiltsh]

Hi there

I am currently re-coding the website for the music shop where I work using PHP and MYSQL to generate dynamic product pages instead of the fixed pages that we currently have.

I have made the database without any problems and I have also done a simple search using the following sql statement:

"select * from products where make='$make' and type='$type' ";

The $make and $type variables are passed from an dropdown menus in an HTML form and a table is then generated showing the appropriate images and bullet points which are stored in the database.

Now I want to do an advanced search. The variables that can be selected are make, type, priceRange, new, preOwned and both.

I want to allow a user to search on any combination of the above variables, eg just make and priceRange, or type and new etc.

I have tried using if statements to select the appropriate sql statement for the variables that are passed to my php script, but there are just so many combinations that this can't possibly work.

How can I select an appropriate SQL statement based on the variables that are passed, or write a single statement that is flexible enough to allow for any combination?

Thanks

Daniel Wiltshire

[Ravenous]

You could lessen your number of IF statement by simply tacking on a AND $thisfield = '$thisvar' to the end of your query. For instance,

$q = SELECT * FROM products WHERE $thisfield = '$var1';

if(isset($asecondvar))
   $q .= " AND $field2 = '$var2';

That's not perfect, but it might be a start.

[djwiltsh]

Thanks very much.

That has worked perfectly and saved me hundreds of if statements!