Why can't I have 2 foreach loops in this code


#1

I’m trying to do a bit of an experiment, however I am encountering an issue.

I am trying to accomplish 2 things from a query:

  1. For every row of data that is in the query, I want the label “Alternate” to appear as a table column header.
  2. For every row of data that is in the query, I want to show the the value of “image_mini_id” from my query.
//Display Table
if (isset($miniSelection)) {
     $sql = "SELECT mini_id, mini_name, mini_number, mini_imag_id FROM tableMinis WHERE mini_id = ?";       
     $stmt = $pdo->prepare($sql);
     $stmt->execute([$miniSelection]);            
     $mini = $stmt->fetch();

     $sql = "SELECT imag_name, imag_mini_id FROM tableImages WHERE imag_mini_id = ?";
     $stmt = $pdo->prepare($sql);
     $stmt->execute([$miniSelection]);

     echo '<table class="optionsMenuTable">';
          echo '<tr>';
               echo '<th>Main</th>';
               foreach ($stmt as $row) {
                    echo '<th>Alternate</th>';
               }
          echo '</tr>';    
          echo '<tr>';
               echo '<td>';
                    echo $mini["mini_image_id"];
               echo '</td>';   

               foreach ($stmt as $row) {
                    echo '<td>';
                         echo $row['imag_name'];
                    echo '</td>';
               }
          echo '</tr>';     
     echo '</table>';
}

This table environment (and all the echo’s) is just to rough out what I am trying to achieve – I will be changing it all later once I have the functionality I am trying to achieve.

To help understand what I am trying to do, I’ll explain this a bit.

tableMinis has a column “mini_imag_id”. This is the id# of a corresponding image from the images table (tableImages). The image linked to this id# is considered the “main display image”.

tableImages contains all images, and consists of 3 columns, an id# column (imag_id), a name column (imag_name), and linking column (imag_mini_id) which links the id# of a record in tableMinis.

Eventually, I will want to show the the “main image” associated to the id of record in tableMinis, but then next to it, I want to also show the secondary (or alternate) images which are also linked to that id#.

Could someone please tell me why the results of the second foreach loop are null. When I remove the first foreach loop (which removes my table headers) then second foreach loop works fine (however I want the headers to populate in equal amount to the data returned).


#2

Note: I do realize that joining those 2 queries would be best, and I will do that, however that does not affect the issue I am encountering in the second foreach.

I also tried a different appearance way, however the results are the same – the second foreach does not work:

            echo '<table class="optionsMenuTable">';
                echo '<tr>';
                    foreach ($stmt as $row) {
                        echo '<td>' . $row['imag_name'] . '</td>';
                    }
                echo '</tr>';
                echo '<tr>';
                    foreach ($stmt as $row) {
                        if ($mini['$mini_imag_id'] = $row['imag_id']) {
                            echo '<td>Main</td>';
                        }
                        else {
                            echo '<td>Alternate</td>';
                        }
                            
                    }
                echo '</tr>';   
            echo '</table>';

EDIT: I have also tried adding in a FetchAll: $result = $stmt->fetchAll(); but that doesn’t work either:

        //Display Table
        if (isset($miniSelection)) {
            $sql = "SELECT mini_id, mini_name, mini_number, mini_imag_id FROM tableMinis WHERE mini_id = ?";       
            $stmt = $pdo->prepare($sql);
            $stmt->execute([$miniSelection]);            
            $mini = $stmt->fetch();
            $stmt = null;

            echo 'Images for: ' . $mini["mini_name"];
            if (isset($mini["mini_number"])) {
                echo ' (' . $mini["mini_number"] . ')';
            }

            $sql = "SELECT imag_name, imag_mini_id FROM tableImages WHERE imag_mini_id = ?";
            $stmt = $pdo->prepare($sql);
            $stmt->execute([$miniSelection]);
            $result = $stmt->fetchAll();
            
            echo '<table class="optionsMenuTable">';
                echo '<tr>';
                    foreach ($result as $row) {
                        echo '<td>' . $row['imag_name'] . '</td>';
                    }
                echo '</tr>';
                echo '<tr>';
                    foreach ($result as $row) {
                        if ($mini['$mini_imag_id'] = $row['imag_id']) {
                            echo '<td>Main</td>';
                        }
                        else {
                            echo '<td>Alternate (' . $mini['$mini_imag_id'] . '/' . $row['imag_id'] . ')</td>';
                        }
                            
                    }
                echo '</tr>';   
            echo '</table>';

        }

#3

I think I figured out what was not working – sort of have it working now.


#4

You need to use a JOIN, not two queries.


#5

I have done the join, the only thing I am not able to figure out is how I can query a single value.

+---------+-----------+-------------+--------------+---------+--------------+--------------+
| mini_id | mini_name | mini_number | mini_imag_id | imag_id | imag_name    | imag_mini_id |
+---------+-----------+-------------+--------------+---------+--------------+--------------+
|       2 | Boar      | --          |           14 |      13 | wereboar.jpg |            2 |
|       2 | Boar      | --          |           14 |      14 | boar.jpg     |            2 |
+---------+-----------+-------------+--------------+---------+--------------+--------------+

Here is the table from the JOIN result.

Since this was created from the “mini_id”, I want to show that the “mini_name” for the “mini_name” value of “2”, is “Boar”.

I can’t figure out how I can get that value, since I query it only once outside of the foreach. Could you please recommend the SQL I would need for that.


#6

I just can’t get foreach or while loops to work with this query. Clearly there is something I am doing wrong, but I can’t figure it out. Please help.

        //Display Table
        if (isset($miniSelection)) {
            $sql = "
                SELECT tableMinis.mini_id, tableMinis.mini_name, tableMinis.mini_number, tableMinis.mini_imag_id, tableImages.imag_id, tableImages.imag_name, tableImages.imag_mini_id 
                FROM tableImages 
                LEFT JOIN tableMinis 
                ON tableImages.imag_mini_id = tableMinis.mini_id 
                WHERE mini_id = ?";
            $stmt = $pdo->prepare($sql);
            $stmt->execute([$miniSelection]);            
            $result = $stmt->fetch();

            echo 'Images for: ' . $result["mini_name"];
            if (isset($result["mini_number"])) {
                echo ' (' . $result["mini_number"] . ')';
            }

            echo '<table class="optionsMenuTable">';
                echo '<tr>';
                    while($result = $stmt->fetch()) {
                        echo '<td>' . $result['imag_name'] . '</td>';
                    }
                echo '</tr>';
                echo '<tr>';
                    while($result = $stmt->fetch()) {
                        if ($result['$mini_imag_id'] == $result['imag_id']) {
                            echo '<td>Main (' . $result['mini_imag_id'] . '|' . $result['imag_id'] . ')</td>';
                        }
                        else {
                            echo '<td>Alternate (' . $result['mini_imag_id'] . '/' . $result['imag_id'] . ')</td>';
                        }
    
                    }
                echo '</tr>';   
            echo '</table>';

        }

#7

See my next post below


#8

I switch to the while loops, because the foreach were causing many errors:

Here’s the code I used (with line numbers):

139        //Display Table
140        if (isset($miniSelection)) {
141           $sql = "
142                SELECT tableMinis.mini_id, tableMinis.mini_name, tableMinis.mini_number,
                   tableMinis.mini_imag_id, tableImages.imag_id, tableImages.imag_name,
                   tableImages.imag_mini_id 
143                FROM tableImages 
144                LEFT JOIN tableMinis 
145                ON tableImages.imag_mini_id = tableMinis.mini_id 
146                WHERE mini_id = ?";
147            $stmt = $pdo->prepare($sql);
148            $stmt->execute([$miniSelection]);            
149            $result = $stmt->fetch();
150
151            echo 'Images for: ' . $result["mini_name"];
152            if (isset($result["mini_number"])) {
153                echo ' (' . $result["mini_number"] . ')';
154            }
155
156            echo '<table class="optionsMenuTable">';
157                echo '<tr>';
158                    foreach ($result as $row) {
159                        echo '<td>' . $row['imag_name'] . '</td>';
160                    }
161                echo '</tr>';
162                echo '<tr>';
163                    foreach ($result as $row) {
164                        if ($row['mini_imag_id'] == $row['imag_id']) {
165                            echo '<td>Main</td>';
166                        }
167                        else {
168                           echo '<td>Alternate</td>';
169                        }
170                    }
171                echo '</tr>';   
172            echo '</table>';
173        }

And here is the result:

Warning: Illegal string offset ‘imag_name’ in /home/ubuntu/workspace/minisgallery v2/Live Files/controls/main-images.php on line 159 Call Stack: 0.0011 240680 1. {main}() /home/ubuntu/workspace/minisgallery v2/Live Files/controls/main-images.php:0 Warning: Illegal string offset ‘imag_name’ in /home/ubuntu/workspace/minisgallery v2/Live Files/controls/main-images.php on line 159 Call Stack: 0.0011 240680 1. {main}() /home/ubuntu/workspace/minisgallery v2/Live Files/controls/main-images.php:0 Warning: Illegal string offset ‘imag_name’ in /home/ubuntu/workspace/minisgallery v2/Live Files/controls/main-images.php on line 159 Call Stack: 0.0011 240680 1. {main}() /home/ubuntu/workspace/minisgallery v2/Live Files/controls/main-images.php:0 Warning: Illegal string offset ‘mini_imag_id’ in /home/ubuntu/workspace/minisgallery v2/Live Files/controls/main-images.php on line 164 Call Stack: 0.0011 240680 1. {main}() /home/ubuntu/workspace/minisgallery v2/Live Files/controls/main-images.php:0 Warning: Illegal string offset ‘imag_id’ in /home/ubuntu/workspace/minisgallery v2/Live Files/controls/main-images.php on line 164 Call Stack: 0.0011 240680 1. {main}() /home/ubuntu/workspace/minisgallery v2/Live Files/controls/main-images.php:0 Warning: Illegal string offset ‘mini_imag_id’ in /home/ubuntu/workspace/minisgallery v2/Live Files/controls/main-images.php on line 164 Call Stack: 0.0011 240680 1. {main}() /home/ubuntu/workspace/minisgallery v2/Live Files/controls/main-images.php:0 Warning: Illegal string offset ‘imag_id’ in /home/ubuntu/workspace/minisgallery v2/Live Files/controls/main-images.php on line 164 Call Stack: 0.0011 240680 1. {main}() /home/ubuntu/workspace/minisgallery v2/Live Files/controls/main-images.php:0 Warning: Illegal string offset ‘mini_imag_id’ in /home/ubuntu/workspace/minisgallery v2/Live Files/controls/main-images.php on line 164 Call Stack: 0.0011 240680 1. {main}() /home/ubuntu/workspace/minisgallery v2/Live Files/controls/main-images.php:0 Warning: Illegal string offset ‘imag_id’ in /home/ubuntu/workspace/minisgallery v2/Live Files/controls/main-images.php on line 164 Call Stack: 0.0011 240680 1. {main}() /home/ubuntu/workspace/minisgallery v2/Live Files/controls/main-images.php:0


#9

Change fetch to fetchAll. Do a print_r on $results and make sure the expected data is there.

If you are still having problems post an SQL dump of your DB with a few sample records so I can test.


#10

To start with, the table positions in the query are backwards. You are primarily querying for mini data and any related image data. You should also be using table alias names to simplify the sql syntax. The query should be using - FROM tableMinis m LEFT JOIN tableImages i ON m.mini_id = i.imag_mini_id

Next, two of the reasons you were given for fetching all the data from a query into an appropriately named variable were to force you to think about what data you needed and to get any database specific code out of the html document. For a query that will return a set of zero or more rows of data, you would use the fetchAll() method.

At the point of producing the output from the fetched data, you need to address the possibility of no matching data from the query (the variable holding the data will be empty()) and output an appropriate message.

To get the common mini values for producing a header/title section, use this - $header_row = current($result); You can then reference the ‘mini_name’ and ‘mini_number’ elements in $header_row.

You would then use a foreach() loop to loop over the rows in $result to produce the remainder of the output.


#11

benanamen and phdr, thank you so much for your help on this. I finally have it working now!

This is the only part that is not working for me right now.

I am checking for data with this:

if ($stmt->rowCount() > 0) {
     // do stuff
}
else {
     echo 'no results were found';
}

When there is data, everything works fine. When there is no data, no table displays, however the message in the else condition is not displayed.

I checked to ensure there was no data with print_r ($result); and the result was: Array ( )

Would this be a correct result for an array with no data? (There is a single space between the brackets).


#12

There is no need to check for a row count. It is as simple as this

if ($result){
    //Process Results
}
else{
echo 'No Results';
}

From the fetchAll manual…
An empty array is returned if there are zero results to fetch, or **FALSE** on failure.

The if function is a boolean check. It checks for true or false. An empty array will also check as false.


#13

Thanks again - I really appreciate you helping me learn all this.