Geek Answers Handbook

MySQL Query to display the number of comments my site received every day?

I launched an online journal and would like to skip the easy way to track 3 indicators:

  • How many comments are left every day.
  • How many links you send to my users.
  • How many new members I get every day.

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

I have a comment table with approximately 3,500 comments. Each comment receives a row in the table. One of the columns in the table is a “timestamp”.

I assume there is a query that selects this table, sorts the rows by timestamp, groups them in 24-hour increments, and then counts the number of rows in each group - letting me know how many new comments I received every day.

What will this query look like? I think I can find out others if I have my first job.

+6
mysql drupal
source share
2 answers

This snippet will display your results in a thematic 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) does not work because comment.timestamp is in UNIX_TIMESTAMP format, while DATE () expects an ASCII date.

+7
source share

Use the date_format () function to format dates;

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

More articles:


All Articles