mysqli fetch records

Question

This is regarding a post Show three images from each user where there is a great solution for a same issues which I was having.

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>

I was wondering how can I also get pictureID as well in this query? E.g if I use pictures for voting, I would then have to get pictureID as well.

Answer after Supericy help:(if someone looking for same)

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")) {

And then bind it

$stmt->bind_result($user, $pic1, $pic1id, $pic2, $pic2id, $pic3, $pic3id);

score 1 · Answer 1 · answered Dec 25 '12 at 00:30

Try this,

SELECT p1.userID,coalesce(p1.pictureID,p2.pictureID,p3.pictureID) pictureID, 
       coalesce(p1.picture,p2.picture,p3.picture) as pic
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,pictureID,pic
LIMIT ?,1

Supericy · Accepted Answer · 2012-12-25T01:17:04.260

0

Assuming the picture's ID column is named "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

and then when binding the result:

$stmt->bind_result($user, $pic1, $pic1id, $pic2, $pic2id, $pic3, $pic3id);

edited Dec 25 '12 at 01:17

answered Dec 25 '12 at 00:34

Supericy

5,866
1
21
25

Sounds good, but do I then have to bind its variable as well in bind_result ? – Dec 25 '12 at 00:41
Oh right, yes. `$stmt->bind_result($user, $pic1, $pic1_id, $pic2, $pic2_id, $pic3, $pic3_id);` (edited my answer) – Supericy Dec 25 '12 at 00:42
I tried but it gives "Warning: mysqli_stmt::bind_result() [mysqli-stmt.bind-result]: Number of bind variables doesn't match number of fields in prepared statement" – Dec 25 '12 at 00:52
I just have one more question please, what if i want to add a check like "selecting only images where approved=1", where would I write it in query? – Dec 25 '12 at 01:22

mysqli fetch records

2 Answers2