Grouping Images in a Profile

Question

I have a profile that shows the profiles in the list. as shown in the picture below.

users table

id | email | full_name | job_title | bio | profile_photo 

image table

  image_id | id | artist_img 

THE CODE

 <?php $db = dbconnect(); $stmt = $db->prepare('SELECT users.email, users.full_name, users.job_title, users.bio, users.profile_photo, images.id, images.artist_img FROM users INNER JOIN images ON users.id=images.id GROUP BY images.id'); $stmt->execute(); $result = $stmt->get_result(); while (($row = mysqli_fetch_assoc($result)) != false) { $id = $row['id']; $full_name = $row['full_name']; $email = $row['email']; $job_title = $row['job_title']; $bio = $row['bio']; $ProfilePhoto = $row['profile_photo']; $artist_img = $row['artist_img']; if (isset($ProfilePhoto) && ! empty($ProfilePhoto)) { $image = "$ProfilePhoto"; } else { $image = "avatar.jpg"; } echo "<div class='container team-wrap'> <div class='row'> <div class='col-md-6'> <img class='img-responsive' src='artist/$image'> </div> <div class=\"col-md-6\"> <strong>$full_name<br>$job_title</strong> <br> <p>$bio</p> <a href='mailto:$email' class='btn btn-info'>Contact Me</a> </div> </div> </div> <div class=\"container space team-wrap\"> <div class=\"row\"> <div class=\"col-lg-12\"> <div id=\"gallery-slider\" class=\"slider responsive\"> <div>"; echo" <img src=\"gallery/$artist_img\" alt=\"\"></a>"; echo "</div> </div> <hr> </div> </div> </div>"; } ?> 

Problem area

 echo"<img src=\"gallery/$artist_img\" alt=\"\"></a>"; 

The problem is that it repeats the profile for each image, if the user has 5 images, he will add 5 profiles 1 for each img.

and the profile of other users is not displayed at all. show how it is displayed, look at the image, for example, it received 4 images under profile 1, and it shows the pic profile there .. well, it transmits all this information for each image. I want photos that have the same identifier as the user to show as a slider, as shown below.

and also refuses to show other profiles of other users.

Answer 1

You need to use the array example below . the way you call it now is as if it were just a variable, so its only pull is 1.

 $artist_img1=array(); $artist_img1 = $row['images']; $artist_img2=row2['id']; echo "<img src=\"gallery/".$artist_img2."\" alt=\"\"></a>"; 

Answer 2

This is because you are grouping the data wrong.

As you say, you have several lines for the user ID and you are trying to display these several results on the same line using GROUP BY . Thus, a database cannot group such data because it does not know any connection between the data and what is associated with several results.

However, you can use the JOIN here, as that is what you are trying to do. Use the following query

 SELECT * FROM `users` LEFT JOIN `images` ON images.id = users.id 

Then you should process the result as follows:

 $users = []; foreach($results as $row){ if(isset($users[$row->userId])) $users[$row->userId][] = $row->imageUrl; else $users[$row->userId] = [$row->imageUrl]; } 

$users is what you want. He has to do the work.

Answer 3

Try it.

 SELECT t.id,t.email,t.fullname,t2.image_id,t2.picture_name FROM table2 t LEFT JOIN table1 ON t2.id=t.id 

Answer 4

Your entry in the database should be like at the bottom of the table.

Table 1 - Users

 ------------------------------------------ id | email | full_name ------------------------------------------ 1 | [e-mail] | test1 2 | [e-mail] | test2 3 | [e-mail] | test3 

Table2 - Images

 ------------------------------------------ image id | id | picture_image ------------------------------------------ 1 | 1 | image1.jpg 2 | 1 | image2.jpg 3 | 1 | image3.jpg 4 | 1 | image4.jpg 5 | 1 | image5.jpg 6 | 1 | image6.jpg 7 | 1 | image7.jpg 8 | 1 | image1.jpg 9 | 1 | image2.jpg 10 | 1 | image3.jpg 11 | 1 | image4.jpg 12 | 1 | image5.jpg 

Use below SQL Query to get images using UserID

 SELECT * FROM users INNER JOIN images ON (users.id = images.id) WHERE (images.id = '1') 

Above the query will give the result of profile ID 1.

Answer 5

For the request, you can use:

 SELECT * FROM `users` LEFT JOIN `images` ON images.id = users.id 

And you must repeat all the user images one by one in order to distinguish the profile image (1st image) from others.

 $result = $stmt->get_result(); $users = array(); foreach($results as $row){ if (in_array($row['id'], $users)) { echo "Print profil picture"; } else { array_push($users, $row['id']); echo "Print the other pictures"; } } 

Answer 6

Change the two lines, you will get the result as expected

prepare ('SELECT users.email, users.full_name, users.job_title, users.bio, users.profile_photo, images.id, GROUP_CONCAT (images.artist_img SEPARATOR' | ') as artist_img FROM users INNER JOIN images ON users.id = images.id GROUP BY images.id '); $ Stmt-> Execute (); $ result = $ stmt-> get_result (); while (($ row = mysqli_fetch_assoc ($ result))! = false) {$ id = $ row ['id']; $ full_name = $ row ['full_name']; $ email = $ row ['email']; $ job_title = $ row ['job_title']; $ bio = $ row ['bio']; $ ProfilePhoto = $ row ['profile_photo']; // Split as an array $ artist_imgs = explode ("|", $ row ['artist_img']); if (isset ($ ProfilePhoto) &&! empty ($ ProfilePhoto)) {$ image = "$ ProfilePhoto"; } else {$ image = "avatar.jpg"; } echo " $ full_name
$ JOB_TITLE

$ bio

Contact me "; foreach ($ artist_imgs as $ artist_img) {echo" ";} echo"

---

";}?>

Answer 7

Try this as an SQL statement -

 SELECT * FROM users INNER JOIN images on users.id = images.id INNER JOIN (SELECT MAX(image_id) as maxid, id from images GROUP BY id) as g ON images.image_id = g.maxid 

This will give you the desired results. Please note that the last image is required as the profile image. If you want to use the first inserted image, use the MIN function instead of MAX.

Although this works, I will still follow @ parantap-parashar's answer as it is more elegant.

Answer 8

Assuming that from the previous answers you get the correct set of results from your SQL query:

 SELECT * FROM `users` LEFT JOIN `images` ON images.id = users.id 

i.e. a table of all users duplicating a row for each user based on the number of artist images that they have, or zeros in the col image if they don't have images

I think what you are missing in your PHP. You need to recognize the change in profile_ID in order to deflate your profile, and then in the inner loop your images, if any.

To do this, you must first save all the artist’s images.

Therefore, you set the first profile identifier, save the images, then when you reach the next profile identifier, you display everything before doing the same with the next, etc.

Note that the top if skipped on the first pass. Also note that variables that capture profile data are overwritten for each repeated line (sounds like 12 for your first profile in your test data), but since the data is the same, every time this is not a problem.

You need to remember the output of the last data set when exiting the main loop.

 $result = $stmt->get_result(); $profile=$result[0]['id'] // initialise $imgs=array(); // inititalise foreach($result AS $row) { if($profile!=$row['id']) { $profile= $row['id']; echo "<div class='container team-wrap'> <div class='row'> <div class='col-md-6'> <img class='img-responsive' src='$image'> </div> <div class=\"col-md-6\"> <strong>$full_name<br>$job_title</strong> <br> <p>$bio</p> <a href='mailto:$email' class='btn btn-info'>Contact Me</a> </div> </div> </div>"; echo "<div class=\"container space team-wrap\"> <div class=\"row\"> <div class=\"col-lg-12\"> <div id=\"gallery-slider\" class=\"slider responsive\"> <div>"; foreach($imgs AS $img) { echo "<a target=\"_blank\" href=\"$img\"> <img src=\"$img\" alt=\"\"></a>"; } $imgs=array(); // Empty out image array echo " </div> </div> </div> </div> <hr> </div>"; } $full_name = $row['full_name']; $email = $row['email']; $job_title = $row['job_title']; $bio = $row['bio']; $ProfilePhoto = $row['profile_photo']; if (isset($ProfilePhoto) && ! empty($ProfilePhoto)) { $image = "$ProfilePhoto"; } else { $image = "avatar.jpg"; } if($row['artist_img']!==NULL) { $imgs[]= $row['artist_img']; } } /* Catch last one */ $full_name = $row['full_name']; $email = $row['email']; $job_title = $row['job_title']; $bio = $row['bio']; $ProfilePhoto = $row['profile_photo']; if (isset($ProfilePhoto) && ! empty($ProfilePhoto)) { $image = "$ProfilePhoto"; } else { $image = "avatar.jpg"; } echo "<div class='container team-wrap'> <div class='row'> <div class='col-md-6'> <img class='img-responsive' src='$image'> </div> <div class=\"col-md-6\"> <strong>$full_name<br>$job_title</strong> <br> <p>$bio</p> <a href='mailto:$email' class='btn btn-info'>Contact Me</a> </div> </div> </div>"; echo "<div class=\"container space team-wrap\"> <div class=\"row\"> <div class=\"col-lg-12\"> <div id=\"gallery-slider\" class=\"slider responsive\"> <div>"; foreach($imgs AS $img) { echo "<a target=\"_blank\" href=\"$img\"> <img src=\"$img\" alt=\"\"> </a>"; } echo " </div> </div> </div> </div> <hr> </div>";