I have a profile page that has 2 database tables, table1.users and table2.images
table1.users has id, email, full_name
and
table2.images has image_id, id, picture_image
This is not a page that requires sessions or login
My problem is that the user can upload images to their profile for user 1, there is 12 images in the database but it only shows 1 and it show that one image under user 2, but that is not user 2's image only user one.
User's profiles will stack under one another on 1 page there will only be 4 users at this time.
the part where $artist_img has a link it should add all images from that artist not just one
<?php $db = dbconnect(); $stmt = $db->prepare('SELECT * FROM users, images GROUP BY users.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='$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> <a target=\"_blank\" href=\"$artist_img\"> <img src=\"$artist_img\" alt=\"\"> </a> </div> </div> <hr> </div> </div> </div>"; } ?> 5 Answers
Answers 1
This is because you are grouping the data in wrong fashion.
As your question describes you have multiple rows for a user id and you are trying to display these multiple results in a single row using the GROUP BY. So, This is not possible for database to group data like this because it does nit know any relation to the data and what is to do with multiple results.
However, you can use JOIN here as it is what you trying to do. Use the following query
SELECT * FROM `users` LEFT JOIN `images` ON images.id = users.id You should then process the result like this:
$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.It should do the work.
Answers 2
Try this one.
SELECT t.id,t.email,t.fullname,t2.image_id,t2.picture_name FROM table2 t LEFT JOIN table1 ON t2.id=t.id Answers 3
Your Database entry must be like below tables.
Table1 - users
------------------------------------------ id | email | full_name ------------------------------------------ 1 | test1@gmail.com | test1 2 | test2@gmail.com | test2 3 | test3@gmail.com | 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 by UserID
SELECT * FROM users INNER JOIN images ON (users.id = images.id) WHERE (images.id = '1') Above query will give you result of profile id 1.
Answers 4
Try this as your 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 should give you the desired results. Please note that it takes the most recent picture as the profile picture. If you want to use the first inserted image use MIN function instead of MAX.
Even though this works, I would still follow @parantap-parashar's answer as it is more elegant.
Answers 5
Assuming from the previous responses you are getting the correct result set 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 artists images they have or nulls in the image col if they have no images.
I think what you are missing is in your PHP. You need to recognise a change in profile_ID to pump out your profile and then in an inner loop your images if there are any.
To be able to do this you need to store all the artist images first.
Therefore you set the first profile id, store up the images, then when you reach the next profile id you output everything before doing the same with the next one etc.
Note that the top if statement is skipped on the first pass. Also note that the variables capturing profile details get overwritten for each duplicate row (sounds like 12 for your first profile in your test data) but as the data is the same each time this is not a problem.
You need to remember to output the last set of data when you exit 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>"; 
0 comments:
Post a Comment