I am trying to query all valid discounts from a mysql table.
It is setup like:
ID ItemID ProviderID ConditionID GradeID DiscLimit DiscRedeemed DiscExp
1 0 0 3 0 10 10 0000-00-00 00:00:00
Where DiscExp is a datetime.
Only one of ItemID, ProviderID, ConditionID or GradeID would ever have a value other than 0 at any given time.
I am trying to query all valid discounts, where the "limit" hasn't been reached DiscRedeemed < DiscLimit and it hasn't expired DiscExp < NOW()
Here is the query:
$query = $db->query("SELECT * FROM `store_item_discounts` WHERE
(ItemID > 0 AND ItemID = '$item[ID]')
OR (ProviderID > 0 AND ProviderID = '$item[Provider]')
OR (ConditionID > 0 AND ConditionID = '$item[ItemCondition]')
OR (GradeID > 0 AND GradeID = '$item[ItemGrade]')
AND (DiscLimit = 0 OR DiscRedeemed < DiscLimit)
AND (DiscExp = '0000-00-00 00:00:00' OR DiscExp > NOW())
ORDER BY DiscType ASC");
Of the $item array, all values are 0 except for $item['ItemCondition'] which equals 3.
For some reason, this is returning the result as shown above, where the limit has been reached. Also it doesn't filter out the dates or other variables correctly.
I obviously have something wrong with my query, but I can't figure it out. Any suggestions?
You're facing a problem with operator precedence. Since AND operator has higher precedence than OR operator, you WHERE clause really looks like:
WHERE
(
(ItemID > 0 AND ItemID = '$item[ID]')
OR (ProviderID > 0 AND ProviderID = '$item[Provider]')
OR (ConditionID > 0 AND ConditionID = '$item[ItemCondition]')
) OR (
(GradeID > 0 AND GradeID = '$item[ItemGrade]')
AND (DiscLimit = 0 OR DiscRedeemed < DiscLimit)
AND (DiscExp = '0000-00-00 00:00:00' OR DiscExp > NOW())
)
So ConditionID = 3 is a sufficient condition to return a row. As Uueerdo pointed out, you need to embrace all your OR conditions in another pair of parenthesis. Your query should then look like:
$query = $db->query("SELECT * FROM `store_item_discounts` WHERE
(
(ItemID > 0 AND ItemID = '$item[ID]')
OR (ProviderID > 0 AND ProviderID = '$item[Provider]')
OR (ConditionID > 0 AND ConditionID = '$item[ItemCondition]')
OR (GradeID > 0 AND GradeID = '$item[ItemGrade]')
) AND (DiscLimit = 0 OR DiscRedeemed < DiscLimit)
AND (DiscExp = '0000-00-00 00:00:00' OR DiscExp > NOW())
ORDER BY DiscType ASC");
See MySQL documentation to learn more about operator precedence.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments