问显示连接的sqldata
EN

Stack Overflow用户

提问于 2013-05-24 09:41:47

回答 2查看 52关注 0票数 0

我正在尝试在单个表框中显示来自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>";

php

mysql

回答 2

Stack Overflow用户

发布于 2013-05-24 09:55:33

Group Concat已经连接了控制器的名称，因此您不需要在代码id中迭代错误的$rows['directorName'] .This部件，因为$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连接的结果。

票数 1

Stack Overflow用户

发布于 2013-05-24 10:05:58

关键的信息是，您需要为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>";