Page 1 of 1
Mutiple AND in MYSQL Query not working
Posted: Fri Oct 15, 2010 9:16 pm
by princeofvegas
I am running an SQL query and everything is working great with it just the way that it is supposed to. However I just had to ass the coupon_status = 'ACTIVE' portion to it and for some reason the query is ignoring that part of it. Is it possible to have multiple AND statements in a query and if so what is the proper way to do it. Any help would be appreciated.
Code: Select all
"SELECT * FROM coupons WHERE category_id IN ($selected_categories) OR category_id2 IN ($selected_categories) OR category_id3 IN ($selected_categories) OR category_id4 IN ($selected_categories) OR category_id5 IN ($selected_categories) && coupon_zip IN ($homezip, $zipsinrange) && coupon_status = 'ACTIVE' $sort LIMIT ".$limits.",$max"
Re: Mutiple AND in MYSQL Query not working
Posted: Sat Oct 16, 2010 1:12 pm
by McInfo
In MySQL, AND has a higher precedence than OR. [
MySQL Manual: Operator Precedence] Therefore, in your query, "category_id5 IN ... && coupon_zip IN ..." will be evaluated first. Next, that result will be used in the comparison "
result && coupon_status = 'ACTIVE'". Then the ORs will be evaluated, starting with the leftmost comparison following "WHERE".
Use parentheses to group the comparisons so they will be evaluated in the intended order.
The appearance of sequential field names (category_id, category_id2, category_id3, ...) is a symptom of a poorly designed schema. Tables should not expand horizontally (by adding columns), they should expand only vertically (by adding rows). If you find yourself adding columns that are related such that they can be named sequentially, consider creating a separate table.
It appears that you have a coupon entity and a category entity. If coupons can belong to multiple categories and categories can contain multiple coupons, the coupons and categories are said to have a many-to-many relationship. Supposing that two tables already exist for coupons and categories, a third table (coupon_category) is needed for the relationship.
Re: Mutiple AND in MYSQL Query not working
Posted: Sat Oct 16, 2010 1:28 pm
by princeofvegas
Thank you very much for your help and input. That is making more sense to me now. I am still in the learning proces of implementing databases with PHP. I have one more question on that topic.
If I create a new table (i.e. coupons_category), how do I join that table into my query so that it searches coupons_category with $selected_categories.
Thank you again for your help.
Re: Mutiple AND in MYSQL Query not working
Posted: Sat Oct 16, 2010 2:41 pm
by McInfo
Given this schema and data...
Code: Select all
DESCRIBE `coupon`
# +--------+-----------------+------+-----+---------+----------------+
# | Field | Type | Null | Key | Default | Extra |
# +--------+-----------------+------+-----+---------+----------------+
# | id | int(3) unsigned | NO | PRI | | auto_increment |
# | name | varchar(20) | NO | | | |
# | active | tinyint(1) | NO | | 1 | |
# +--------+-----------------+------+-----+---------+----------------+
DESCRIBE `category`
# +-------+-----------------+------+-----+---------+----------------+
# | Field | Type | Null | Key | Default | Extra |
# +-------+-----------------+------+-----+---------+----------------+
# | id | int(3) unsigned | NO | PRI | | auto_increment |
# | name | varchar(20) | NO | | | |
# +-------+-----------------+------+-----+---------+----------------+
DESCRIBE `coupon_category`
# +-------------+-----------------+------+-----+---------+-------+
# | Field | Type | Null | Key | Default | Extra |
# +-------------+-----------------+------+-----+---------+-------+
# | coupon_id | int(3) unsigned | NO | PRI | | |
# | category_id | int(3) unsigned | NO | PRI | | |
# +-------------+-----------------+------+-----+---------+-------+
SELECT * FROM `coupon`
# +----+----------+--------+
# | id | name | active |
# +----+----------+--------+
# | 1 | Coupon01 | 1 |
# | 2 | Coupon02 | 1 |
# | 3 | Coupon03 | 0 |
# | 4 | Coupon04 | 1 |
# | 5 | Coupon05 | 1 |
# +----+----------+--------+
SELECT * FROM `category`
# +----+-------+
# | id | name |
# +----+-------+
# | 1 | Cat01 |
# | 2 | Cat02 |
# | 3 | Cat03 |
# | 4 | Cat04 |
# | 5 | Cat05 |
# +----+-------+
SELECT * FROM `coupon_category`
# +-----------+-------------+
# | coupon_id | category_id |
# +-----------+-------------+
# | 1 | 2 |
# | 1 | 3 |
# | 2 | 4 |
# | 3 | 5 |
# | 5 | 4 |
# | 5 | 6 |
# | 6 | 5 |
# +-----------+-------------+
This query selects all associated coupons and categories. Notice that INNER JOIN is used to exclude [Coupon 5, Category 6] and [Coupon 6, Category 5] because there is no Coupon 6 or Category 6.
Code: Select all
SELECT
cc.coupon_id,
cp.name AS coupon_name,
cc.category_id,
ct.name AS category_name
FROM
coupon_category AS cc
INNER JOIN
coupon AS cp
ON (cp.id = cc.coupon_id)
INNER JOIN
category AS ct
ON (ct.id = cc.category_id);
# +-----------+-------------+-------------+---------------+
# | coupon_id | coupon_name | category_id | category_name |
# +-----------+-------------+-------------+---------------+
# | 1 | Coupon01 | 2 | Cat02 |
# | 1 | Coupon01 | 3 | Cat03 |
# | 2 | Coupon02 | 4 | Cat04 |
# | 3 | Coupon03 | 5 | Cat05 |
# | 5 | Coupon05 | 4 | Cat04 |
# +-----------+-------------+-------------+---------------+
This query is the same as above with an additional WHERE clause. Notice that the row [Coupon 3, Category 5] is missing because Coupon 3 is not active. Also notice that row [Coupon 1, Category 2] is missing because Category 2 is not in the set {3, 4, 5}.
Code: Select all
SELECT
cc.coupon_id,
cp.name AS coupon_name,
cc.category_id,
ct.name AS category_name
FROM
coupon_category AS cc
INNER JOIN
coupon AS cp
ON (cp.id = cc.coupon_id)
INNER JOIN
category AS ct
ON (ct.id = cc.category_id)
WHERE
cp.active = 1
AND cc.category_id IN (3, 4, 5);
# +-----------+-------------+-------------+---------------+
# | coupon_id | coupon_name | category_id | category_name |
# +-----------+-------------+-------------+---------------+
# | 1 | Coupon01 | 3 | Cat03 |
# | 2 | Coupon02 | 4 | Cat04 |
# | 5 | Coupon05 | 4 | Cat04 |
# +-----------+-------------+-------------+---------------+
Edit: Changed LEFT JOINs to INNER JOINs.
Re: Mutiple AND in MYSQL Query not working
Posted: Sun Oct 24, 2010 4:35 pm
by princeofvegas
Hello again. Thank you for all of your help.
I have been learning more and more about the different types of joins and I have recreated the tables similar to what you have recommended. I now have 3 different tables, coupons, coupons_categories, and business_locations. This is the query that I am using now:
Code: Select all
SELECT * FROM (coupons LEFT JOIN business_locations on coupons.location_id = business_locations.location_id) LEFT JOIN coupons_categories on coupons.coupon_id = coupons_categories.coupon_id WHERE category_id IN ($selected_categories) AND business_zip IN ($homezip, $zipsinrange) AND coupon_status = '1'
Everything works fine until I join the categories table to it. When I have a coupon that is in multiple categories, the left join returns how ever many rows for that coupon depending on the number of categories it is in. Is there a way to run the query so that if there is more than one row in the result for each coupon_id to limit it to one row per coupon_id?
Thank you again for all of your help I am learning a lot and very fast.
Re: Mutiple AND in MYSQL Query not working
Posted: Mon Nov 01, 2010 3:37 pm
by McInfo
Please describe all the tables you have now.