[SOLVED] Date queries

[thaxroads]

I have a cms site I'm making and I want to add a section in the admin area where it shows how many users joined:

 

TODAY

YESTERDAY

THIS MONTH

LAST MONTH

THIS YEAR

LAST YEAR

 

The queries I have so far:

 

$query="SELECT count(*) FROM users WHERE joindate=now()-INTERVAL 24 HOUR"; // TODAY

$query="SELECT count(*) FROM users WHERE joindate=now()-INTERVAL 1 DAY"; // YESTERDAY

$query="SELECT count(*) FROM users WHERE YEAR(joindate)=YEAR(now()) AND MONTH(joindate)=MONTH(now())";

$query="SELECT count(*) FROM users WHERE YEAR(joindate)=YEAR(now()-INTERVAL 1 MONTH) AND 
MONTH(joindate)=MONTH(now()-INTERVAL 1 MONTH)";

$query="SELECT count(*) FROM users WHERE YEAR(joindate)=YEAR(now())";

$query="SELECT count(*) FROM users WHERE YEAR(joindate)=YEAR(now()-INTERVAL 1 YEAR)";

 

of course the first two codes which are today & yesterday queries do not work ... the dates are in the MySQL database in this format: 0000-00-00 00:00:00 ... can someone help me out with the correct queries I'm looking for ..

 

Any help would be appreciated

 

Thanks

 

[Chicken Little]

Try using CURDATE, this should get you yesterday

 

SELECT * FROM users
WHERE DATE( joindate ) <= DATE_ADD( CURDATE( ) , INTERVAL -1
DAY ) 
ORDER BY joindate DESC

 

[thaxroads]

Try using CURDATE, this should get you yesterday

 

SELECT * FROM users
WHERE DATE( joindate ) <= DATE_ADD( CURDATE( ) , INTERVAL -1
DAY ) 
ORDER BY joindate DESC

 

 

Thanks ... this is what I came up with after your input about CURDATE():

 

$query="SELECT count(*) FROM users WHERE DATE(joindate)=CURDATE()"; //USER JOINED TODAY
$query="SELECT count(*) FROM users WHERE DATE(joindate)=DATE_ADD(CURDATE(), INTERVAL -1 DAY)"; // USERS JOINED YESTERDAY

 

Works like a charm!

 

Thanks Again!!