在查询结果中显示类别

Question

我已经使用INNER JOIN连接了多个表，并得到了类似如下的输出：

SPORT COUNTRY LEAGUE MATCH
SPORT COUNTRY LEAGUE MATCH
SPORT COUNTRY LEAGUE MATCH

我希望SPORT只在第一次在查询中找到时才显示。国家和联赛也是一样，所以他们是比赛的头球。如下所示：

SPORT
COUNTRY
LEAGUE1
MATCH
MATCH
MATCH
LEAGUE2
MATCH
MATCH
MATCH

代码：

if ($db_found) {

$SQL ="
select sportname, 
  tournament_templatename, 
  tournament_stagename,    
  GROUP_CONCAT(eventname) as event_name
from ('SELECT 
         event.id AS eventid,
         event.name AS eventname,
         event.tournament_stageFK AS eventtournamentstageFK,
         event.startdate AS eventstartdate,
         tournament_stage.id AS tournament_stageid,
         tournament_stage.name AS tournament_stagename,
         tournament_stage.tournamentFK AS tournament_stagetournamentFK,
         tournament.id AS tournamentid,
         tournament.name AS tournamentname,
         tournament.tournament_templateFK AS tournamenttournament_templateFK,
         tournament_template.id AS tournamenttemplateid,
         tournament_template.name AS tournament_templatename,
         tournament_template.sportFK AS tournament_templatesportFK,
         sport.id AS sportid,
         sport.name AS sportname
       FROM event 
         INNER JOIN tournament_stage 
           ON event.tournament_stageFK=tournament_stage.id
         INNER JOIN tournament 
           ON tournament_stage.tournamentFK=tournament.id 
         INNER JOIN tournament_template 
           ON tournament.tournament_templateFK=tournament_template.id 
         INNER JOIN sport 
           ON tournament_template.sportFK=sport.id
       WHERE
         DATE(event.startdate) = CURRENT_DATE() 
       ORDER BY
         sport.id ASC, tournament_template.name ASC, event.startdate ASC'
     )
GROUP BY 
  sportname, tournament_templatename, tournament_stagename
ORDER BY 
  sportname, tournament_templatename, tournament_stagename";
$result = mysql_query($SQL);

echo "<table border='1' style='border-collapse: collapse;border-color: white;
font-size:8px; background-color: #c8ccce; width:100%;'>";

while($db_field=mysql_fetch_assoc($result)){
echo  "<tr>" , "<td>" , $db_field['sportname'] , "</td> " , "<td>" ,
$db_field['tournament_templatename'] , "</td>" , "<td>" , 
$db_field['tournament_stagename'] , "</td>" , "<td>" , $db_field['eventstartdate'] ,
"</td>" , "<td>" , $db_field['eventname'] , "</td>" , "</tr>";
}
echo "</table>";
mysql_close($db_handle);
}

感谢您对PHP新手的帮助。

Answer 1

在您的查询中，您可以：

$SQL ="
select sportname, 
   tournament_templatename, 
   tournament_stagename,    
   GROUP_CONCAT(eventname) as eventname
from (
SELECT event.id AS eventid,
event.name AS eventname,
event.tournament_stageFK AS eventtournamentstageFK,
event.startdate AS eventstartdate,
tournament_stage.id AS tournament_stageid,
tournament_stage.name AS tournament_stagename,
tournament_stage.tournamentFK AS tournament_stagetournamentFK,
tournament.id AS tournamentid,
tournament.name AS tournamentname,
tournament.tournament_templateFK AS tournamenttournament_templateFK,
tournament_template.id AS tournamenttemplateid,
tournament_template.name AS tournament_templatename,
tournament_template.sportFK AS tournament_templatesportFK,
sport.id AS sportid,
sport.name AS sportname
FROM
event INNER JOIN tournament_stage ON event.tournament_stageFK=tournament_stage.id
INNER JOIN tournament ON tournament_stage.tournamentFK=tournament.id 
INNER JOIN tournament_template 
ON tournament.tournament_templateFK=tournament_template.id 
INNER JOIN sport ON tournament_template.sportFK=sport.id
WHERE
DATE(event.startdate) = CURRENT_DATE() 
) a
group by sportname, tournament_templatename, tournament_stagename
order by sportname, tournament_templatename, tournament_stagename";

然后获取结果，其中前3列已经是唯一的。

php代码保持不变：

$result = mysql_query($SQL);

while($db_field=mysql_fetch_assoc($result)){
echo $db_field['sportname'] , " " , $db_field['tournament_templatename'] , " " ,      
$db_field['tournament_stagename']  ,  " " , $db_field['eventname'];
echo "<br>";
}

Answer 2

SQL不支持该类型的分组。您必须解析所有数据，并执行您自己的标题/详细信息突破。

Answer 3

然后你应该计算结果。每次显示输出时只需计数，如果结果想要超过1次，只需返回null。