I’m thinking I need to use a while loop within a while loop, but when I do I receive bizarre results.
I have the following PHP:
$sql = "SELECT c.id, c.title, c.image, u.profileImage, u.fname, u.lname, u.level AS ulevel, u.city, u.state, u.hs, u.ps, uc.id, uc.cid
FROM c, u, uc
WHERE uc.cid = c.id AND uc.userid = u.id
ORDER BY usercollege.createdate ASC";
if ($result = mysqli_query($conn, $sql)) {
while ($row = mysqli_fetch_assoc($result)) {
if ($row['status'] == null) {
echo "";
}
if ($row['ulevel'] == "A Profile") {
echo '<tr>' .
'<td class="small">' . '<a href="http://www.example.com/aprofile.php?user="'.$row["username"] . '>' . '<img src="'.$row['profileImage'].'" />' . '</a>' . '</td>' .
'<td class="large">' . $row['fname']. ", " . $row['lname'] . '</td>' .
'<td class="large">' . $row['city'] . ", " . $row['state'] . '</td>' .
'<td class="large">' . $row['hs'] . '</td>' .
'<td class="largest">' .
'<div class="Limage">' . '<img src="images/c/'.$row['image'].'"/>' . '</div>' .
'</td>' .
'</tr>';
$row['image'] within <div class="Limage"> currently returns just ONE image, but the database can have up to 10 images per userid. Obviously, this loop (if there are more than one image per user) will create a new row for the user with a different image.
I am trying to achieve having all images (up to 10) appear within the same row for the same user, without replicating the same user in the table.
Advertisement
Answer
while your question is a bit hard to follow I can tell you what you need to do, which is not a loop within a loop ( even though it is ). So because I don’t care to decider you schema but I think I understand your issue, i’ll outline it with psudo code
First you need to organize your data
if ($result = mysqli_query($conn, $sql)) {
$users = []; //initialize variable to store data in.
while ($row = mysqli_fetch_assoc($result)) {
if( !isset( $users['u'.$row['id']]){
$users['u'.$row['uid']] = $row; //group users by id
}
$users['u'.$row['uid']]['images'][] = $row['image'];
}
//*note you'll have to probably add u.id as uid to you select.
foreach( $users as $user ){
//... code/html for displaying user (open).
foreach( $user['images'] as $img){
//... code/html for displaying image.
}//end foreach image
//... code/html for displaying user (close).
}//end foreach user
What you wind up with for the $users array is something like this when you output it with var_export() etc.
[
[ 'u10' => [
'id' => 10,
'images' => [
'{src for image1}',
'{src for image2}',
]
]
When you pull the records with the join you get a reflection of the one to many relationship users have to images. So you have the same user row for each image row. All you need to do is group them by user id, and that will make your code down stream much easier. Unless your dealing with 1000’s of users it will probably have minimal impact on the time, and is well worth it to simplify the HTML structuring. The order they are returned in the sql could be causing all kinds of issues, you could start with user 1 have 10 other users then end with user 1, this way they are grouped nicely in the first loop. To group them we can rely on the fact that associative keys in PHP are unique.
I add the u{id} there because certain PHP functions have a way of resting numeric keys, that just avoids that becoming an issue. Most the time it’s not needed, but it’s precautionary and takes little effort, makes the array a bit easier to read and IMO a good habit.
Lastly if I recall correctly in CSS, an id of id="10" ( #10{ ..no dice.. } ) is not valid but one of id="u10" is. A bit of an explanation on that can be found here