我试图在单个表框中显示multiple rows in sql
的数据。格式为:
"MovieTitle" "movieReleaseYear" "directorName"
The Matrix 1999 Andy Wachowski, Lana Wachowski
其中Andy Wachowski and Lana Wachowski
来自不同的行,但与group_concat
聚集在一起
我在phpmyadmin中没有问题,但我不知道如何在php中显示它。
我有这个:
$sql="SELECT 725G54_5_movies.MovieTitle, 725G54_5_movies.movieProductionYear, GROUP_CONCAT( 725G54_5_director.directorName )
FROM 725G54_5_movies
JOIN 725G54_5_directed ON 725G54_5_movies.MovieID = 725G54_5_directed.movieID
JOIN 725G54_5_director ON 725G54_5_directed.directorID = 725G54_5_director.directorID
GROUP BY 725G54_5_movies.MovieTitle
ORDER BY $order ASC";
$result=mysql_query($sql);
//Presentation av kontakterna via while-sats till ett formulär
while($rows=mysql_fetch_array($result)){
echo "<tr>
<td>"; echo $rows['MovieTitle']; echo "</td>
<td>"; echo $rows['movieProductionYear']; echo "</td>
<td>";
while($director=mysql_fetch_array($rows['directorName'])){ echo $director; };
echo"</td>
</tr>";
Group Concat已经连接了董事的名称,因此您不需要迭代$rows['directorName']
。这部分在您的代码id错误,因为$rows
是来自查询结果的一行,$rows['director']
是来自该行的值,您不能迭代值。
$sql = "SELECT GROUP_CONCAT( 725G54_5_director.directorName ) AS directorNames ..."
while($rows=mysql_fetch_array($result)){
echo "<tr>";
echo "<td>" . $rows['MovieTitle'] . "</td>";
echo "<td>" . $rows['movieProductionYear'] . "</td>"
echo "<td>" . $rows['directorNames'] . "</td>"
echo"</tr>";
}
您使用GROUP BY 725G54_5_movies.MovieTitle
,所以directorNames
字段将是所有directorName
为这个电影连接的结果。
关键的信息是,您需要为您的GROUP_CONCAT设置别名,以便您可以在数组中访问它。为了提高可读性,我还会在输出html块时使用Heredoc:
$sql=
"SELECT
725G54_5_movies.MovieTitle,
725G54_5_movies.movieProductionYear,
GROUP_CONCAT(725G54_5_director.directorName) directorNames
FROM
725G54_5_movies
JOIN
725G54_5_directed
ON 725G54_5_movies.MovieID = 725G54_5_directed.movieID
JOIN
725G54_5_director
ON 725G54_5_directed.directorID = 725G54_5_director.directorID
GROUP BY
725G54_5_movies.MovieTitle
ORDER BY
$order ASC";
$result=mysql_query($sql);
//Presentation av kontakterna via while-sats till ett formulär
echo "<table>";
while($rows=mysql_fetch_array($result)){
echo <<<HTML
<tr>
<td>{$rows['MovieTitle']}</td>
<td>{$rows['movieProductionYear']}</td>
<td>{$rows['directorNames']}</td>
</tr>
HTML;
}
echo "</table>";