+- 0Day Forums (https://zeroday.vip)
+-- Forum: Coding (https://zeroday.vip/Forum-Coding)
+--- Forum: CMS (https://zeroday.vip/Forum-CMS)
+---- Forum: Drupal (https://zeroday.vip/Forum-Drupal)
+---- Thread: mySQL Query to list number of comments my site received each day? (/Thread-mySQL-Query-to-list-number-of-comments-my-site-received-each-day)
mySQL Query to list number of comments my site received each day? - vicaratesvusszkp - 07-27-2023
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.
RE: mySQL Query to list number of comments my site received each day? - zarellaggedu - 07-27-2023
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
RE: mySQL Query to list number of comments my site received each day? - peripharyngeal835305 - 07-27-2023
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.