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