php - 每个影片标题只能显示一次？

我目前在PHP中使用mySQL。我的任务是列出通过给定搜索产生的每部电影的所有演员。搜索和结果正常。但是，我需要更改结果的显示。

$sql = "SELECT f.title, a.first_name, a.last_name
    FROM film as f, actor as a, film_actor as fa
    WHERE (f.title LIKE \"%$filter%\")
    AND fa.actor_id = a.actor_id
    AND f.film_id = fa.film_id
    ORDER BY f.title;";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
echo "The following movies match the filter value: &quot$filter&quot." . "</strong>" .
     "</div>\n<br><br>\n";

// output data of each row
while(($row = $result->fetch_assoc())) {
    echo
    $row["title"] . " " . "stars" . " " .
    $row["first_name"] .  " " .
    $row["last_name"] .
    "</div>\n" .
    "<br>\n";
}
} else {
echo "0 results";
}

通过上面的代码，我得到了：

“以下电影与过滤器值匹配：“ choco”。

BUTTERFLY CHOCOLAT星星NICK STALLONE
蝴蝶巧克力星MENA TEMPLE
BUTTERFLY CHOCOLAT明星KIM ALLEN
BUTTERFLY CHOCOLAT明星LISA MONROE
BUTTERFLY CHOCOLAT饰演ED GUINESS
BUTTERFLY CHOCOLAT星星爆庙
BUTTERFLY CHOCOLAT明星玛丽·凯特尔
CHOCOLAT HARRY明星乔·斯旺克
CHOCOLAT HARRY饰演RIP CRAWFORD
CHOCOLAT HARRY饰演KIRK JOVOVICH
...
...“

当我应该得到这个时：

BUTTERFLY CHOCOLAT饰演ED GUINESS，MARY KEITEL，LISA MONROE，NICK STALLONE，BURT TEMPLE，MENA TEMPLE

巧克力HARRY明星RIP CRAWFORD，JANE JACKMAN，
...
...

我尝试了SELECT DISTINCT，但这只会使它更加复杂。

最佳答案

您将需要GROUP BY电影，然后使用Group_Concat功能

$sql = "SELECT f.title, group_concat(concat(a.first_name, " ", a.last_name)) ActorNames
FROM film as f, actor as a, film_actor as fa
WHERE (f.title LIKE \"%$filter%\")
AND fa.actor_id = a.actor_id
AND f.film_id = fa.film_id
GROUP BY f.title
ORDER BY f.title;";

并将循环修改为

while(($row = $result->fetch_assoc())) {
echo
$row["title"] . " " . "stars" . " " .
$row["ActorNames"] .  " " .
"</div>\n" .
"<br>\n";

关于php - 每个影片标题只能显示一次？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40003668/