Mysqli to Array foreach在foreach内; 在foreach中使用另一个mysqli to array不能正确地从外部数组中读取值

Question

Ok here is my set up of the outer loop.

$sql    ="SELECT * from tblStock";
$result =mysqli_query($conn, $sql);
while($row=mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    $rows[]=$row;
}
foreach($rows as $ad) {

Then keeping the connection open Inside of this loop I have a part I need to read the image urls from a database.

$sql        ="SELECT url from Photos where itemId ='" . $ad['ItemID'] . "';";
    echo $sql;
    $imageresult=mysqli_query($conn, $sql);
    while($imagerow=mysqli_fetch_array($imageresult, MYSQLI_ASSOC)) {
        $imagerows[]=$imagerow;
    }

I empty the variables that would be used inside the new loop so they are clean for every new row.

$image1="";
$image2="";
$image3="";
$count =0;

Then I use this to get out up to 3 image urls.

foreach($imagerows as $value) {
        switch($count) {
            case 2:
                $image3=$value['url'];
                $count =$count+1;
                break;
            case 1:
                $image2=$value['url'];
                $count =$count+1;
                break;
            case 0:
                $image1=$value['url'];
                $count =$count+1;
                break;
        } 
    }

These values are later written to new line in a tab file within the $rows as $ad foreach I set up at the beginning.

What I am getting is this below where the inner foreach loop for the pictures is getting stuck on the first item in the stock table and not reading from the new $ad['ItemID'] on each iteration.

Answer 1

In your below code:

$sql ="SELECT url from Photos where itemId ='" . $ad['ItemID'] . "';"; echo $sql; $imageresult=mysqli_query($conn, $sql); while($imagerow=mysqli_fetch_array($imageresult, MYSQLI_ASSOC)) { $imagerows[]=$imagerow; }

You should reset the $imagerows[] array just before the while loop begins. So it should look like:

$sql ="SELECT url from Photos where itemId ='" . $ad['ItemID'] . "';"; echo $sql; $imageresult=mysqli_query($conn, $sql); $imagerows = array(); while($imagerow=mysqli_fetch_array($imageresult, MYSQLI_ASSOC)) { $imagerows[]=$imagerow; }

If you don't do that, everytime the results will keep getting added at the end of the $imagerows. Thus it will hold all elements of the entire result set. As a result, everytime it goes in foreach($imagerows as $value) loop, it will take the first 3 rows, assign their images & then not do anything (as you have restricted $count only for cases 0, 1 & 2.