Grouping query results

I am SOOOOO close to getting this working! Argh!

I’ve created a section on my blog that has links to previous posts. I wanted to arrange them like so:

  • 2010
    [LIST]
  • post title
  • post title
  • post title
    [/LIST]
  • 2009
    [LIST]
  • post title
  • post title
  • post title
    [/LIST]

I’ve managed to get that with the following code:


<?php
	$db = mysql_pconnect("", "foo", "bar");
	mysql_select_db("mydb");
	
		$query = mysql_query("
		SELECT  blog_id
			, category_id
			, entry_author_id
			, entry_id
			, entry_name
			, entry_date
			, FROM_UNIXTIME(entry_date, '&#37;Y') as postyear
			, entry_category
		FROM blog_entries
		WHERE entry_author_id = 1
		AND entry_status = 'published'
		ORDER BY entry_date DESC
		LIMIT 5, 15
		");
	
		while($row = mysql_fetch_object($query)) {
			$set[$row->postyear][] = $row->entry_name;
			$ids[$row->postyear][] = $row->entry_id;

			}
		foreach ($set as $postyear => $rows) { 
			echo "<h4>{$postyear}</h4>\
";
			echo "<ul>";

		foreach ($rows as $key => $entry_name) {
			echo "\
\	<li><a href=\\"index.php?entry={$ids[$entry_id][$key]}\\" title=\\"Posted: {$entry_date}\\">{$entry_name} ></a></li>";
			}
			echo "\
</ul>\
"; 
		}
		?>

It shows up exactly the way I want, but there are two problems.

  1. The entry ID in the href is blank
  2. I’ve tried to put the exact post date in the title=“” attribute, and that’s also blank.

I can’t for the life of me figure out how to get those two to show up! Probably because I usually code in ColdFusion, and this type of thing would be built into a tag or two.

Any pointers in the right direction would be appreciated.

I find this very interesting! I’m in the middle of a site design and was thinking of doing this exact same thing for blog postings. I’m curious how are you outputting the results to the html page?

Sorry for such a newbie question but I am new to php.

Figured it out! (and no, I did not stay up all night working on it) :stuck_out_tongue: But I think I am finally starting to “get” this array thing in PHP.

I was right about needing a third row in the spot I indicated in my last post. I ended up with this:

$dates[$row->postyear][] = $row->postdate;

Being a CF person, I’m used to using the cfdump tag for debugging. I wondered if someone had come up with a snippet that does something similar, so I Googled and found this: http://dbug.ospinto.com/

It’s the coolest thing EVER and allowed me to figure out exactly where I was going wrong. Anyway…

Final (working) code:


$query = mysql_query("
		SELECT  blog_id
			, category_id
			, entry_author_id
			, entry_id
			, entry_name
			, DATE_FORMAT(FROM_UNIXTIME(entry_date),'%W, %M %e, %Y') as postdate
			, FROM_UNIXTIME(entry_date, '%Y') as postyear
		FROM blog_entries
		WHERE entry_author_id = 1
		AND entry_status = 'published'
		ORDER BY entry_date DESC
		LIMIT 5, 15
		");

		while($row = mysql_fetch_object($query)) {
			$set[$row->postyear][] = $row->entry_name;
			$ids[$row->postyear][] = $row->entry_id;
			$dates[$row->postyear][] = $row->postdate;
			}
		foreach ($set as $postyear => $rows) { 
			echo "<h4>{$postyear}</h4>\
";
			echo "<ul>";
		foreach ($rows as $key => $entry_name) {
			echo "\
\	<li><a href=\\"index.php?entry={$ids[$postyear][$key]}\\" title=\\"Posted: {$dates[$postyear][$key]}\\">{$entry_name} ></a></li>";
			}
			echo "\
</ul>\
"; 
		}

:cool:

Aha! Got the post ID.

This:

{$ids[$entry_id][$key]}

Needed to be this:

{$ids[$postyear][$key]}

Now I just need to figure out how to get the entry date. I’m thinking I either need something here:


		while($row = mysql_fetch_object($query)) {
			$set[$row->postyear][] = $row->entry_name;
			$ids[$row->postyear][] = $row->entry_id;
something here --> 
			}

or to somehow modify this:

foreach ($rows as $key => $entry_name) {

We were all new once. Some of us are still sort of new (me).

The results of the above produce HTML by virtue of the “echo” statements. I put the code above where I want the HTML to appear, and voila!

Sorry for the delayed reply! I didn’t realize anyone had replied to this.

What an awesome thread, OP solver it themselves and in turn, solved another member’s problem. SitePoint rocks. :slight_smile:

Hey,

Thanks for getting back. When I get to that part of my site re-design, I will attempt to implement something similar.

In the middle of trying this now but am getting nowhere. I see that I have to construct my sql query differently because my blogs are broken down into categories so I have to pull information from the categories table in order to try to construct the links as so:


//do a left join to grab only those columns from categories table and articles table to construct the links.
$query = mysql_query("SELECT articles.aid
						, articles.catid
						, articles.title
						, articles.reported_date
						, categories.catdir
						, categories.details
						, categories.catid
						, DATE_FORMAT(FROM_UNIXTIME(reported_date),'%W, %M %e, %Y') as postdate
						, FROM_UNIXTIME(reported_date, '%Y') as postyear
					FROM categories 
					LEFT JOIN articles 
					ON articles.catid 
					WHERE articles.catid = categories.catid 
					ORDER BY reported_date DESC 
					LIMIT 5, 15") or die(mysql_error());

while($row = mysql_fetch_assoc($query)){
		$id = $row['aid'];//id of article and corresponds to primary key in articles table
		$cDir = $row['catdir'];//directory designation in categories table
		$cPage = $row['details'];//details page in categories table
		$title= $row['title'];//title of article in articles table
    # Tracking what happens is often good for debugging purposes
    # And this part will tell you everything is fine - you do get your comments...
    echo '<h3><a href="'.$cDir.'/'.$cPage.'?aid='.$id.'">'.$title.'</a></h3>';
	}


Up to this point, everything works fine, the while loop lists everything accordingly.

Implementing this code gives me strange results again, my array is a bit different


   while($row = mysql_fetch_object($query)) {
            $set[$row->postyear][] = $row->title;
            $ids[$row->postyear][] = $row->aid;		$cDir[$row->postyear][] = $row->catdir;
			$cPage[$row->postyear][] = $row->details; 
            $dates[$row->postyear][] = $row->postdate;
            }
        foreach ($set as $postyear => $rows) {
            echo "<h4>{$postyear}</h4>\
";
            echo "<ul>";
        foreach ($rows as $key => $entry_name) {
            echo "\
\	<li><a href=\\"$cDir\\$cPage?aid={$ids[$postyear][$key]}\\" title=\\"Posted: {$dates[$postyear][$key]}\\">{$entry_name} ></a></li>";
            }
            echo "\
</ul>\
";
        }


My output is as so:

1970

Title 1
Title 2
Title 3
Title 4
(all the way to Title 15)

Nothing is broken down by year and I have no posts in 1970 :slight_smile:

Any ideas?

Thanks

Got it working! I had to take out the FROM_UNIXTIME and just went with a straight DATE_FORMAT function in the SQL. I assumed “FROM_UNIXTIME” may have something to do with the time on your server which is probably a UNIX server.

After that the years worked. Then it was short order to solve my links issues. Anyway, here’s my final result.


//do a left join to grab only those columns from categories table and articles table to construct the links.
$query = mysql_query("SELECT articles.aid
						, articles.catid
						, articles.title
						, articles.reported_date
						, categories.details
						, categories.catdir
						, categories.catid
						, DATE_FORMAT (reported_date,'%W, %M %e, %Y') as postdate
						, DATE_FORMAT (reported_date, '%Y') as postyear
					FROM articles 
					LEFT JOIN categories 
					ON categories.catid 
					WHERE articles.catid = categories.catid
					ORDER BY reported_date DESC 
					LIMIT 5, 30") or die(mysql_error());

    while($row = mysql_fetch_object($query)) {
            $set[$row->postyear][] = $row->title;
            $ids[$row->postyear][] = $row->aid;
			$cDir[$row->postyear][] = $row->catdir;
			$cPage[$row->postyear][] = $row->details; 
            $dates[$row->postyear][] = $row->postdate;
            }
        foreach ($set as $postyear => $rows) {
            echo "<h4>{$postyear}</h4>\
";
            echo "<ul>";
        foreach ($rows as $key => $entry_name) {
            echo "\
\	<li><a href=\\"{$cDir[$postyear][$key]}/{$cPage[$postyear][$key]}?aid={$ids[$postyear][$key]}\\" title=\\"Posted: {$dates[$postyear][$key]}\\">{$entry_name} ></a></li>";
            }
            echo "\
</ul>\
";
        }


Couldn’t have done it without your starting point!

Thanks!

Nice work!!

Yep, I needed the unixtime deal because 1) my webserver runs Linux, and 2) my blog spits out a unix date format that looks something like 1142522846.

Glad you got it working!