我正在PHP中运行以下代码,从数据库中生成电影的随机列表,但由于genere的值是一个数组,它只是捕获数组的第一个值。
如何修改查询,以便在逗号分隔的行中获得generae的所有值。
$q = "SELECT *
FROM
title
INNER JOIN title_genere ON (title.id = title_genere.id_title)
INNER JOIN genere ON (title_genere.id_genere = genere.id)
ORDER BY RAND()
LIMIT 8";
$result = mysql_query($q);
while ($row = mysql_fetch_array($result)) {
$id = $row['id'];
$title = $row['title'];
$year = $row['year'];
$poster = $row['poster'];
$poster = str_replace("./", "lib/", $poster);
$genere = $row['genere'];
echo "<div id='a'>";
echo "<div id='b'>".$title.'</div>';
echo "<div id='c'>".$year.'</div>';
echo "<div id='d'><a href='select.php?movieid=$id'><img src='$poster' alt='' border='1' align='center' width='214' height='314' /></a></div>";
echo "<div id='e'>".$genere.'</div>';
echo "</div>";
//var_dump($genere);
}
试试这个:
$q = "SELECT
*,
GROUP_CONCAT(CAST(title_genere AS BINARY) SEPARATOR ',') AS generes
FROM
title
INNER JOIN title_genere ON (title.id = title_genere.id_title)
INNER JOIN genere ON (title_genere.id_genere = genere.id)
ORDER BY RAND()
GROUP BY title.id
LIMIT 8";
这应该在密钥generes
中选择一个逗号分隔的generaes列表。有关更多信息,请参阅手册http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
不要使用GROUP_CONCAT/GROUP BY,这会导致开销。
首先,将您的8个随机标题(仅从titles
表中)选择到一个由标题id索引的关联数组中。让我们将其命名为$idx。
接下来,准备id列表,比如说,作为$ids变量。
然后,运行查询
SELECT
title_genere.id_title
, genre.genre
FROM
title_genre
INNER JOIN genre ON title_genere.id_genere = genere.id
WHERE
title_genere.id_title IN ($ids)
ORDER BY
genre
并在$idx
:中获取列表中的genre
值
$idx[$row['id_title']]['genres'][] = $row['genre']
最后,从$idx生成HTML。
已排序。我使用下面的代码来生成我想要的内容。感谢大家对的帮助
SELECT
title.id,
title.title,
GROUP_CONCAT(genere SEPARATOR ' | ') AS genere,
title.`year`,
title.poster
FROM
title
INNER JOIN title_genere ON (title.id = title_genere.id_title)
INNER JOIN genere ON (title_genere.id_genere = genere.id)
GROUP BY
title.id,
title.title,
title.`year`,
title.poster
ORDER BY
RAND()
LIMIT 8