我试图让我的查询一次返回数据库中的每个记录一次,问题是如果清单中有多个图像存储在一个单独的表中,它将返回多个记录(每个图像一个)
这是我的数据库布局的一部分(**中的数据是主键)
tbl_listings => **listingID**,title,description,dateListed
tbl_images => **imageID**,filename
tbl_listing_image => **listingID**,**imageID**
这是我的代码
$stmt = $db->stmt_init();
if($stmt->prepare("SELECT l.listingID, l.title, l.description, l.dateListed, c.category, tn.townID, tn.town, i.filename
FROM tbl_listings AS l
LEFT JOIN tbl_listing_category AS lc ON lc.listingID = l.listingID
LEFT JOIN tbl_category AS c ON c.categoryID = lc.categoryID
LEFT JOIN tbl_listing_type AS lt ON lt.listingID = l.listingID
LEFT JOIN tbl_type AS t ON t.typeID = lt.typeID
LEFT JOIN tbl_listing_town AS ltn ON ltn.listingID = l.listingID
LEFT JOIN tbl_towns AS tn ON tn.townID = ltn.townID
LEFT JOIN tbl_listing_image AS li ON li.listingID = l.listingID
LEFT JOIN tbl_images AS i ON i.imageID = li.imageID
WHERE t.typeID =?"))
{
$type = 1;
$stmt->bind_param("i",$type);
$stmt->execute();
$stmt->bind_result($id,$title,$desc,$date,$cat,$townID,$town,$image);
echo "<ul>";
while($stmt->fetch())
{
$img = "images/listing/$id/$image";
echo "<li><img src='$img' alt='$title' title='$title'>#".$id."<h4>" . $title . "</h4>" . $desc . "<br /><strong> Category:</strong> " . $cat . " - <strong>Location: </strong> - <strong> Posted On:</strong> " . date("i M Y",$date) . "</li><hr>";
}
echo "</ul>";
$stmt->close();
它返回以下结果(请记住,tbl_listings表中只有1条记录)
listingID title description dateListed category townID town filename
1 listing 1 listing desc 1411240751 teaching 11 town a image1.jpg
1 listing 1 listing desc 1411240751 teaching 11 town a image2.jpg
1 listing 1 listing desc 1411240751 teaching 11 town a image3.jpg
因此,即使它的清单相同,查询也会返回3条记录(每个文件名一个)
所以我尝试将GROUP BY l.listingID添加到我的查询中,该查询仅返回一个记录,但也仅返回第一个图像,如下所示
listingID title description dateListed category townID town filename
1 listing 1 listing desc 1411240751 teaching 11 town a image1.jpg
所以我的问题是对于每个列表,我如何仅返回1条记录,但返回其所有关联图像?我需要在while循环中运行单独的查询来获取图像吗?
希望有帮助
干杯
最佳答案
您可以使用GROUP_CONCAT
将文件名作为单个字段列出:
SELECT l.listingID, l.title, l.description, l.dateListed, c.category, tn.townID, tn.town,
GROUP_CONCAT(i.filename) as filenames
FROM tbl_listings AS l
LEFT JOIN tbl_listing_category AS lc ON lc.listingID = l.listingID
LEFT JOIN tbl_category AS c ON c.categoryID = lc.categoryID
LEFT JOIN tbl_listing_type AS lt ON lt.listingID = l.listingID
LEFT JOIN tbl_type AS t ON t.typeID = lt.typeID
LEFT JOIN tbl_listing_town AS ltn ON ltn.listingID = l.listingID
LEFT JOIN tbl_towns AS tn ON tn.townID = ltn.townID
LEFT JOIN tbl_listing_image AS li ON li.listingID = l.listingID
LEFT JOIN tbl_images AS i ON i.imageID = li.imageID
WHERE t.typeID =?
GROUP BY l.listingID
优点是您只需一个往返时间即可查询一个查询,因此效率很高。当然,您确实需要再次拆分这些文件名,但这在PHP中是微不足道的。
另一方面,可以返回的最大长度为
GROUP_CONCAT
,如果类别可以包含许多文件,则可能会达到该限制。另外,如果任何其他表也导致重复,则可能会多次获得每个文件名。可以使用
GROUP_CONCAT(DISTINCT filename)
解决此问题,因此您仍然很安全,但是如果您不仅想要文件名,还想要文件的其他属性(类型,所有者?),就会遇到麻烦。在这种情况下,对多次出现的文件名或其他详细信息运行单独的查询不会感到羞耻。关于php - PHP的SQL查询返回多行每个单独的记录,滥用联接?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/26027620/