Would someone please try & modify this query in a way that it shows the count for how many users have registered for the current day ?

So like right now it should display a count of 2 and then come midnight it would show 0 and then incrementally go up for every user that registers for the day and then the following day at midnight reset to show 0 and start the count all over again.

All times in the registered table are in a unix format (1288796722).

$request = mysql_query("SELECT COUNT(`registered`) AS total FROM `ucard`");
list($latestmember3) = mysql_fetch_row($request);

Ps; I could be wrong but did I over complicate the already provided code and if so how could it be cleaned up ?

7 Years
Discussion Span
Last Post by MoreBloodWine

I sort of got it working, I just don't know what to set for the = ''

Everything I tried didnt work...

$request = mysql_query("SELECT COUNT(`registered`) FROM `ucard` WHERE `registered` = ''");
list($latestmember3) = mysql_fetch_row($request);


You have to group your query by the date:

select date(from_unixtime(registered)) as theDate, count(*) from ucard group by date(from_unixtime(registered))

For further help please submit the table structure and some test data.


Structure: [img]http://www.myu2sig.com/registeredstructure.gif[/img]

As far as test data goes since everything is being pulled from one row name registered and is in UNIX format all you really need is maybe three UNIX times matching todays date. So then the output of the query should read three. If you memove one it should read two, come midnight in 12hrs and change it should roll to 0 and start the count all over again for tomorrow and every day therafter at midnight.

The overall goal of the query is to display the number of users who have registered for the current day.


Problem appears to have been solved with the following query, will know more come a new add to see if the count goes up and come midnight to see if the count reads 0.

$request = mysql_query("SELECT COUNT(*) FROM `ucard` WHERE DATE(FROM_UNIXTIME(registered)) = DATE(NOW())");

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.