SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sql query

    Hi I cant get this to work:

    Code:
    $sql = mysql_query("SELECT orderid, userid, total, DATE_FORMAT(orderdate, '%d %M %y') AS orderdate, approved FROM cart WHERE approved = 1 OR 2 AND userid = '".$_SESSION['userid']."' ORDER BY orderid DESC") or die(mysql_error());
    I need it to grab records where

    approved holds value 1 or 2 and userid = userid

    The code above brings back approved with the value of 3 as well 1 and 2 ..

    How should it be?


    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you have this --
    Code:
     WHERE approved = 1 
        OR 2 
       AND userid = $_SESSION['userid']
    since ANDs have precedence over ORs, this is evaluated as --
    Code:
     WHERE ( 
           approved = 1 
           )
        OR (
           2 
       AND userid = $_SESSION['userid']
           )
    in any other database system, you would've gotten an error message, because 2 by itself is not a condition

    however, in mysql, 2 is true!

    so, for every row, if either approved = 1 or userid = $_SESSION['userid'], then that row is returned

    there are two ways to fix it --
    Code:
     WHERE (
           approved = 1 
        OR approved = 2 
           )
       AND userid = $_SESSION['userid']
    or --
    Code:
     WHERE approved in ( 1 , 2 )
       AND userid = $_SESSION['userid']
    note: if userid is numeric, do not use single quotes around $_SESSION['userid']
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard holmescreek's Avatar
    Join Date
    Mar 2001
    Location
    Northwest Florida
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not being an ****, but shouldn't this be over in the MySQL forum? It's not directly PHP related, but, I imagine there are folks in the MySQL forum that might be looking for a thread with the helpful answer that was provided.
    intragenesis, llc professional web & graphic design

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    moved

    and thanks for the kind words, john
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    724
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK ..

    Thanks for the help .. I got it working but I am setting variables for if()


    $approved = "in (1 , 2)";

    $approved = "= 3";

    WHERE approved $approved AND userid = ".$_SESSION['userid']."


    Cheers


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
  •