Grouping by date in php/mysql

Question

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?

Paul · Accepted Answer · 2011-07-16 02:11:46Z

2

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"

edited Jul 16, 2011 at 2:11

answered Jul 16, 2011 at 1:52

Paul

142k28 gold badges285 silver badges272 bronze badges

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

4 Comments

Paul Over a year ago

GROUP BY and ORDER BY are two different things. GROUP BY will only have an affect if more than one tuple in your table has the same time and you are selecting aggregate data like SUM(quantity) or something

bfavaretto Over a year ago

Also, store the last date('Y-m-d') in a var and display the date header only if the date changes.

Paul Over a year ago

Oh, didn't notice he wanted each date to only be outputted once. I'll update my post. Thanks

john mossel Over a year ago

I already have the result, so I don't need to do while($row = mysql_fetch_assoc($result)) (I've build a wrapper function for this), I'm using a foreach($result_array as $item) statement.

Germán Rodríguez · Accepted Answer · 2011-07-16 01:53:37Z

0

<?
$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
      } 

}

?>

answered Jul 16, 2011 at 1:53

Germán Rodríguez

4,2881 gold badge21 silver badges19 bronze badges

Collectives™ on Stack Overflow

Grouping by date in php/mysql

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related