A Complex WHERE

MySQL - Need help formatting multiple conditions

[quixote]

Hey gang, been trying to figure this out, can't find an answer so I'm hoping someone here can help.

I'm working on a search function in PHP/MySQL and I need to write a SELECT statement with several conditionals. Here's an example of what I want:

SELECT id FROM mytable WHERE (label = 'color' AND value LIKE 'green') AND (anotherlabel = 'brand' AND anothervalue LIKE 'izod') etc... for about 5 passes.

The above does not work, obviously. I inherited the database structure that requires such pairing so I can't really change it.

Is this possible the way I'm attempting? Is there a better way to get exclusive matches without a million IF statements? Thanks to anyone who can shed some light.

[phranque]

are you saying table 'mytable' has columns named 'label', 'value', 'anotherlabel' and 'anothervalue'?

i don't wee why this wouldn't work.
what error do you get?

[quixote]

Not exactly, sorry if I was unclear in my post (it was pretty late...) :)

For each item, there are these columns: Id, Value and Label. And each item will have several entries for value and label, I'm trying to keep them paired in the query.

"anotherlabel' etc was to show that I want to query a different 'value'/'label' combo for each condition

Bascially my query for each pass:
Look for all values that match a set 'label' and see if the corresponding 'value' matches (user input string), if so, return the item_id.

Here's a schematic (made ugly by stripped spaces):

************************************** 
* ROW_ID * item_id * label * value * 
************************************** 
* 276 * 1 * color * green * 
* 277 * 1 * brand * lucky * 
* 278 * 1 * size * petite * 
* 279 * 2 * color * blue * 
* 280 * 2 * brand * izod * 
* 281 * 2 * size * petite * 
************************************** 
And so on... 

Notice that there are more than one row for each item_id, one for each label/value combo, and that there's a unique ROW_ID for each um, row.

[quixote]

As far as errors, not getting any syntax errors, just no results... :(

[LifeinAsia]

Think about your statement logically- there's no syntax error, but you will NEVER get any row that meets all the AND conditions.

Quick patch- You'll need to use OR:
SELECT id FROM mytable WHERE (label = 'color' AND value LIKE 'green') OR (anotherlabel = 'brand' OR (anothervalue LIKE 'izod')
and then somehow sort things out to pull out the IDs that meet all the conditions.

Real fix- completely redo the table structure if you're going to be doing queries like this a lot.

[edited by: LifeinAsia at 3:50 pm (utc) on Oct. 17, 2007]

[quixote]

Thanks for replying, LifeinAsia!

I tried your method but it returns non-exclusive results. I'm trying to get my query to do my filtering for me. Maybe too much to ask in this case. (If someone can shoot me some PHP to properly filter with if's, I'm open!)

Is there no way to pair up conditions i.e. "Color is Blue" AND "Brand is Izod" AND "Size is petite" in a query, as these could be conditions in "real life".

Real fix- completely redo the table structure

Yeah, I agree! It's a work of sheer madness. Unfortunately I'm adding features to an of-the-shelf setup. Fortunately, this should be only time I need to do these acrobatics. {fingers crossed}

[LifeinAsia]

Is there no way to pair up conditions i.e. "Color is Blue" AND "Brand is Izod" AND "Size is petite" in a query, as these could be conditions in "real life".

Not exactly, with the current setup.

Something like this might work:
SELECT item_id, COUNT(*)
WHERE (label='color' AND value='Blue') OR (label='brand' AND value='Izod') OR (label='Size' AND value=petite'')
GROUP BY item_id
HAVING COUNT(*)=3

This should give you the item_id where each of the 3 conditional pairs match. If you have additional conditional pairs to check, you'll need to adjust the HAVING number appropriately (the number needs to match the number of conditional pairs).

[quixote]

You are brilliant! That worked like a charm.

Thanks for all your help, I owe you bigtime!

[LifeinAsia]

I take cash, credit, and beer. :)