"OR" not working in MYSQL SQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
camarosource
Forum Commoner
Posts: 77
Joined: Sat Aug 03, 2002 10:43 pm

"OR" not working in MYSQL SQL

Post by camarosource »

What am I doing wrong?

Code: Select all

 
SELECT 
  SUBMISSION_ID, 
  FULL_NAME, 
  EMAIL, 
  CAMARO_YEAR, 
  CAMARO_MODEL, 
  SAID_TO_PURCHASE,
  ACTUALLY_PURCHASED, 
  WERE_PICS_UPLOADED, 
  EMAILED_NO_PICS_SENT, 
  ADMIN_COMMENTS, 
  GALLERY_CREATED, 
  EMAILED_NO_GALLERY, 
  WERE_PICS_GOOD_ENOUGH, 
  EMAILED_PICS_NOT_GOOD 
FROM 
  user_submissions 
WHERE 
  WERE_PICS_UPLOADED = 'YES'
  AND WERE_PICS_GOOD_ENOUGH = 'NO' 
  OR WERE_PICS_GOOD_ENOUGH = ' ' 
ORDER BY SUBMISSION_ID;
 

I want to do the following

- Select those tables from the user_submissions database WHERE WERE_PICS_UPLOADED = 'YES'. It would ONLY select those tables if the WERE_PICS_GOOD_ENOUGH field = 'NO' OR WERE_PICS_GOOD_ENOUGH = a BLANK entry..

What's wrong?

It seems to select the "WERE_PICS_UPLOADED = 'YES'" is not being read as it will select all those tables EVEN if the WERE_PICS_UPLOADED = is NOT "YES".

If I make it.

"SELECT SUBMISSION_ID, FULL_NAME, EMAIL, CAMARO_YEAR, CAMARO_MODEL, SAID_TO_PURCHASE,
ACTUALLY_PURCHASED, WERE_PICS_UPLOADED, EMAILED_NO_PICS_SENT, ADMIN_COMMENTS, GALLERY_CREATED, EMAILED_NO_GALLERY,
WERE_PICS_GOOD_ENOUGH, EMAILED_PICS_NOT_GOOD FROM user_submissions WHERE WERE_PICS_UPLOADED = 'YES'
AND WERE_PICS_GOOD_ENOUGH = 'NO' ORDER BY SUBMISSION_ID";

(removing "OR WERE_PICS_GOOD_ENOUGH = ' ' ") t then it WON'T select the tables for those where "WERE_PICS_GOOD_ENOUGH" is BLANK.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: "OR" not working in MYSQL SQL

Post by Christopher »

Operators are evaluated left to right, and AND has a higher precedence than OR, so "A AND B OR C" is the same as "(A AND B) OR C". I think what you want is "A AND (B OR C)".
(#10850)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: "OR" not working in MYSQL SQL

Post by califdon »

It is risky to use both AND and OR in any boolean notation without using parentheses. If I told you, "Please show me all the dogs AND show me all the cats OR show me all the pigs AND show me all the cows," that can be interpreted in several entirely contradictory ways:
  1. (dogs AND cats) OR (pigs AND cows)
  2. dogs AND (cats OR pigs) AND cows
  3. dogs AND (cats OR (pigs AND cows))
You need to analyze what you want the outcome to be and put parentheses around the boolean operators to give you that.
Post Reply