php mysql while循环里面循环查询同一个表

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

直到这一点，一切正常，它向我显示我的数据库中的所有相册的名称。我接下来要做的是我希望它显示属于$ album_names的所有图片所以我在上面的代码之后编写了这段代码

// 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.

问题是它只显示$ image_path和$ thumbnail_path的第一个结果，但我希望它显示属于该特定专辑的所有图像路径。

Hope I have cleared my question .

希望我已经解决了我的问题。

3 个解决方案

#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标签）。无论如何，问题如下..你的html标签李安排是错误的。这就是为什么你没有看到其他结果。它必须修复如下

<?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

?>

#2

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).

简化为单个查询。这假设image_path和resized path一直都存在于每个图像中（如果它们不是很容易修复，但这会显示基础知识）。

<?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>";

?>

#3

I do not know if my answer will help you.

我不知道我的回答是否会对你有所帮助。

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

但我认为你的循环中的html标签有错误。

 <?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

你在第二个循环中循环关闭标记（li和ul），但是你没有为li和ul放置开放标记。我希望这有帮助。谢谢