Page 1 of 1

Make MySQL query meet multiple requirements

Posted: Wed Jun 30, 2010 1:24 am
by 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:

Code: Select all

$searchresult = mysql_query("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.

Re: Make MySQL query meet multiple requirements

Posted: Wed Jun 30, 2010 1:46 am
by rahulzatakia
Hi, I have tried your query and there is no problem in it. Its working fine with me. If you still have any error then let me know with more code as to find out where is the exact problem.

Re: Make MySQL query meet multiple requirements

Posted: Wed Jun 30, 2010 1:55 am
by princeofvegas
I just tried breaking it down and it works perfectly when I run the query like that.. However, I just noticed that the problem starts and I get unexpected results when I run the query like this:

Code: Select all

$searchresult = mysql_query("SELECT * FROM coupons WHERE category_id='5' OR category_id='6' AND coupon_zip='89118' or coupon_zip='91361'");
I dont know if the OR statements are confusing it and if so is there a more proper way to do it?

Re: Make MySQL query meet multiple requirements

Posted: Wed Jun 30, 2010 8:34 am
by Jade
You need to put parenthesis around your logic.

Code: Select all

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

Re: Make MySQL query meet multiple requirements

Posted: Wed Jun 30, 2010 1:36 pm
by princeofvegas
Hello thank you for you help with this. That worked perfectly. I also found that this line worked as well:

Code: Select all

$searchresult = mysql_query("SELECT * FROM coupons WHERE category_id IN (5, 6) AND coupon_zip IN (89118, 91361)");
Finally hours of headache are gone. :D