mysql group by php concat
I have a table of fixtures in my DB: hometeam, awayteam, date, time
I am currently using mysql_fetch_array
and getting the following:
hometeam awayteam date time
--------------------------------------------
Blackburn Wolves 13/08/2011 3:00pm
Arsenal Liverpool 13/08/2011 3:00pm
etc
I want to return:
13/08/2011
---------------------------
Blackburn, Wo开发者_如何学运维lves, 3:00pm
Arsenal, Liverpool, 3:00pm
etc
I have tried group by date, but this just returns 1 fixture for that date which is rubbish.
I have also tried group_concat
, but the problem with this is I need to modify the items in this group, such as changing the time format from server time to a readable time like above.
you can sort by date and then check in php when the date changes between two rows. something like this:
function h($s) { return htmlspecialchars($s); }
$conn = mysqli_connect(…);
$result = mysqli_query($conn, 'select * from fixtures order by date');
$lastdate = NULL;
while(($row = mysqli_fetch_assoc($result)) !== FALSE) {
if($lastdate != $row['date']) {
// output date divider/header
echo '<h1>',h($row['date']),'</h1>';
$lastdate = $row['date'];
}
echo h($row['hometeam']), ', ', h($row['awayteam']), ', ', h($row['time']);
}
精彩评论