Solved

PHP - group and sort recordset by Year/Month

Posted on 2012-03-23
9
409 Views
Last Modified: 2012-03-25
I have an application that I have built which contains articles and dates for each article.  I need to be able to group by the Year/Month and then display the article titles below each Year/Month combination group.  This is my query:

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());
$row_getBlog = mysql_fetch_assoc($getBlog);
$totalRows_getBlog = mysql_num_rows($getBlog);

$mydate = $row_getBlog['blogDate'];
$month = date("F",strtotime($mydate));
$year = date("Y",strtotime($mydate));

I'd like the format to be something like this:

March, 2012
lorem ipsum
lorem ipsum
lorem ipsum

February, 2012
lorem ipsum
lorem ipsum
lorem ipsum

Thank you!
0
Comment
Question by:ssailer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 37760518
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...
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 37760534
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;
}

Open in new window

0
 

Author Comment

by:ssailer
ID: 37761896
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!!!
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 110

Expert Comment

by:Ray Paseur
ID: 37762437
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);

Open 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";

Open in new window

0
 

Author Comment

by:ssailer
ID: 37762782
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!
0
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 37762803
Glad to help - looks like it's doing what you need.  As usual when processing dates in PHP and MySQL there is more than one way to skin a cat :-)  

best regards, ~Ray
0
 

Author Comment

by:ssailer
ID: 37762817
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.
0
 

Author Closing Comment

by:ssailer
ID: 37762820
He pointed me in the right direction, but I ended up solving the problem myself.  However, he did help considerably.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 37762827
... 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.
http://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/
0

Featured Post

Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question