php - 在查询结果中显示类别

Question

我使用 INNER JOIN 连接了多个表，并得到了类似于以下内容的输出:

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

我希望仅在第一次在查询中找到 SPORT 时才显示它。与 COUNTRY 和 LEAGUE 相同，因此它们充当比赛的标题。像这样:

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>";
}