Count number of times a date is in DB

Question

Hi

Im looking for some help creating a graph using the google chart API

I have some idea of how i want to code it, but i need help turning psuedo into real code.

I have a database with site users. Each user has a date that they joined. I want to count how many people join on each date, then put this into a string for the google chart API url.

im thinking something like this, but need some help:

Set up DB connection etc...

select date from site_users, count and group by date_joined under column called 'number', as to give a 2 column table result -> Date and Number of joins.

while (sql query){
    $date .= $row['date_joined']."|";
    $number .= $row['number']."|";
}

construct google chart url
$url = "www.google.cometc...".$date."&".$number;

<img src=".&url." alt="chart" />

Some help would be great, i think the part im struggling with is the sql statement.

Thankyou

Answer 1

Here is correct SQL, but you'll have to fix the specific field names:

select date,count(*) as number
from site_users
group by date;

If the date field is a timestamp or other type which contains times as well, a truncation is needed (in MySQL):

select DATE_FORMAT(date,'%Y-%e-%d') as date,count(*) as number
from site_users
group by DATE_FORMAT(date,'%Y-%e-%d');

Answer 2

SELECT date, count(*) as users FROM site_users GROUP BY date;

or if you need WHERE:

SELECT date, count(*) as users FROM site_users
    WHERE date > '2008-01-01'
    GROUP BY date;

The GROUP BY is the important part. It generates an aggregate report. What you're count()ing is the number of rows in each GROUP (in this case, date).