How many users registered TODAY...

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

How many users registered TODAY...

Post 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.
anthony88guy
Forum Contributor
Posts: 246
Joined: Thu Jan 20, 2005 8:22 pm

Post by anthony88guy »

something like

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

just my 2 cents...
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post 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()";
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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")."'";
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post 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?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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...
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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.
smartknightinc
Forum Newbie
Posts: 4
Joined: Wed Nov 02, 2005 11:45 pm

solution

Post by smartknightinc »

hi,

best query is

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


:) thanks
Post Reply