PHP While loop: combine data based on date

Question

I have a table with these rows:

rowAutoID, eventDate, eventTime, personID

Screenshot of rows with a data sample:

and I would like to loop through the rows and group the items based on date and personID.

This is what I currently have:

$query = "SELECT rowAutoID,eventDate,eventTime,personID FROM `PubEvent` ";
$result = mysql_query($query);

if ( $result && mysql_num_rows($result)) {
    while( $obj = mysql_fetch_array($result, MYSQL_ASSOC) ) {
        echo '<tr><td>'.$obj["eventDate"].' '.$obj["eventTime"].'</td><td>'.$obj["personID"].'</td><td>status</td></tr>';
    }
}

where it gives me this:

+---------------------+------------+--------+
| 2015/10/05 05:27:24 | 0000000011 | status |
+---------------------+------------+--------+
| 2015/10/05 05:40:24 | 0000000020 | status |
+---------------------+------------+--------+
| 2015/10/05 06:01:23 | 0000000003 | status |
+---------------------+------------+--------+
| 2015/10/05 11:49:51 | 0000000011 | status |
+---------------------+------------+--------+

So if I group by date (NOT Time), I am looking for this outcome:

+-------------+------------+--------+----------+
| 2015/10/05  | 0000000011 | status | 05:27:24 |
|             |            |        | 11:49:51 |
+-------------+------------+--------+----------+
| 2015/10/05  | 0000000020 | status | 05:40:24 |
+-------------+------------+--------+----------+
| 2015/10/05  | 0000000003 | status | 06:01:23 |
+-------------+------------+--------+----------+

So that the personID 0000000011 group itself on the same date and on an extra column I get all the eventTime's of that Date/personID.

Note: I wouldn't want to change my SQL due to the way I will use eventDate,eventTime for status, user permissions and other purposes.

Answer 1

Using while loop. You can do this in this way. Try it..

$query = "SELECT rowAutoID,eventDate,eventTime,personID FROM `PubEvent` ";
$result = mysql_query($query);
$new_result = array();
if ( $result && mysql_num_rows($result)) {
    while( $obj = mysql_fetch_array($result, MYSQL_ASSOC) ) {        
        $date = date("Y/m/d", strtotime($obj["eventDate"]));
        $time = date("H:i:s", strtotime($obj["eventDate"]));
        $new_result[$obj["personID"]][$date]['date'] = $date;
        $new_result[$obj["personID"]][$date]['time'] = $time;
        $new_result[$obj["personID"]][$date]['id'] = $obj["personID"];
        $new_result[$obj["personID"]][$date]['status'] = "status";
    }
}
echo "<pre>"; print_r($new_result);

Hope it will help.

Answer 2

Using GROUP_CONCAT with Group By eventDate and personID will give you the desired output

SELECT
  eventDate,
  personID,
  GROUP_CONCAT(eventTime SEPARATOR '\n')
FROM pubevent
GROUP BY eventDate, personID;