SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot Beckwith's Avatar
    Join Date
    Mar 2004
    Location
    California
    Posts
    149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Where Clause Organization

    I'm trying to get this query to work:

    Code:
     SELECT items.id,items.title,items.category,items.expiration,items.price,items.pic1,items.pic2,items.pic3,items.picture,purchases.item_number FROM `items`,`purchases` WHERE items.expiration > '$now' AND purchases.item_number!=items.id AND items.picture != '' OR items.pic1 != '' OR items.pic2 != '' OR items.pic3 != '' ORDER BY RAND() LIMIT 4
    items.expiration has to evaluate true, and purchases.item_number can't equal items.id. The problem is that when I add the pic part at the end, if one of those evaluates true, the whole thing's true. So, how do I organize it so that one of the pic fields is not empty, and it's not expired and it hasen't been purchased? Thanks

  2. #2
    SitePoint Member Roj's Avatar
    Join Date
    Jul 2006
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Hi,

    Please try this

    Code:
     SELECT items.id,items.title,items.category,items.expiration,items.price,items.pic1,it  ems.pic2,items.pic3,items.picture,purchases.item_number FROM `items`,`purchases` WHERE items.expiration > '$now' AND purchases.item_number!=items.id AND (items.picture != '' OR items.pic1 != '' OR items.pic2 != '' OR items.pic3 != '') ORDER BY RAND() LIMIT 4
    Roj Kumar Shahi
    Web Developer from Nepal

  3. #3
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Also, replace "WHERE items.expiration > '$now'" with "WHERE items.expiration > CURRENT_TIMESTAMP". No need to slow down the query parsing a date from PHP.


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
  •