CODEWITHSUNDEEP
phpmysql
john mossel
john mossel

Reputation: 2156

Grouping by date in php/mysql

I have a an array:

Array
(
    [0] => Array
        (
            [id] => 53
            [product] => something
            [price] =>  £78
            [quantity] => 23
            [time] => 2011-07-15 20:29:21
        )

    [1] => Array
        (
            [id] => 52
            [product] => product
            [price] => £89
            [quantity] => 1
            [time] => 2011-07-14 00:51:57
        )

    [2] => Array
        (
            [id] => 51
            [product] => product
            [price] => £89
            [quantity] => 1
            [time] => 2011-07-14 00:51:37
        ))

I got this using the following sql statement:

select * from some_table GROUP BY time DESC

I want to display the results ordered by date, with the date as a heading:

e.g. 

**2011-07-15**
all array elements for above date go here

**2011-07-15**
all array elements for above date go here

I thought about doing a seperate statement for each seperate day, but that sounds wrong. Is there a better way?

Upvotes: 0

Views: 155

Answers (2)

Paul
Paul

Reputation: 141829

ORDER BY Time in your query, like this:

select * from some_table ORDER BY time DESC

Then loop in PHP:

$last_date = 0;
while($row = mysql_fetch_assoc($result)){
    $date = date('Y-m-d', strtotime($row['time']));
    if($date != $last_date)
        echo '**'.$date.'**';
    $last_date = $date;
    echo '<br />';
    echo 'Id: '.$row['Id'];
    echo 'Product: '.$row['product'];
    echo 'Price: '.$row['price'];
    echo 'Quantity: '.$row['quantity'];
    echo '<br /><br />';
}

If you're ouputting to a console instead of an HTML page then replace the '<br \>' with "\n"

Upvotes: 2

Germ&#225;n Rodr&#237;guez
Germ&#225;n Rodr&#237;guez

Reputation: 4288

<?
$arrDate = Array
(
    [0] => Array
        (
            [id] => 53
            [product] => something
            [price] =>  £78
            [quantity] => 23
            [time] => 2011-07-15 20:29:21
        );


$currentDate = null;

foreach ($arrDate as $item) {

      if ($item["time"] == $currentDate) {
          // print details
      }
      else {
           $currentDate = $arrDate["time"];
           // print date header.
           // print details
      } 

}

?>

Upvotes: 0

Related Questions