I have a pictures table where users pictures are saving with their ID and pic physical link.
userID | picture
1 | picnameLink
1 | picnameLink
2 | picnameLink
1 | picnameLink
2 | picnameLink
3 | picnameLink
Now, I want to show maximum 3 pictures in a jquery picture gallery block where one block should show all 3 pictures from one same user and if a user have less than 3 pictures, it should show no image text.
I have tried to do with group by mysql query but I am not getting desired result. Do I have to use two loops?
--Edit for fthiella-- Here is code
$query = "SELECT * FROM pictures GROUP BY userID";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)){
$image_array[] = $row['picLink'];
$id_array[] = $row['pic_id'];
}
$num_images_to_display = 3; /* MODIFY TO REFLECT NUMBER OF IMAGES TO SHOW PER SCREEN */
$num_images = count($image_array);
$image_path = "../images/"; /* MODIFY TO REFLECT THE PATH TO YOUR IMAGES */
$y = $num_images_to_display;
if(!isset($_GET['first'])){
$first = 0;
}else{
$first = (int) $_GET['first'];
}
$x = $num_images - 1;
$z = $x - $y;
if($first>$z) {
$first = $z;
}
$last = $first + $num_images_to_display;
And here is HTML area:
<div style="position:absolute; top:50px; left:100px; width:800px; text-align: center;">
<?PHP
$i = $first;
while($i<$last) { $showme = $image_path . $image_array[$i]; ?>
<?php if($image_array[$i]!="") { ?><img src="<?PHP echo $showme; ?>" width="176px" height="197px"><?php } else { ?><img src="../image/no_image.jpg" width="176px" height="197px"><?PHP } ?>
$prev = $first-1;
$next = $first +1;
if($prev<0){ $prev = 0; }
?>
</div>
Result of this query shows pictures in groups but I want maximum three pictures of each user where no image shows if a user has less than three images.
I don't know if there's a better solution, but i think you could use this:
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
This will select three images for each user. If a user has less than three images, it will show nulls, if it has more, it chooses three between all of them.
An alternative, that show three images each one in a different row, is this query that makes use of variables:
SELECT userid, picture
FROM (
SELECT
userid,
picture,
case when @prec_id=userid then @row:=@row+1 else @row:=1 end as row,
@prec_id:=userid
FROM
`pictures`,
(SELECT @prec_id:=0, @row:=0) s
ORDER BY userid) s
WHERE row<=3
EDIT: to show three images for each user at a time I would use my first query, and I would start with some code like this:
<?php
$mysqli = new mysqli("localhost", "username", "password", "test");
$image_path = "../images/";
$no_image = "../image/no_image.jpg";
if(!isset($_GET['first'])){
$first = 0;
} else {
$first = (int) $_GET['first'];
}
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>
(it has be improved, but you could start with it. I'm using mysqli instead of mysql)