Show three images from each user

Question

I have an image table in which user photos are saved with their identifier and a link to a physical link.

userID | picture 1 | picnameLink 1 | picnameLink 2 | picnameLink 1 | picnameLink 2 | picnameLink 3 | picnameLink 

Now I want to show a maximum of 3 pictures in the jquery picture gallery block, where one block should show all 3 pictures from the same user, and if the user has less than 3 images, he should not show the text of the image.

I am trying to make a mysql query with a group, but I am not getting the desired result. Should I use two loops?

- Edit for fthiella-- Here is the 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 the 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> 

The result of this query shows the images in groups, but I want to get a maximum of three images for each user, where the image is not displayed if the user has less than three images.

Answer 1

I don't know if there is 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 allow you to select three images for each user. If the user has less than three images, he will show zeros; if he is larger, he selects three of them.

An alternative showing three images on each line is this query, which uses 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 when I will use my first request, and I would start with some code as follows:

 <?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 been improved, but you can start with it. I use mysqli instead of mysql)