...

View Full Version : (PHP) Mysql select where `field` = 4,6,7,8,9,10,11,12,13 or 14



tomharto
09-11-2011, 10:48 PM
I have a users table containing there level, and i need to run a Sql to see if a members level is either 4,6,7,8,9,10,11,12,13 or 14.

My SQL atm is


$Sql = "SELECT user_id, username, password FROM members WHERE username = '$username' AND password = '$password'";

Would i have to write them all out e.g.

$Sql = "SELECT user_id, username, password FROM members WHERE username = '$username' AND password = '$password'"; AND `level` = '4' OR username = '$username' AND password = '$password'"; AND `level` = '6' OR username = '$username' AND password = '$password'"; AND `level` = '7' etc etc etc Or is there a better way to do it?

oracleguy
09-11-2011, 11:18 PM
You didn't include 5 in your list, is that intentional?

If it was:

$Sql = "SELECT user_id, username, password FROM members WHERE username = '$username' AND password = '$password'" AND (level = 4 OR level BETWEEN 6 AND 14)

If it wasn't:


$Sql = "SELECT user_id, username, password FROM members WHERE username = '$username' AND password = '$password'" AND level BETWEEN 4 AND 14


If your level field is a number you don't need to put quotes around the number you are comparing against. MySQL will let you get away with doing it but other SQL dialects will not so it isn't a good habit to get into.

tomharto
09-11-2011, 11:19 PM
Yeah i didnt include it on purpose, Thanks for that, ill give it a go :)

Old Pedant
09-12-2011, 06:56 PM
And that works when most of your range is sequential.

But you can also do this:



$Sql = "SELECT user_id, username, password FROM members "
. " WHERE username = '$username' AND password = '$password' "
. " AND level IN (4,6,7,8,9,10,11,12,13,14) ";


Which would be more practical if your list were, for example, (4,23,99,107,388,1011,1033) or such.

tomharto
09-12-2011, 06:59 PM
That could also be userful for my site too, thanks :). I guess IN means "is in this list of values"?

Old Pedant
09-12-2011, 08:22 PM
That could also be userful for my site too, thanks :). I guess IN means "is in this list of values"?

Yes. NOTE: If the field in question is *NOT* numeric, then you have to put apostrophes around *each* value:


... WHERE name IN ('joe','ann','bob','kate')

tomharto
09-12-2011, 08:24 PM
Okay, thanks :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum