Solved

PHP - group and sort recordset by Year/Month

Posted on 2012-03-23
9
402 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
  • 5
  • 4
9 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Easy Project Management (No User Manual Required)

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:ssailer
Comment Utility
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 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
He pointed me in the right direction, but I ended up solving the problem myself.  However, he did help considerably.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
... 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now