Jordan Jordan - 5 months ago 10
PHP Question

multiple AND / OR operators not working as expected in mysql query

I'm trying to query my items table and get items based on a search query but I want to exclude items that have a status of 2 or 0 from those results, 1 represents items that are open for bidding.

My query thus far is

SELECT a.item_id,a.auction_id as item_auction_id,a.item_code,a.name as item_name,a.description as item_desc,a.full_description as item_full_desc,a.image as item_image,a.donor as item_donor,a.cost as item_cost,a.value as item_value,a.reserve as item_reserve,a.bid_increment as item_bid_increment,a.buy_price as item_buy_price,a.status as item_status,a.published as item_published,a.paid as item_paid,a.pay_type as item_pay_type, a.transaction_id as item_transaction_id,a.is_raffle as item_is_raffle,a.raffle_price as item_raffle_price, a.raffle_qty as item_raffle_qty,a.live_auction as item_live_auction,a.active as item_active,a.refunded as item_refunded, MAX(b.bid_amt) as item_high_bid,b.user_id as item_high_bid_user_id,b.bid_time as item_high_bid_time, b.bid_id as item_high_bid_id,SUM(c.raffle_ticket_qty) as item_tickets_purchased, '0000,0000' as item_bid_history, 0 as watching
FROM text2bid_items as a
LEFT JOIN (SELECT user_id, item_id, MAX(bid_amt) as bid_amt, bid_time, bid_id FROM text2bid_bids GROUP BY bid_id) as b
ON a.item_id = b.item_id
LEFT JOIN text2bid_raffle_purchases as c
ON a.item_id = c.item_id
WHERE a.name LIKE '%$params[2]%'
OR a.description LIKE '%$params[2]%'
OR a.full_description LIKE '%$params[2]%'
AND a.status = 1
GROUP BY a.item_id


But items with status = 2 or 0 are still showing in my results. I'm sure it has something to do with the use of both
OR
and
AND
here but I'm not sure how to google-fu this to figure out how to do it, I've tried different combinations of
()
to see if I can get a difference precedence but maybe that's not the solution?

The results should be any items where the
name, description, or full_description
are like the search query but I want to exclude items that don't have a
status
equalling 1

Answer

The logical operators in your query are mixed. So its better to keep them in parenthesis so as to keep the query logic in tact. I have updated the query accordingly, please check!

SELECT a.item_id,a.auction_id as item_auction_id,a.item_code,a.name as item_name,a.description as item_desc,a.full_description as item_full_desc,a.image as item_image,a.donor as item_donor,a.cost as item_cost,a.value as item_value,a.reserve as item_reserve,a.bid_increment as item_bid_increment,a.buy_price as item_buy_price,a.status as item_status,a.published as item_published,a.paid as item_paid,a.pay_type as item_pay_type, a.transaction_id as item_transaction_id,a.is_raffle as item_is_raffle,a.raffle_price as item_raffle_price, a.raffle_qty as item_raffle_qty,a.live_auction as item_live_auction,a.active as item_active,a.refunded as item_refunded, MAX(b.bid_amt) as item_high_bid,b.user_id as item_high_bid_user_id,b.bid_time as item_high_bid_time, b.bid_id as item_high_bid_id,SUM(c.raffle_ticket_qty) as item_tickets_purchased, '0000,0000' as item_bid_history, 0 as watching
            FROM text2bid_items as a
            LEFT JOIN (SELECT user_id, item_id, MAX(bid_amt) as bid_amt, bid_time, bid_id FROM text2bid_bids GROUP BY bid_id) as b
            ON a.item_id = b.item_id
            LEFT JOIN text2bid_raffle_purchases as c
            ON a.item_id = c.item_id
            WHERE (a.name LIKE '%$params[2]%'
            OR a.description LIKE '%$params[2]%'
            OR a.full_description LIKE '%$params[2]%') 
            AND a.status = 1
            GROUP BY a.item_id