Make MySQL query meet multiple requirements

[princeofvegas]

I have had a heck of a time figuring this out and it is driving me crazy! lol. I am trying to make an SQL query meet several requirements from the table before the results are displayed. Here is the statement I am working with:

"SELECT * FROM coupons WHERE category_id=5 AND coupon_zip=89118"

I want it to only pull rows from the database where both the category ID match AND the coupon ZIP match. Unfortunately no matter how many times I try it it still provides me with rows that have either one or both of the requirements. Am I doing something wrong? Any help would be appreciated.

[Matthew1980]

Hi there princeofvegas,

Welcome to the forum :)[webmasterworld.com ]

Have you tried posting in the mysql forum? [webmasterworld.com ]

That query looks fine to me, but if there are duplicate values in the DB it will only pull out what it matches, so if there is more than 1 hit, it will obviously pull out what it thinks you are asking for :) Unless I am missing something *REALLY* obvious...

Cheers,
MRb

[princeofvegas]

Thank you.. I did not realize that there was a mysql forum. Sorry (noob here).

I did not copy the right line when i posted. The script problem actually starts when I run:

$searchresult = mysql_query("SELECT * FROM coupons WHERE category_id='5' OR category_id='6' AND coupon_zip='89118' or coupon_zip='91361'");

It will pull any row that either match the zip code or the category ID.

I am hoping to get it to pull just the rows that match both the zip code and the category ID.

[Readie]

We need to use parentheses to split the WHERE clause up here, try this:

$searchresult = mysql_query("SELECT * FROM coupons WHERE (category_id='5' OR category_id='6') AND (coupon_zip='89118' or coupon_zip='91361')"); 

For the record, there is a neater way of checking groups of values in MySQL:

SELECT * FROM coupons WHERE category_id IN (5, 6) AND coupon_zip IN (89118, 91361)

[princeofvegas]

Thank you VERY VERY much that worked like a charm. I did try the IN statements and I could not seem to get it to work. Could it be because when I wrote them I did it like this:

category_id IN('5','6')

Thank you again for you help.

[Matthew1980]

Hi all,

>>I did not copy the right line when i posted

Well at least that explains why the code you posted looked fine to me ;)

As far as I am aware the single quotes shouldn't matter either way, as I found out recently with DATE_FORMAT() ...

Cheers,
MRb