PHP - group and sort recordset by Year/Month

Got a "Neglected Question Alert" on this one.  Without your data base I can't give you tested code, but I can show you what I think would work.  Back in a moment...

See if this makes sense and please post back with any questions.

<?php // RAY_temp_ssailer.php
error_reporting(E_ALL);

mysql_select_db($database_blogtest, $blogtest);
$query_getBlog = "SELECT blogID, blogTitle, blogEntry, blogDate FROM blogdata ORDER BY blogDate DESC";
$getBlog = mysql_query($query_getBlog, $blogtest) or die(mysql_error());

// WHAT IS A DATE THAT WILL NOT BE IN THE RESULTS SET?
$old = date('Y-m-1', strtotime('TODAY + 2 MONTH'));

// THE QUERY ORDERED THE ROWS FOR US - USE AN ITERATOR TO GET EACH ROW
while ($row = mysql_fetch_assoc($getBlog))
{
    // MIGHT ADD SANITY CHECK FOR blogDate COLUMN?
    $ts  = strtotime($row['blogDate']);
    $now = date('Y-m-1', $ts);
    $mo  = date("F", $ts);
    $yr  = date("Y", $ts);

    // IF THE MONTH CHANGED
    if ($old != $now)
    {
        $old = $now;
        echo "<h2> $mo $yr </h2>";
        echo PHP_EOL;
    }

    // DISPLAY THE BLOG TITLE
    echo '<br/>' . $row['blogTitle'];
    echo PHP_EOL;
}

Select allOpen in new window

Sweet!  Close - very, very close, but there are actually two records for the month of March and only one is showing up on the output.  But it's really close!!!

Close - very, very close, but...

Without your data base I can't give you tested code, ...

But let me guess: Is March the first month in the record set?  And by any chance did you leave an unwanted and extraneous mysql_fetch function call in the code, something like the second line in this snippet:

$getBlog = mysql_query($query_getBlog, $blogtest) or die(mysql_error());
$row_getBlog = mysql_fetch_assoc($getBlog);

Select allOpen in new window

There is a reason why I removed that statement and instead used mysql_fetch in the while() iterator.  Each call to any of the mysql_fetch functions removes a row from the query results set.  In the code snippet below I have some examples of how to do the basics in PHP and MySQL.  Please read it over when you have a chance, especially the man page references.  If you truly understand these things, it will be much easier to get your programming working just the way you want, I promise!

If you want to post the code the way you have it now, having incorporated my changes, I will try to show you why you're losing the record and what to do about it.  I still cannot test for you, but I may be able to spot something that might cause the loss of one of the rows of the results set.  

Best regards, ~Ray

<?php // RAY_mysql_example.php
error_reporting(E_ALL);


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQL
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
// MAN PAGE: http://php.net/manual/en/function.mysql-query.php
// MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php



// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $err <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $err <br/>";
    die('NO DATA BASE');
}
// IF THE SCRIPT GETS THIS FAR IT CAN DO QUERIES




// ESCAPE ALL DATA FIELDS BEFORE USE IN MYSQL QUERIES
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, SHOW THE ERROR
if (!$res)
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/> $sql <br/>";
    die($err);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = mysql_num_rows($res);
$fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY FOUND NO DATA: ";
    echo "<br/> $sql <br/>";
}
else
{
    echo "<br/>QUERY FOUND $fmt ROWS OF DATA ";
    echo "<br/> $sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
while ($row = mysql_fetch_assoc($res))
{
    // ROW BY ROW PROCESSING IS DONE HERE
    var_dump($row);
}




// ANOTHER WAY OF DETERMINING HOW MANY ROWS WE HAVE IN A TABLE
$sql = "SELECT COUNT(*) FROM my_table";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($err);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = mysql_fetch_array($res, MYSQL_NUM);
$num = $row[0];
$fmt = number_format($num);
echo "<br/>THERE ARE $fmt ROWS IN THE TABLE";




// MAKING AN INSERT QUERY AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (username) VALUES ('$safe_username')";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/> $sql <br/>";
    die($err);
}

// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
$id  = mysql_insert_id($db_connection);
echo "<br/>YOU JUST INSERTED A RECORD WITH AUTO_INCREMENT ID = $id";

Select allOpen in new window

Thanks, Ray.  Late last night, I actually found a dreamweaver extension that does this type of thing and it worked!  If you're interested, I'll post the link.  I was able to take it out of table format and use <ul> <li> </li> </ul> tags and CSS instead.  I also tweaked the query to display the month and year differently.  

This is my new query:  
SELECT blogID, blogTitle, MONTH(blogDate) as blogMonth, YEAR(blogDate) as blogYear, CONCAT(MONTHNAME(blogDate), ', ', YEAR(blogDate)) AS FULLDATE FROM blogdata ORDER BY blogDate DESC

The output is now displayed as follows:

March, 2012
  · entry one
  · entry two

February, 2012
  · entry one
  · entry two
  · entry three

January, 2012
  · entry one
  · entry two

December, 2011
  · entry one
  · entry two
  · entry three

I've also made each article title a link so the article displays in another column when you click on the title.  

Thank you for your help!

I was a ColdFusion programmer before I started working with PHP, and CF is SO MUCH EASIER to work with where dates are concerned.  Also, outputting queries with different levels of grouping are SO MUCH EASIER as well.  Sigh...  

Thanks again for your help.  I will award points to you.

He pointed me in the right direction, but I ended up solving the problem myself.  However, he did help considerably.

... and CF is SO MUCH EASIER to work with where dates are concerned.

Maybe you have not given PHP enough opportunity to serve your needs.  Please see this article.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

If you're thinking of using Dreamweaver as a tool to help you learn PHP -- just don't do it.  Dreamweaver contains some of the worst PHP code ever written.  Instead, get this little book and give yourself some time to read it over and work through the examples.  It will provide some structured learning, and with a CF background you will find it to be a breeze.  Very readable with great examples, it has been a part of my professional library since Editon One.
http://www.sitepoint.com/books/phpmysql4/