php foreach loop only returns values for first iteration in array

Question

I seem to be unable to identify why my foreach loop is able to loop for all 5 of the ProductionOrderID's created but only returns the data for the first ID.

It is my understanding that the array is looping correctly as you can see the current result here: https://i.sstatic.net/KhzIC.png but what's weird is ID:2 has no table being generated and ID 5 has 2 tables created, all blank as per the imgur screenshot just linked.

I've doubled checked my sample data, there are 5 unique records for each table with no duplications or issues that I could find.

EDIT: 1 I forgot to mention the desired result to clarify how I wish the looping to work. Please see this screenshot: https://i.sstatic.net/sOLIv.png (Cheers Sand).

EDIT: 2 Here is an Export of the SQL: https://pastebin.com/MG2gtASu And here is my ERD should it help: https://i.sstatic.net/5nlJu.png

EDIT: 3 New, updated code (Thanks Sand):

<?php
include('OrderCore/connect-db.php');
$POIds = array();
if ($result = $mysqli->query("SELECT ProductionOrderID FROM ProductionOrder" ) ) {
    while ($row = $result->fetch_object()) {
        $POIds[] = $row->ProductionOrderID;
    }
}
foreach ( $POIds as $index => $OrderId ) {
    if ( $result = $mysqli->query("
    SELECT * 
    FROM ProductionOrder AS p
    LEFT JOIN ProductionOrderStatus AS s ON ( p.ProductionOrderID = s.ProductionOrderStatusID ) 
    LEFT JOIN NotGood AS n ON ( p.ProductionOrderID = n.NGID ) 
    LEFT JOIN BatchOrder AS b ON ( p.ProductionOrderID = b.BatchID ) 
    LEFT JOIN Brand AS bd ON ( p.ProductionOrderID = bd.BrandID ) 
    LEFT JOIN CustomerOrder AS co ON ( p.ProductionOrderID = co.COID ) 
    LEFT JOIN Customer AS c ON ( p.ProductionOrderID = c.CustomerID ) 
    LEFT JOIN CustomerOrderStatus AS cos ON ( p.ProductionOrderID = cos.COStatusID ) 
    WHERE p.ProductionOrderID='$OrderId'") ) {
        while( $row = $result->fetch_object() ) {
            print "<h1>Order: $OrderId</h1>";
            print "<table class='table table-striped'>";
            print "<tr> <th>PO ID</th> <th>PO #</th> <th>Order Quantity</th> <th>Balance Left</th> <th>Production Date</th> <th>Production Order Status</th> <th>Not Good ID</th> </tr>";
            print "<td>" . $row->ProductionOrderID . "</td>";
            print "<td>" . $row->PONum . "</td>";
            print "<td>" . $row->OrderQTY . "</td>";
            print "<td>" . $row->BalLeftNum . "</td>";
            print "<td>" . $row->ProductionDate . "</td>";
            print "<td>" . $row->ProductionOrderStatusID . "</td>";
            print "<td>" . $row->NGID . "</td>";
            print "</tr>";
            print "</table>";
            //BatchOrder
            print "<table class='table table-striped'>";
            print "<tr> <th>Batch ID</th> <th>Brand Name</th> <th>Batch Quantity</th> <th>Availability Date</th> <th>Remaining Balance</th> <th>Production Order ID</th> </tr>";
            print "<td>" . $row->BatchID . "</td>";
            print "<td>" . $row->BrandID . "</td>";
            print "<td>" . $row->BatchQTY . "</td>";
            print "<td>" . $row->AvailDate . "</td>";
            print "<td>" . $row->RemainBal . "</td>";
            print "<td>" . $row->ProductionOrderID . "</td>";
            print "</tr>";
            print "</table>";
            //CustomerOrder
            print "<table class='table table-striped'>";
            print "<tr> <th>Customer ID</th> <th>Customer Name</th> <th>Invoice Quantity</th> <th>Invoice #</th> <th>Shipping Date</th> <th>Batch ID</th> <th>CO Status</th> </tr>";
            print "<td>" . $row->COID . "</td>";
            print "<td>" . $row->CustomerID . "</td>";
            print "<td>" . $row->InvoiceQTY . "</td>";
            print "<td>" . $row->InvoiceNum . "</td>";
            print "<td>" . $row->ShipDate . "</td>";
            print "<td>" . $row->BatchID . "</td>";
            print "<td>" . $row->COStatusID . "</td>";
            print "</tr>";
            print "</table>";
        }
    }
    else
    {
        print "No results to display!";
    }
}
$mysqli->close();
?>

You want to loop 5 per page or 5 records each time the SQL runs ? or you just want display the record in your table? — S4NDM4N
– S4NDM4N, Commented Sep 20, 2017 at 4:46
I want to loop every record (ProductionOrder) there is; which is currently 5 but could be 5 or 500. — Ben J
– Ben J, Commented Sep 20, 2017 at 4:50
That is my attempt to create an array which stores all of the ProductionOrderID's in which gets called as $OrderId in the foreach loop. The result currently looks like this: i.imgur.com/MIhfB0s.png — Ben J
– Ben J, Commented Sep 20, 2017 at 4:55
Yes the thing I was trying to figure out is why you're using a foreach if you want display what's in your data table just use a while loop it will loop until there's no more data to display. — S4NDM4N
– S4NDM4N, Commented Sep 20, 2017 at 4:57

S4NDM4N · Accepted Answer · 2017-09-21 03:53:00Z

3

I figured out why it's happening it's due to the join type INNER this will help you to understand.

Found the problem why it was just showing 1 batch of data. It's because you were equaling the p.ProductionOrderID = b.BatchID so what happens is query looks to match your production ID to batch ID and your batch ID is unique so there's no duplicates which leads to showing a single data record from that matching line. What you really want to do is to match the production ID in your batch table because that is the relationship between your production table and the batch table. Now when you run this it will draw tables till the end of batch's.

If you want to show all the batch details in one column in your HTML then suggest while or foreach and you don't need another SQL you already have the rows selected. EX:$row["BatchQTY"]

And here's my solution.

if ( $result = $mysqli->query("
    SELECT * 
FROM ProductionOrder AS p
LEFT JOIN ProductionOrderStatus AS s ON ( p.ProductionOrderID = s.ProductionOrderStatusID ) 
LEFT JOIN NotGood AS n ON ( p.ProductionOrderID = n.NGID ) 
LEFT JOIN BatchOrder AS b ON ( p.ProductionOrderID = b.ProductionOrderID)//Changed this equation 
LEFT JOIN Brand AS bd ON ( p.ProductionOrderID = bd.BrandID ) 
LEFT JOIN CustomerOrder AS co ON ( p.ProductionOrderID = co.COID ) 
LEFT JOIN Customer AS c ON ( p.ProductionOrderID = c.CustomerID ) 
LEFT JOIN CustomerOrderStatus AS cos ON ( p.ProductionOrderID = cos.COStatusID )
    WHERE p.ProductionOrderID='$OrderId'")

edited Sep 21, 2017 at 3:53

answered Sep 20, 2017 at 6:08

S4NDM4N

9382 gold badges13 silver badges31 bronze badges

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

6 Comments

Ben J Over a year ago

Thank you so much!!! And for the visual examples, that definitely helps to clarify the different types. Thanks again for taking the time to understand and help.

Ben J Over a year ago

I did notice that it's not displaying more than 1 Batch or Customer Order per Production Order. Do you have any advice on how to loop through each of them>

S4NDM4N Over a year ago

Sorry I was offline when you commented. No problem your problem also gave me a chance to brush up on my SQL. And about your 2nd comment is this after correcting the SQL ? and what you mean is your system only shows 1 set of data when there more under the same Customer Order ? Also are your using the same SQL or this is different one ?

Ben J Over a year ago

Hi Sand, no worries. So I've updated my original question again with the updated code and that's what I'm using right now. Here you can see it's working fantastically for each new ProductionOrder: i.imgur.com/JXNzEfS.png The only issue is it's only showing me 1 BatchOrder and 1 customer order per ProductionOrder. There can be more than 1 BatchOrder attached to a ProductionOrder and there can be more than 1 CustomerOrder attached to a BatchOrder. I've spent a while trying to create a loop within a loop but not had much success so far!

S4NDM4N Over a year ago

Ah ok think this can be done by using SQL it self can you do me a favor and give me a SQL paste bin with some data which I can recreate your said scenario so I can test it out on my own.

|

Shahriar63 · Accepted Answer · 2017-09-20 06:31:51Z

1

Type inside the while loop

echo "<pre>";
print_r($row);
echo "</pre>";

So you can see the behavior of your data. I think the main problem raise from your select query.

answered Sep 20, 2017 at 6:31

Shahriar63

581 silver badge8 bronze badges

Collectives™ on Stack Overflow

php foreach loop only returns values for first iteration in array

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related