Page 1 of 1

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

Posted: Tue Aug 26, 2008 3:41 pm
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");

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

Posted: Tue Aug 26, 2008 4:57 pm
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
 

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

Posted: Tue Aug 26, 2008 5:08 pm
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

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

Posted: Tue Aug 26, 2008 5:52 pm
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.