Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Jul 2010
    Location
    Sheffield
    Posts
    827
    Thanks
    93
    Thanked 18 Times in 18 Posts

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

    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

    PHP Code:
    $Sql "SELECT user_id, username, password FROM members WHERE username = '$username' AND password = '$password'"
    Would i have to write them all out e.g.
    PHP Code:
    $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?

  • #2
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    You didn't include 5 in your list, is that intentional?

    If it was:
    Code:
    $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:
    Code:
    $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.
    Last edited by oracleguy; 09-12-2011 at 12:21 AM.
    OracleGuy

  • Users who have thanked oracleguy for this post:

    tomharto (09-12-2011)

  • #3
    Regular Coder
    Join Date
    Jul 2010
    Location
    Sheffield
    Posts
    827
    Thanks
    93
    Thanked 18 Times in 18 Posts
    Yeah i didnt include it on purpose, Thanks for that, ill give it a go

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,635 Times in 4,597 Posts
    And that works when most of your range is sequential.

    But you can also do this:

    Code:
    $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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    Jul 2010
    Location
    Sheffield
    Posts
    827
    Thanks
    93
    Thanked 18 Times in 18 Posts
    That could also be userful for my site too, thanks . I guess IN means "is in this list of values"?

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,635 Times in 4,597 Posts
    Quote Originally Posted by tomharto View Post
    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:
    Code:
    ... WHERE name IN ('joe','ann','bob','kate')
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    tomharto (09-12-2011)

  • #7
    Regular Coder
    Join Date
    Jul 2010
    Location
    Sheffield
    Posts
    827
    Thanks
    93
    Thanked 18 Times in 18 Posts
    Okay, thanks


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •