Display 'new' AND 'read' messages however displays all?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Display 'new' AND 'read' messages however displays all?

Post by JAB Creations »

The following MySQL query is intended to display only messages where the id_for_folder column/row is set to "new" or "read" however it is undesirably pulling all the messages. What is wrong with my syntax?

Code: Select all

mysql_query("SELECT id, id_for_folder, id_from, date_0, date_1, message, subject FROM `public_private_messages` WHERE id_for = '".$_SESSION['member']."' OR id_from = '".$_SESSION['member']."' AND id_for_folder = 'new' AND id_for_folder = 'read' ORDER BY `public_private_messages`.`date_0` DESC");
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Display 'new' AND 'read' messages however displays all?

Post by jayshields »

Try this

Code: Select all

SELECT 
id, id_for_folder, id_from, date_0, date_1, message, subject 
FROM 
`public_private_messages` 
WHERE 
id_for = 'whatever' 
AND 
(id_for_folder = 'new' 
OR 
id_for_folder = 'read')
ORDER BY `public_private_messages`.`date_0` DESC
 
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Display 'new' AND 'read' messages however displays all?

Post by JAB Creations »

Thank you for that subtle clarification Jay! I took a look at the code and eventually I noticed the parenthesis for two or more I suppose "types". It's sort of a pattern I've noticed. For me to learn this stuff lots of working chunks of code that are useful help me learn even things later down the road so seeing the parenthesis (even without knowing why they were being used) helped me figure this out. Otherwise I'd have to ask you for clarification.

Here is the working code for anyone who may find this useful as an archive...

Code: Select all

SELECTid, id_for_folder, id_from, date_0, date_1, message, subjectFROM`public_private_messages`WHERE(id_for = '".$_SESSION['member']."' OR id_from = '".$_SESSION['member']."')AND(id_for_folder = 'new' OR id_for_folder = 'read')ORDER BY `public_private_messages`.`date_0` DESC
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Display 'new' AND 'read' messages however displays all?

Post by jayshields »

I was going to suggest what you found to be the solution but I thought it would be a bad idea because then it would show everything thats been sent and received from/by the same user - effectively combining a "Sent Messages" and "Inbox", which wouldn't be ideal.

Bracket logic is the same in SQL as any other programming language, the brackets might not even be needed, but it definately increases readability if not.
Post Reply