这是关于一个Show three images from each user后,有一个很好的解决办法,我有相同的问题。

if ($stmt = $mysqli->prepare("SELECT p1.userID, p1.picture as pic1, p2.picture as pic2, p3.picture as pic3
FROM
  pictures p1 left join pictures p2
  on p1.userID=p2.userID and p1.picture<>p2.picture
  left join pictures p3
  on p1.userID=p3.userID and p1.picture<>p3.picture and p2.picture<>p3.picture
GROUP BY p1.userID
LIMIT ?,1")) {

    $stmt->bind_param("i", $first);
    $stmt->execute();
    $stmt->bind_result($user, $pic1, $pic2, $pic3);
    $stmt->fetch();
    $stmt->close();
}
$mysqli->close();
?>
<div style="position:absolute; top:50px; left:100px; width:800px; text-align: center;">
  <img src="<?PHP echo (isset($pic1) ? $image_path.$pic1 : $no_image); ?>" width="176px" height="197px">
  <img src="<?PHP echo (isset($pic2) ? $image_path.$pic2 : $no_image); ?>" width="176px" height="197px">
  <img src="<?PHP echo (isset($pic3) ? $image_path.$pic3 : $no_image); ?>" width="176px" height="197px">
</div>

我想知道如何在这个查询中也获得pictureID?如果我用图片来投票,那么我也必须得到图片ID。
上级帮助后回答:(如果有人找同样的人)
if ($stmt = $mysqli->prepare("SELECT p1.userID, p1.picture as pic1, p1.pictureID as pic1id, p2.picture as pic2, p2.pictureID as pic2id, p3.picture as pic3, p3.pictureID as pic3id
FROM
  pictures p1 left join pictures p2
  on p1.userID=p2.userID and p1.picture<>p2.picture
  left join pictures p3
  on p1.userID=p3.userID and p1.picture<>p3.picture and p2.picture<>p3.picture
GROUP BY p1.userID
LIMIT ?,1")) {

然后把它绑起来
$stmt->bind_result($user, $pic1, $pic1id, $pic2, $pic2id, $pic3, $pic3id);

最佳答案

假设图片的ID列命名为“pictureID”:

SELECT p1.userID, p1.picture as pic1, p1.pictureID as pic1id, p2.picture as pic2, p2.pictureID as pic2id, p3.picture as pic3, p3.pictureID as pic3id
FROM
  pictures p1 left join pictures p2
  on p1.userID=p2.userID and p1.picture<>p2.picture
  left join pictures p3
  on p1.userID=p3.userID and p1.picture<>p3.picture and p2.picture<>p3.picture
GROUP BY p1.userID
LIMIT ?,1

然后在绑定结果时:
$stmt->bind_result($user, $pic1, $pic1id, $pic2, $pic2id, $pic3, $pic3id);

10-08 13:33