mysql组通过php concat [英] mysql group by php concat

问题描述

我在我的数据库中有一个fixtures表：hometeam，awayteam，date，time

I have a table of fixtures in my DB: hometeam, awayteam, date, time

我正在使用 mysql_fetch_array 并获得以下内容：

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

我想返回：

13/08/2011
---------------------------
Blackburn, Wolves, 3:00pm
Arsenal, Liverpool, 3:00pm
etc

我试过按日期分组，但这只是返回1个固定的日期，这是垃圾。

I have tried group by date, but this just returns 1 fixture for that date which is rubbish.

我也试过 group_concat ，但问题在于我需要修改此组中的项目，例如将时间格式从服务器时间更改为可读时​​间像上面那样。

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.

推荐答案

您可以通过da te，然后在两行之间的日期发生变化时检查php。像这样：

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']);
}

这篇关于mysql组通过php concat的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！