Daily queries

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
hame22
Forum Contributor
Posts: 214
Joined: Wed May 11, 2005 5:50 am

Daily queries

Post by hame22 »

Hi I have a web site which allows members to register to it

When a user regsiters I store the date on whihc they do this.

From this data I now want to display how many memebers have signed up on each day since the site was launched, so I can display something like "2006-04-17 : 13 members" for each day

any ideas what sort of query i should run, was thinkin of using a distinct for the day then counting the number of registartions fromit?

thanks in advance

alex
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

why not just do:

Code: Select all

SELECT
  *
FROM
  table_name
WHERE
  join_date = $today
and do a count on that or whatever? somthing simple like that
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

Query should be

Code: Select all

SELECT COUNT(*)
FROM
  `table_name`
GROUP BY
  `join_date`;
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

How is your date stored in the database (unix_timestamp or actual Date/Time value)? At some point in your query you are going to want to make sure that all dates that could be the same are selected along the same format. Then I would suggest using DISTINCT in combination with GROUP BY. As for counting, well, take a look at the queries that were presented in the posts before this for how you would use COUNT in conjunction with DISTINCT to get what you want. If you still need help, post back.
Post Reply