My brain! My brain!

[devi]

Any SQL experts online tonight? Help!


I'm sorting out the school intranet. It's got an area for each subject, and teachers are supposed to be able to post messages in their own subject areas - except that messages have been showing up all over the place most of the time. Like, a sociology message I posted showed up in Biology. A message someone else posted in the Japanese area showed up everywhere.

I've found out why. The offending bit of code's here (from the AS messages file - there's an identical one for A2 and another for GCSE):

$sql = "SELECT * FROM subjectmessages WHERE subject='$_POST[subject]'and display='yes' and level='a' or level='gen' or level='as' order by id desc";

It's been parsing that statement as "select messages that a) have the right subject and the level A, or b) have level 'gen' (never mind the subject), or c) have level 'as' (and the same disregard of subject)".

What I can't figure out, though, is how to write an SQL query which does what I want it to - that is, 'choose all messages that have the right subject and any one of these three levels'.

Suggestions?

Edit: It works! Woo! Thanks, syleth, ruudboy and mooism for all giving the right answer with spooky speed...

Comments

[ruudboy.livejournal.com]

You need brackets round the 'or' bit, like:

$sql = "SELECT * FROM subjectmessages WHERE subject='$_POST[subject]'and display='yes' and (level='a' or level='gen' or level='as') order by id desc";

[syleth.livejournal.com]

SELECT *
FROM subjectmessages
WHERE subject='$_POST[subject]'
AND display='yes'
AND (level='a' or level='gen' or level='as')
ORDER BY id DESC

another option would be the one below (which is prettier)

SELECT *
FROM subjectmessages
WHERE subject='$_POST[subject]'
AND display='yes'
AND level IN ('a','gen','as')
ORDER BY id DESC

[mooism.livejournal.com]

Not an SQL expert, but…
…can you not just use brackets?

$sql = "SELECT * FROM subjectmessages WHERE subject='$_POST[subject]'and display='yes' and (level='a' or level='gen' or level='as') order by id desc";

(Btw, putting $_POST[subject] into the query in that way is a potential security hole, but you don’t want to worry about that right now I suppose…)

[ravenblack.livejournal.com]

Shouldn't be a security problem if the PHP is configured with magic_quotes_gpm (may not be gpm; some group of three letters) set to 'on'. A setting I abhor, but I suppose it's alright for simple things like this.

[mooism.livejournal.com]

Point.

(Just I tend to notice such things, as my boss’s java is littered with them.)