php mysql while loop inside while loop querying same table

Question

This is my code

<?php

    $query= "SELECT album_name FROM gallery group by album_name order by MIN(date_time)";
     $stmt = $connection->prepare($query);
     $stmt->execute();
     $result=$stmt->get_result();
           while ($row = $result->fetch_array(MYSQLI_BOTH)) { //First while loop
           $album_names =htmlspecialchars($row['album_name']);` 
    ?>
      <ul>
        <li data-tags="<?php echo $album_names ?>">

Till this point everything works fine , It shows me the names of all the albums that are there in my db . What I want to do next is I want it to show all the pics which belong to $album_names so I wrote this code after above code

// Code continues
 <?php

$query2= "SELECT imgage_path,resized_path FROM gallery WHERE album=?"; 
 $stmt = $connection->prepare($query2);
 $stmt->bind_param("s",$album_names);
 $stmt->execute();
 $result2=$stmt->get_result();
       while ($row2 = $result2->fetch_array(MYSQLI_BOTH)) { //Second while loop


  $thumbnail_path = =htmlspecialchars($row2['resized_path']);
  $image_path =htmlspecialchars($row2['image_path']);

     ?>
          <a href="<?php echo $image_path ?>">
          <img src="<?php echo $thumbnail_path ?>" alt="Illustration" />
                </a>

            </li>
        </ul>
  <?php

   } //Second While loop ends

} //First While loop ends

?>

Problem is that it only shows me the first result of $image_path and $thumbnail_path but I want it to show all the image paths belonging to that particular album.

Hope I have cleared my question .

both queries are selecting from the same table but the first only retrieves the album_name - why not use the outer query to return all the information you want and remove the inner query? — Professor Abronsius
– Professor Abronsius, Commented Feb 11, 2016 at 8:18
@RamRaider The first query shows DISTINCT album names but in second query I want it to includes all albums without bothering about duplicate album names so that I can fetch all pics from that particular album. — MR-4O4
– MR-4O4, Commented Feb 11, 2016 at 8:21
With a single query things would be a lot more efficient, and you can just output the album details when they change. Nested queries like you have here tend to be very inefficient. — Kickstart
– Kickstart, Commented Feb 11, 2016 at 9:48

Thanga · Accepted Answer · 2016-02-11 09:45:45Z

1

This looks like pseudo-code to me (as it is not having proper php tags). Anyway, Problem is as follows.. Your html tags li arrangements are wrong. That is why you are not seeing the other results. It has to be fixed as below

<?php
    $query= "SELECT album_name FROM gallery group by album_name order by MIN(date_time)";
    $stmt = $connection->prepare($query);
    $stmt->execute();
    $result=$stmt->get_result();
    while ($row = $result->fetch_array(MYSQLI_BOTH)) { //First while loop
    $album_names =htmlspecialchars($row['album_name']);` 
?>
<ul>
    <li data-tags="<?php echo $album_names ?>"> 
    <?php

        $query2= "SELECT imgage_path,resized_path FROM gallery WHERE album=?"; 
        $stmt = $connection->prepare($query2);
        $stmt->bind_param("s",$album_names);
        $stmt->execute();
        $result2=$stmt->get_result();
        while ($row2 = $result2->fetch_array(MYSQLI_BOTH)) { //Second while loop


        $thumbnail_path = =htmlspecialchars($row2['resized_path']);
        $image_path =htmlspecialchars($row2['image_path']);

    ?>
    <a href="<?php echo $image_path ?>">
        <img src="<?php echo $thumbnail_path ?>" alt="Illustration" />
    </a>

    <?php
    } //Second While loop ends
    ?>
    </li>
</ul>
<?php

} //First While loop ends

?>

edited Feb 11, 2016 at 9:45

answered Feb 11, 2016 at 8:22

Thanga

8,2003 gold badges22 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MR-4O4 Over a year ago

Thanks for answering but its still not working , please see my updated code if there's a problem in it.

MR-4O4 Over a year ago

Thanks, its working but I wonder I it is possible to do it in single query.I read a lot of demerits of using nested loops. Any suggestions ? Thanks.

Kickstart · Accepted Answer · 2016-02-15 10:17:23Z

1

Simplifying to a single query. This assumes that image_path and resized path always both exist for each image (if they don't it is easily fixed but this shows you the basics).

<?php

// Query gets one row per album. Row has 3 fields, album name, then all the image_path fields concatentated together, the all the resized_path fields joined together
$query= "SELECT album_name 
                GROUP_CONCAT(image_path ORDER BY image_path) AS image_path_concat,
                GROUP_CONCAT(resized_path ORDER BY image_path) AS resized_path_concat
        FROM gallery 
        GROUP BY album_name 
        ORDER BY MIN(date_time)";
$stmt = $connection->prepare($query);
$stmt->execute();
$result=$stmt->get_result();
// Start of unordered list of album names
echo "<ul>";
while ($row = $result->fetch_array(MYSQLI_BOTH)) 
{ 
    $album_names = htmlspecialchars($row['album_name']); 
    // List item of album name
    echo "<li data-tags='$album_names'>"; 

    // Explode out the image paths into an array
    $image_path = explode(',', $row['image_path_concat']);
    // Explode out the image resized paths into an array
    $resized_path = explode(',', $row['resized_path_concat']);

    // Start of unordered list of image paths
    echo "<ul>";

    // Loop around the array of image paths
    foreach($image_path AS $key=>$value)
    {
        // Assuming that the list of image paths always match the array of resized paths then use the key of the current
        // image_path for both the image path and also the resized / thumbnail path
        $image_path = htmlspecialchars($image_path[$key]);
        $thumbnail_path = htmlspecialchars($resized_path[$key]);

        echo "<li><a href='".$image_path."'><img src='".$thumbnail_path."' alt='Illustration' /></a></li>";
    }
    // End of unordered list of image paths
    echo "</ul>";
    // End of list item of album name
    echo "</li>";

} //First While loop ends

// End of unordered list of album names
echo "</ul>";

?>

edited Feb 15, 2016 at 10:17

answered Feb 11, 2016 at 9:58

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

4 Comments

MR-4O4 Over a year ago

I really appreciate the effort to have the work done in single query. I too want it that way but the problem is that your sql query is outputting limited records and not all , as a album_name is repeated multiple times in db having different image_paths . As we are using GROUP BY for showing album names , it can not fetch all the image_paths associated with that album_name at a time .

Kickstart Over a year ago

@kamronshaw - That is why GROUP_CONCAT is used. You are abusing GROUP BY in your first query to give the results that DISTINCT would give (ie, one row per album name), while GROUP BY is meant to be used when you need aggregated values for each album name. GROUP_CONCAT will in this case gives you every image_path / resized_path separated by commas for each album name, and then these are split in php for the inner loop. Note that there are potential other issues (by default GROUP_CONCAT is limited to ~1000 characters but easily reset), but hoping it gives you the basic idea

MR-4O4 Over a year ago

Yes, I guess your approach is right , but as I am a newbie I am encountering many errors implementing this code . Can you please update your code with appropriate comments .Also in this line : <a href="<?php echo $image_path[$key] ?>"> Its echoing ""(empty string), I tried changing it to $image_path[$value] , then it echoed the path but as a string , while it was expecting array. A help would be greatly appreciated. Thanks

Kickstart Over a year ago

I have cleaned the code up a bit and added some comments. Hope it helps

Hendra Halim · Accepted Answer · 2016-02-11 08:55:28Z

0

I do not know if my answer will help you.

But i think there is wrong in your loop for the html tag.

 <?php

$query2= "SELECT imgage_path,resized_path FROM gallery WHERE album=?"; 
 $stmt = $connection->prepare($query2);
 $stmt->bind_param("s",$album_names);
 $stmt->execute();
 $result2=$stmt->get_result();
       while ($row2 = $result2->fetch_array(MYSQLI_BOTH)) { //Second while loop


  $thumbnail_path = =htmlspecialchars($row2['resized_path']);
  $image_path =htmlspecialchars($row2['image_path']);

     ?>
          <a href="<?php echo $image_path ?>">
          <img src="<?php echo $thumbnail_path ?>" alt="Illustration" />
                </a>

            </li> <- you loop the close <li> tag
        </ul> <- you loop the close <ul> tag
  <?php

   } //Second While loop ends

} //First While loop ends

?>

You loop the close tags (li and ul) in the second loop but you do not put open tag for the li and ul. I hope this help. thankyou

answered Feb 11, 2016 at 8:55

Hendra Halim

221 silver badge5 bronze badges

2 Comments

MR-4O4 Over a year ago

In the first code snippet (at the starting) I have opened both <ul> and <li> tags. Second code snippet (which you have included here) continues after 1st one.

Hendra Halim Over a year ago

yeah. i mean, if you only have 1 row data for the second while loop (in the second snippet), it's okay. because it will close the open tag <ul> and <li> in the first snippet. But if you have more than 1 row data, it will close the <ul> and <li> tag multiple times with only 1 time open tag. You should close it after the the while loop.

Collectives™ on Stack Overflow

php mysql while loop inside while loop querying same table

3 Answers 3

2 Comments

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related