Change year and new problem with query

Databases
1

Happy new year.

I have problem with this query MySQL:

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

In your WHERE clause it starts

WHERE 1

Which field is it meant to be looking in?

3

“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 –


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

Problem solved, thanks genius. :slight_smile:
Happy New Year!