AND doesnt seem to be working

Databases
1

my site,
http://houston-asp.com/community/post.php?id=3&topic=Welcome
produces some strange results, heres mt query
SELECT mess.subject, mess.message_txt, mess.mess_id, mess.parent_id, mess.created, members.name, mess.display
FROM messages AS mess
INNER JOIN members
ON members.id = mess.user_id
WHERE mess.parent_id = :id
OR mess.mess_id = :id
AND mess.display = 1
ORDER BY mess.created

Why is it displaying the message where the display is 0?

1920×1080 267 KB

2 
WHERE mess.parent_id = :id
OR mess.mess_id = :id
AND mess.display = 1

is interpreted as

WHERE mess.parent_id = :id
OR (mess.mess_id = :id
AND mess.display = 1)

(note the perentheses)
because AND has a higher precedence than OR

I’m guessing you wanted

WHERE (mess.parent_id = :id
OR mess.mess_id = :id)
AND mess.display = 1

instead, in which case you need to add those parentheses like I just did

2 Likes
3

When I have AND’s and OR’s in a WHERE clause I always use () to avoid this kind of unexpected behaviour.

4

As do I. Adds a bit more characters than needed, but a lot easier than remembering precedence order.

5

oh, didn’t know I could use ()s.

Thanks

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.