[RESOLVED] 1 query sorting data into groups?

coldwerturkey

If I have a list of lets say posts from a blog on a database, with the date formate YYYYMMDD, and I want to list all the titles of these posts under their unique months, is it possible to do that in one or two querys?

ie)

Month Title x
-post (month x)
-post (month x)
-post (month x)

Month Title y
-post (month y)
-post (month y)
etc..

instead of one long list (without the "Month Title .." separations)..

Normally, I would do a query to find the latest date, break it down, compare it to todays date, find the number of months between, than do a query for each of those months in between.. seems kinda tedious and unnecessary to me..

Thanks in advanced for any input!

bpat1434

Sure!! Just grab whatever columns you want, sort by the month 😉 Then loop over and keep track of when it changes:

<?php

$query = "SELECT `id`, `title`, `body`, MONTH(`timestamp`) AS `month`
          FROM `blog_posts`
          ORDER BY `month` DESC";
$result = mysql_query($query);

if(!$result)
    echo 'SQL Error ('.mysql_errno().'): '.mysql_error();
else
{
    echo '
    <table>
        <tr>
            <th>Post Title</th>
            <th>Description</th>
        </tr>';

$prev = false;
while($row = mysql_fetch_assoc($result))
{
    if($prev != $result['month'])
    {
        echo '
    <tr>
      <td colspan="2" style="background:#ddd;">' . $result['month'] . '</td>
    </tr>';
        $prev = $result['month'];
    }

    echo '
    <tr>
        <td><a href="/blog/post.php?id='.$row['id'].'">'.$row['title'].'</a></td>
        <td>' . substr($row['body'], 0, 128) . ' [...]</td>
    </tr>';
}

echo '
</table>';
}

Hope that helps.