daily new members count

[n3p3]

hello everyone,

 

In my script, when a user registers I use a datetime field in db (mysql) to store registration date&time of users. What I want to do is, for my statistics module I want to show daily members count for a time period..for example,

date1 - x users

date2 - y users

date3 - z users

...

 

whats the best way to do this?

Thanks

[ignace]

-- Signups today
SELECT count(*) as todays_new_members FROM members m WHERE m.signup_date = NOW()
-- Signups this month
SELECT count(*) as new_members_this_month FROM members m WHERE month(m.signup_date) = month(now())
-- Signups this year
SELECT count(*) as new_members_this_year FROM members m WHERE year(m.signup_date) = year(now())
-- Total signups
SELECT count(*) as total_registrations FROM members

 

There are probably better (more performant) solutions.

 

My 666th Post

[n3p3]

thanks for quick response..

 

How can we show the numbers day by day in the last week for example?

[ignace]

Although not sure i think this might pull it off.

 

SELECT * FROM members m WHERE week(m.signup_date, 1) = week(now(), 1) - 1

 

It's up to your implementation to display it for each and every day. Please note that for mysql the week starts on sunday. Edit: I modified the code now it starts on monday.