SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    a fresh, new start... dujmovicv's Avatar
    Join Date
    Aug 2006
    Location
    Earth
    Posts
    559
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    multiple conditions in query

    Hi All!

    I was wondering if it's possible to set multiple conditions in a query like :
    Code:
    SELECT * FROM contacts WHERE asker_id = '$user_ID' OR receiver_id = '$user_ID' AND status = 1
    My goal is to find out how many CONFIRMED (status = 1) connections a user with the given ID has...

    Hope someone can help....

    Full time ADMIN - art community
    Part time coder - dsign

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yes, that's possible

    although you will want to use parentheses when you are mixing ANDs and ORs

    these two are fundamentally different --

    WHERE ( asker_id = '$user_ID' OR receiver_id = '$user_ID' ) AND status = 1

    WHERE asker_id = '$user_ID' OR ( receiver_id = '$user_ID' AND status = 1 )

    if you leave out the parentheses, it's the same as the second one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by dujmovicv View Post
    Hi All!

    I was wondering if it's possible to set multiple conditions in a query like :
    Code:
    SELECT * FROM contacts WHERE asker_id = '$user_ID' OR receiver_id = '$user_ID' AND status = 1
    My goal is to find out how many CONFIRMED (status = 1) connections a user with the given ID has...

    Hope someone can help....
    Yes, it's possible. But once you're using AND and OR in one query, you'll have to tell MySQL the order of things (using parentheses):
    Code MySQL:
    SELECT * 
    FROM contacts 
    WHERE (asker_id = '$user_ID' OR receiver_id = '$user_ID')
    AND status = 1
    If you don't use ( and ), then the outcome might not be what you expect

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i hate that default mysql colour coding

    those lime green parentheses are practically invisible, and they're the most important part of the statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    a fresh, new start... dujmovicv's Avatar
    Join Date
    Aug 2006
    Location
    Earth
    Posts
    559
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you guys! That's exactly what I needed!

    Quote Originally Posted by r937 View Post
    i hate that default mysql colour coding

    those lime green parentheses are practically invisible, and they're the most important part of the statement
    By the way, I agree with r937....

    Have a nice day!

    Full time ADMIN - art community
    Part time coder - dsign


Bookmarks

Posting Permissions

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