Using PHP to group and display MySQL entries by weeks

I want to display them grouped by Month and Year, like so:

<p>
    <div>August 2009</div>
     <div class="fav">title 1</div>
     <div class="fav">title 5</div>
     <div class="fav">title 6</div> 
    etc...
</p>

 <p>
    <div>July 2009</div>
     <div class="fav">title 3</div>
     <div class="fav">title 8</div>
     <div class="fav">title 9</div> 
    etc...
</p>

Here is my query...

 $fav = mysql_query("SELECT * FROM links WHERE user_id='{$row['id']}' GROUP BY post_id ORDER BY id DESC");
    $TMPL = array (); $skin = new skin('mylinks/my'); $profile = '';

    while($myfav = mysql_fetch_array($fav))
    {
        $favs = mysql_query("SELECT * FROM imdb WHERE id='{$myfav['post_id']}' LIMIT 100");
            while($myfavs = mysql_fetch_array($favs))
            {   
                $TMPL['profiles'] = '<div class="fav"><a href="/movie/'.$myfavs['id'].'"><img src="/'.$myfavs['poster'].'" title="Posted '.$myfavs['date'].'" /></a></div>';
                $profile .= $skin->make();
                }           
            }

How would I go about grouping them, and then looping to display as above?

Any help would be greatly appreciated.

Answers


If I'm reading it correct, you can merge the two selects and tell SQL to let you know what group they're a part of.

SELECT *, YEAR(`date`) AS dy, MONTH(`date`) AS dm
FROM links
LEFT JOIN imdb WHERE imdb.id = links.post_id
WHERE links.user_id='{$row['id']}'
GROUP BY links.post_id
ORDER BY links.id DESC

after that you can build your own group of entries.

$entries = array();

while($entry = mysql_fetch_array($result))
{   
    $entries[$entry['dy']][$entry['dm']] = $entry;
}

that should give you a three-dimensional array of entries, grouped by year and month. After that you just traverse $entries and display the data grouped.

Let me know if you wanted something else.


Need Your Help

How to invoke the application Using Mysql Command

mysql phpmyadmin

I am writing a trigger, in which i need to invoke the external application using the MySQL

filter ng-repeat yet preserve initial length / count

javascript angularjs angularjs-ng-repeat angularjs-filter

I have been extending one of the demos on angularjs site to add more dynamic filtering.