mySQL Query to list number of comments my site received each day?

Question

I run an online magazine and would like a dead-simple way to track 3 metrics:

1. How many comments are left each day.
2. How many links my users submit.
3. How many new members I get each day.

This information is all my database, I'm just not sure how to get it out.

I have a "comments" table with about 3500 comments. Each comment gets a row in the table. One of the columns in the table is "timestamp."

I'm assuming there's a query that will select this table, sort the rows by timestamp, group them in 24-hour increments and then count the number of rows in each group - telling me how many new comments I received each day.

What would that query look like? I think I can figure out the other ones if I had the first one working.

Answer 1

Use the date_format() function to format the dates;

select count(id) as commentCount, date_format(dateColumn, '%Y-%m-%d') as commentDate
from yourTable
group by commentDate

Answer 2

This fragment will display your results in a themed table:

$sq = 'SELECT COUNT(*) cnt, DATE(FROM_UNIXTIME(timestamp)) day '
    . 'FROM {comments} c '
    . 'GROUP BY 2 '
    . 'ORDER BY 2 DESC';
$q = db_query($sq);
$stats = array();
while ($o = db_fetch_object($q)) {
  $stats[$o->day] = array($o->day, $o->cnt);
}
return theme('table', NULL, $stats));

Using DATE(timestamp) doesn't work because comments.timestamp is in UNIX_TIMESTAMP format, whereas DATE() expects an ASCII date.