Problem to present data from SQL with multiple loops

Question

I've tried to make this work for 24 hours now, and now it feels like I'm close to it!

I would like to get date (column:datum) and location (column:plats) from MySQL, where my code looks like:

<?php

$sql = "SELECT datum FROM gigs GROUP BY DATE_FORMAT( datum, '%Y' ) ORDER BY datum DESC";
$result = mysql_query($sql);

 while($r = mysql_fetch_array($result)) {

  $date = $r['datum'];
  $date_new = new DateTime($date);
  $year = $date_new->format('Y');
  $month = $date_new->format('M');
  $day = $date_new->format('d');

  $sql2 = "SELECT * FROM gigs WHERE DATE_FORMAT( datum, '%Y' ) = $year ORDER BY datum ASC";
  $result2 = mysql_query($sql2);

  echo "<tr height=20px><td></td><td align=center><b>".$year."</b></td></tr>";

  echo "<tr><td><b>".$month."</b></td></tr>";

  while($r2 = mysql_fetch_array($result2)) {

   $date2 = $r2['datum'];
   $date_new2 = new DateTime($date2);
   $year2 = $date_new2->format('Y');
   $month2 = $date_new2->format('M');
   $day2 = $date_new2->format('j');

 //echo "<b>Month: ".$month."</b>";
 //echo "<b>Month2: ".$month2."</b>";

    if($month != $month2) {
     echo "<tr><td>&nbsp;</td></tr><tr><td><b>".$month2."</b></td></tr>";
    }

    $month = $month2;

    echo "<tr class=giglist><td>".$day2."</td><td>".$r2['plats']."</td></tr>"; 



  }
    echo "</td></tr>";
 }

?>

This gives me:

2011
Jan
1. Location
2. Location
3. Location
...

Feb
1. Location
2. Location
3. Location
...
...
Dec
1. Location
2. Location
3. Location
...

2010
Dec

Jan
1. Location
2. Location
3. Location
...
Feb
1. Location
2. Location
3. Location
...
...
Dec
1. Location
2. Location
3. Location
...

I do NOT want the extra December written under "2010"...

I hope someone can help, cause I'm lost in my own code xD thanks!

Xavier Barbosa · Accepted Answer · 2011-01-21 12:46:14Z

1

Your code is to much complicated, you can do that with only one query, see this code

<?php 
$sql = 'SELECT
            *, 
            DATE_FORMAT( datum, "%Y" ) "year", 
            DATE_FORMAT( datum, "%M" ) "month", 
            DATE_FORMAT( datum, "%e" ) "day" 
        FROM gigs
        ORDER BY year DESC, month DESC, day ASC';

$year = null;
$month = null;
$stmt = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
    if ($year != $result['year']) {
        // Year changed
        $year = $result['year'];
        echo "<tr height=20px><td></td><td align=center><b>".$year."</b></td></tr>";
    }
    if ($month != $result['month']) {
        // Month changed
        $month = $result['month'];
        echo "<tr><td>&nbsp;</td></tr><tr><td><b>".$month."</b></td></tr>";
    }
    echo "<tr class=giglist><td>".$result['day']."</td><td>".$row['plats']."</td></tr>"; 
}

edited Jan 21, 2011 at 12:46

answered Jan 21, 2011 at 12:40

Xavier Barbosa

3,9471 gold badge22 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kastermester Over a year ago

This looks much better than the original code - but if I understood the intent of the original code correct you will be printing out in the wrong order. You should order by Year descending and Month (and day) ascending after that.

Robin Over a year ago

Wow, much better code! I didn't know it was possible to split the date like that and "order by" more than 1 'column' in the SQL-code... Now i get it... I haven't tested the code yet but I guess $stmt should be $result, and the $result-variables in loop should be $row right? Thanks!

Dirk · Accepted Answer · 2011-01-21 12:59:49Z

First of all, please please, I really recommend you to use the separation of concerns principle! Use a templating system, e.g. Smarty, http://www.smarty.net/ and use a database abstraction layer. Then your code will be MUCH more flexible, easier to read, etc. and then you wouldn't need to ask this question, and save lots and lots of time.

This said, you could try this (I didn't test it though..);

<?php

$sql = "SELECT datum FROM gigs GROUP BY DATE_FORMAT( datum, '%Y' ) ORDER BY datum DESC";
$result = mysql_query($sql);

while($r = mysql_fetch_array($result))
{
    $date = $r['datum'];
    $date_new = new DateTime($date);
    $year = $date_new->format('Y');
    $month = $date_new->format('M');
    $day = $date_new->format('d');

    $sql2 = "SELECT * FROM gigs WHERE DATE_FORMAT( datum, '%Y' ) = $year ORDER BY datum ASC";
    $result2 = mysql_query($sql2);

    echo "<tr height=20px><td></td><td align=center><b>".$year."</b></td></tr>";
    $first_month_printed = false;

    while($r2 = mysql_fetch_array($result2))
    {
        $date2 = $r2['datum'];
        $date_new2 = new DateTime($date2);
        $year2 = $date_new2->format('Y');
        $month2 = $date_new2->format('M');
        $day2 = $date_new2->format('j');

        if($month != $month2) {
            echo "<tr><td>&nbsp;</td></tr><tr><td><b>".$month2."</b></td></tr>";
        } else if (!$first_month_printed)
            echo "<tr><td>&nbsp;</td></tr><tr><td><b>".$month2."</b></td></tr>";
            $first_month_printed = true;
        }

        $month = $month2;
        echo "<tr class=giglist><td>".$day2."</td><td>".$r2['plats']."</td></tr>"; 
    }

    echo "</td></tr>";
 }

?>

Thank you! I think I will stick to Xavier's code because it's more effective (less char's)!

Collectives™ on Stack Overflow

Problem to present data from SQL with multiple loops

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related