SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Change year and new problem with query

    Happy new year.

    I have problem with this query MySQL:

    Code:
    SELECT * FROM TBL_Q 
    WHERE 1 
    AND 
    ( DATAREG = CURRENT_DATE 
    OR 
    DATAREG >= CURRENT_DATE - INTERVAL 7 DAY ) 
    AND YEAR(DATAREG) = YEAR(NOW()) 
    ORDER BY 
    MONTH(DATAREG) DESC, 
    RAND() LIMIT 0, 1
    In the TBL_Q rows terminating with datareg = 2009-12-31.

    Now 2010-01-01 the query no return resultset.

    Can you help me?

    Kind regards and happy new year.
    Viki

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    In your WHERE clause it starts

    Code SQL:
    WHERE 1

    Which field is it meant to be looking in?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,332
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    "WHERE 1" is a common coding trick used when building queries dynamically

    its use is non-standard (other database systems will throw a wobbly), and mysql evaluates 1 as TRUE

    in dynamic queries, this allows additional conditions to be appended with ANDs, without the extra conditional coding required to check whether any condition exists at all (in which case WHERE 1 by itself will return all rows)

    it is normally written as WHERE 1=1 (which works in all database systems)

    in this particular query, it is not needed

    neither, it would seem, are two of the other three conditions

    viki, try this --
    Code:
    SELECT * FROM TBL_Q 
    WHERE datareg >= CURRENT_DATE - INTERVAL 7 DAY
    ORDER BY MONTH(datareg) DESC, RAND() LIMIT 0, 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    "WHERE 1" is a common coding trick used when building queries dynamically

    its use is non-standard (other database systems will throw a wobbly), and mysql evaluates 1 as TRUE

    in dynamic queries, this allows additional conditions to be appended with ANDs, without the extra conditional coding required to check whether any condition exists at all (in which case WHERE 1 by itself will return all rows)

    it is normally written as WHERE 1=1 (which works in all database systems)

    in this particular query, it is not needed

    neither, it would seem, are two of the other three conditions

    viki, try this --
    Code:
    SELECT * FROM TBL_Q 
    WHERE datareg >= CURRENT_DATE - INTERVAL 7 DAY
    ORDER BY MONTH(datareg) DESC, RAND() LIMIT 0, 1

    Problem solved, thanks genius.
    Happy New Year!


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
  •