CODEWITHSUNDEEP
phpmysqlcounttimestamp
McDan Garrett
McDan Garrett

Reputation: 183

Echo number of rows per group by

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>
    ';
}

Upvotes: 0

Views: 119

Answers (2)

Nisarg Desai
Nisarg Desai

Reputation: 1

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

Upvotes: 0

asprin
asprin

Reputation: 9823

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>
    ';

Upvotes: 1

Related Questions