CODEWITHSUNDEEP
phpmysqldatabaseblogsdate
Chris
Chris

Reputation: 1

PHP - Displaying Blog Menu by Date

I have created a function to display the dates and the count of posts within those dates to be used as my blog menu, however it only partially works, any help with this would be appreciated.

Function being called:

function displayBlogMenu()
{
    blogDBSelect();
    $sql = "SELECT datePosted FROM blog_entries";
    $results = mysql_query($sql);           
    while($post = mysql_fetch_array($results))
    {
        $startDate = date('Y-m-01', strtotime($post['datePosted']));
        $endDate = date('Y-m-01',  strtotime("+1 month", strtotime($post['datePosted'])));  
        $sql2 = "SELECT * FROM blog_entries WHERE datePosted >= '$startDate' AND datePosted < '$endDate'";
        $results2 = mysql_query($sql2); 
        $count = mysql_num_rows($results2);
    }                                   
    echo '<a href="">'.date('F, Y', strtotime($startDate)).' ('.$count.' Posts)</a><br>';
}

I want the function to return the date as links such as:

June, 2011 (10 Posts)
July, 2011 (2 Posts)

Upvotes: 0

Views: 413

Answers (1)

Pentium10
Pentium10

Reputation: 208022

It looks like you can do that by one single query

SELECT Date_format(dateposted, '%M, %Y'), 
       COUNT(*) AS cc 
FROM   blog_entries 
GROUP  BY YEAR(dateposted), 
          MONTH(dateposted) 
ORDER  BY COUNT(*) DESC, 
          dateposted DESC

Upvotes: 4

Related Questions