MySQL Coding Help (AND/OR statements)

So here is the deal;

I have this code

Code:

SELECT * FROM car_inventory WHERE ((make='$make') AND ((series='$series') AND (product='$product'))) OR ((make='$make') AND ((series='$series') OR (product='$product'))) ORDER BY series

That I ideally want it to do this, but it isn't working;

There is a make of a product, Flowlighting.
There is a series, Inferno.
There is a product, Underbody Kit.

What I want it to do is make it so I can have a call to function that grabs something like this:

get_info.php?make=Flowlighting&product=Underbody Kit&series=Inferno

OR

get_info.php?make=Flowlighting&product=Underbody Kit
OR
get_info.php?make=Flowlighting&series=Inferno

It works, kinda.

With the first and second OR's are removed, I can call all 3 things and it picks them out.

When they are placed in and the code calls for all 3, the entire products/series show up for a certain make.

When the code calls for only 2, nothing shows.

Here is an example that is live on my website.

I am calling for all 3 things, Make = TMA, Product = Subwoofers, Series = T-Series

http://shoandgo.com/stereo/get_info.php?make=TM...t=Subwoofers&series=T-Series

All TMA products show up. T-Series is for Speakers and Subwoofers, which is why I need to include the &product.

If &product or &series is removed, nothing shows up.

Whats the deal, y0!?

Thanks!
Chris

Your SQL is heading down the right path, but you need to build your SQL differently.

Try some PHP like this:

Code:

$info_select = " SELECT *   FROM car_inventory WHERE 1=1"; //append make if given if (isset($_GET['make']) && !empty($_GET['make'])) $info_select .= "    AND make = '" . mysql_real_escape_string($_GET['make']) . "'"; //append series if given if (isset($_GET['series']) && !empty($_GET['series'])) $info_select .= "    AND series = '" . mysql_real_escape_string($_GET['series']) . "'"; //append product if given if (isset($_GET['product']) && !empty($_GET['product'])) $info_select .= "    AND product = '" . mysql_real_escape_string($_GET['product']) . "'"; //always append order by clause $info_select .= " ORDER    BY series";

A couple other things to notice:
"WHERE 1=1" is a great to use when building an AND clause. 1=1 will always return true and makes it really easy to add a bunch of other criteria to the clause.
mysql_real_escape_string is required. If you aren't using it then you have a serious vulnerability in your website. If something is going into your database, you need to use mysql_real_escape_string or another strict validation function like intval()/floatval() depending on the datatype.
The SELECT * should also be rewritten with specific columns, but its not life or death.

Try this... seems like u had too many ()

i assume all this info is in 1 row of the database?

Code:

SELECT * FROM car_inventory WHERE ((make='$make') AND (series='$series') AND (product='$product')) OR (make='$make' AND series='$series') OR (product='$product') ORDER BY series

Brandons code should work as well.

zanson wrote:

Try this... seems like u had too many () i assume all this info is in 1 row of the database? Code: SELECT * FROM car_inventory WHERE ((make='$make') AND (series='$series') AND (product='$product')) OR (make='$make' AND series='$series') OR (product='$product') ORDER BY series Brandons code should work as well.

Brandon's is the version to use. You don't want your SQL query searching for empty strings.

Aken wrote:

zanson wrote: Try this... seems like u had too many () i assume all this info is in 1 row of the database? Code: SELECT * FROM car_inventory WHERE ((make='$make') AND (series='$series') AND (product='$product')) OR (make='$make' AND series='$series') OR (product='$product') ORDER BY series Brandons code should work as well. Brandon's is the version to use. You don't want your SQL query searching for empty strings.

hummm good point... sorry im a little brain dead today >.< what an off week.

Thanks guys.

I'll have to rip brandons code apart so I understand it before I use it. I'll report back in a few days. This is one of those things I work on when I have time....sooo once every other week.

Basically he just has three IF statements checking to see if a GET variable exists, and makes sure it isn't empty.

url.php?a=blah&b=stuff

a and b are GET variables. $_GET['a'] and $_GET['b']

So after it finds out if a variable is defined in your URL, and makes sure it isn't empty, it adds on to the WHERE clause in your query.

only bad thing is if a=0&b=0, they will both be considered "empty". If that's a problem for your script, then change !empty($_GET['make']) to $_GET['make'] !== ''

Thanks guys.

A and B will never be the same. Neither will C...anymore.

The problem lied within the TMA, JL Audio and Alpine I believe.

They all have subwoofers and speakers with the same series, which I didn't pay any attention to, until I ran across this problem.

So lets say I have a call that gets A and B, but not C. Will it still select A and B? If I have a call for A, B and C, will it still work?

Thanks!

The code I gave you will work for any combination of A B and C.
A
AB
ABC
B
BC
C
and even nothing (if no options are given, every item gets returned)