Echo number of rows per group by

Question

I have a mysql query in which I group each post by the date is was posted. I want to be able to echo the amount of posts that happened each day, know a way?

Here is what I've written so far which does post a new <div> for each day it was posted.

$trendQuery = "
SELECT DATE(timestamp) AS ForDate,
    COUNT(*) AS NumPosts
 FROM   trends
 WHERE `trend` = '" . $_GET['graph'] . "'
 GROUP BY DATE(timestamp)
 ORDER BY ForDate
";

$result = mysql_query($trendQuery);
    while ($row = mysql_fetch_array($result)) {
        echo'
        <div>
            <p>Here is where I want to say how many posts that happened!</p>
    </div>
    ';
}

Does `echo $row['NumPosts'];` not work? Or am I missing something? — Amal Murali, Nov 18 '13 at 11:52
Remember to not use the deprecated `mysql_*` functions: http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — Ron van der Heijden, Nov 18 '13 at 12:18

score 1 · Accepted Answer · answered Nov 18 '13 at 11:52

You have your query pretty much set up. To echo the results, you simply need to refer the columns with the alias name, in your case ForDate and NumPosts

$trendQuery = "
SELECT timestamp AS ForDate,
    COUNT(*) AS NumPosts
 FROM   trends
 WHERE `trend` = '" . $_GET['graph'] . "'
 GROUP BY timestamp
 ORDER BY ForDate
";

$result = mysql_query($trendQuery);
    while ($row = mysql_fetch_array($result)) {
        echo'
        <div>
            Date: '.$row['ForDate'].' ---- Number of Posts: '.$row['NumPosts'].'<br />
    </div>
    ';

score 0 · Answer 2 · answered Nov 18 '13 at 11:57

0

Create a view of your trend query and inner join it with "Select (Id or day that you want to merge) From trend"

i hope that helps

answered Nov 18 '13 at 11:57

Nisarg Desai

1
1

Sorry Actually I think that You Want To fetch another day or id column that you want to print with this group column – Nisarg Desai Nov 18 '13 at 12:25

Echo number of rows per group by

2 Answers2