Page 1 of 1

How many users registered TODAY...

Posted: Wed Nov 02, 2005 7:58 pm
by seodevhead
I am writing a php/mysql application and I'm trying to display a statistic of how many users registered today.

The 'users' table contains all the registered users and the 'registration_date' column is of DATETIME format and I used the NOW(); function when users register.

What SQL query do I run to count the number of users that registered TODAY? I know it should be something like...

"SELECT COUNT(*) FROM users WHERE registration_date.... "

Any help would greatly be appreciated! Thanks again.

Posted: Wed Nov 02, 2005 8:49 pm
by anthony88guy
something like

SELECT * FROM `users` WHERE registration_date >= time()-86400

just my 2 cents...

Posted: Wed Nov 02, 2005 8:54 pm
by John Cartwright
Your going to need to make a function to get the first second of the day, and the last second and then use the BETWEEN clause to get a more accurate registers members today.. not in the last 24hours

Posted: Wed Nov 02, 2005 9:04 pm
by seodevhead
Jcart wrote:Your going to need to make a function to get the first second of the day, and the last second and then use the BETWEEN clause to get a more accurate registers members today.. not in the last 24hours
Isn't there a way to do it all in the SQL query?? I thought the query below would work, but I'm getting wierd results. Any ideas?

$query = "SELECT COUNT(*) FROM users WHERE DATE_FORMAT(registration_date, '%Y-%c-%e') = CURDATE()";

Posted: Wed Nov 02, 2005 9:10 pm
by Burrito
if the field is a datetime field use something like this:

Code: Select all

$query = "select count(id) from myTable where date(dateField) = '".date("Y-m-d")."'";

Posted: Wed Nov 02, 2005 9:18 pm
by seodevhead
Burrito wrote:if the field is a datetime field use something like this:

Code: Select all

$query = "select count(id) from myTable where date(dateField) = '".date("Y-m-d")."'";
I'm getting a "supplied argument is not a valid MySQL result resource" error.

I of course replaced the sections applicable to my db table. Here is my query:

Code: Select all

$query = "SELECT COUNT(*) FROM users WHERE date(registration_date) = '".date("Y-m-d")."'";
Any ideas?

Posted: Wed Nov 02, 2005 9:32 pm
by Burrito
try creating an alias for your selection:

select count(*) as theCount....

I'm pretty sure that's not your problem though, so post some code...

Posted: Wed Nov 02, 2005 9:40 pm
by Burrito
do you have it throwing an error if the query fails?

ie

Code: Select all

$result = mysql_query("select blah blah")
   or die(mysql_error());
if not that's a good place to start troubleshooting.

solution

Posted: Wed Nov 02, 2005 11:52 pm
by smartknightinc
hi,

best query is

select * from tbluser where date_format(flddatetime,'%d-%Y')=date('%d-%Y')


:) thanks