[Resolved] [Resolved] [Resolved] DATETIME Field: Selecting Specifics

[ThaSpY]

I have a users table with a date joined column in the format of 0000-00-00 00:00:00. I would like to select every row in a MySQL SELECT query with the rows that have joined the current day, and a new SELECT query that will grab rows joined the previous day, and again for the current month, and finally for the current year (4 queries). Any ideas on how to do this would be greatly appreciated!

Thanks.

[dnszero]

Check out the DATE and TIME functions section the mysql manual online. Especially CURDATE() and DATE_SUB(). They're very easy to use.

[ThaSpY]

Thanks for your answer! I got the Month and Year problem solved using MONTH(datejoined) and YEAR(datejoined), but I'm still stumped on getting the current day and previous day. What is the syntax to select the XX out of this format: 0000-00-XX 00:00:00 and I guess to get previous day, I just go XX - 1?

[ThaSpY]

Okay I have current day figured out using the following code. I have changed the field type from DATETIME to just DATE by the way. Logging the time to the exact second seems redundant.

$curdate = date("Y-m-d");
$dayresult = mysql_query("select * from admin_users where joindate = '$curdate'");
$daycount = mysql_num_rows($dayresult);
echo "Users who have joined today: " . $daycount . "<br>";

Now if I could just use a similar query to select the users joined yesterday instead of today...😕

[ThaSpY]

Sorry problem solved. Figured it all out on my own. Thanks for the help on the MySQL side though!

(Edit: Hah, you can tell I'm not patient. Accidently clicked Resolved three times :eek:!)