[SOLVED] Wrong display on foreach

[timmy0320]

I'm displaying tree like data for my archives and when I get down to the days it is only showing one post for that day instead of displaying all of them. I tried changing the foreach to a while $i< count($melement) with a counter and it did the same thing.

 

<?php
$results = mysql_query("SELECT DISTINCT YEAR(date) AS `year`, DATE_FORMAT(date, '%m') AS `month`, DATE_FORMAT(date, '%d') AS `day`, count(id) as `posts`, post_link as `link`, post_title as `title` FROM blog_posts GROUP BY YEAR(date), DATE_FORMAT(date, '%d') ORDER BY date DESC", $connection);

$posts = array();
while ($row = mysql_fetch_assoc($results)) {
    if (!isset($posts[$row['year']][$row['month']])) {
       $posts[$row['year']][$row['month']] = array();
    }
    $posts[$row['year']][$row['month']][] = $row;
}

// display years
foreach ($posts as $year => $yelement) {
    echo '<a href=archives.php?yr='.$year.'>'.$year .'</a> ['. count($yelement) .']<br />';
         // display months
        foreach ($yelement as $month => $melement) {
           if (isset($_GET['yr']) && $_GET['yr'] == $year) {
            echo '  <a href=archives.php?yr='.$year.'&mon='.$month.'>'. $month .'</a> ['. count($melement) .']<br />';
           }
        // display days
        foreach ($melement as $row) {
           if (isset($_GET['mon']) && isset($_GET['mon']) && $_GET['mon'] == $month && $_GET['yr'] == $year) {
            echo '    <a href=archives.php?yr='.$year.'&mon='.$month.'&title='.$row['link'].'>'. $row['day'].'</a> - '.$row['title'].'<br />';
           }
        }
        }
}
?>

 

I have 3 entries for today and it's only displaying the top one queried. It results as

2008 [1]
  04 [1]
    06 - Test post

 

 

When it should be displaying

2008 [3]
  04 [3]
    06 - Test post
    06 - Test post 2
    06 - Test post 3

 

 

I'm also having another issue with the year  and month displaying the wrong count because of this. Any help would be appreciated.

[Barand]

If you GROUP BY day then you only get one row per day. It's what GROUP BY does.

[timmy0320]

If you GROUP BY day then you only get one row per day. It's what GROUP BY does.

 

Alright, I removed GROUP BY, didn't work. So I GROUP BY year, still does the same thing. Should I be using a completely different SQL statement then?

[Barand]

You could leave the GROUP BY in and use GROUP_CONCAT for the post items.

[timmy0320]

Wouldn't I have to edit my code then? I've never used GROUP_CONCAT before but from the MySQL Reference it looks like it would require me to change up my code..

[Barand]

Well, let's wait and see if anyone comes up with a way of getting your script to work without changing anything.

[timmy0320]

Guess not. What's new.

[timmy0320]

Got it! It was all in the Query like I thought it would be. Thanks Barand for the idea on where to start, it seems as I can *NEVER* get help on here anymore. 

 

I played with the GROUP BY and it started to work but the counts were off. So I decided to change count(id) to count(*) and bam. All the dates posted.

 

<?php
$results = mysql_query("SELECT DISTINCT YEAR(date) AS `year`, DATE_FORMAT(date, '%m') AS `month`, DATE_FORMAT(date, '%d') AS `day`, count(*) as `posts`, post_link as `link`, post_title as `title`
FROM blog_posts $where GROUP BY date ORDER BY date DESC", $connection);
?>