CODEWITHSUNDEEP
phpmysqlarraysforeachinner-join
Ben J
Ben J

Reputation: 59

php foreach loop only returns values for first iteration in array

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();
?>

Upvotes: 4

Views: 1388

Answers (2)

S4NDM4N
S4NDM4N

Reputation: 924

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'")

Upvotes: 3

Shahriar63
Shahriar63

Reputation: 58

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.

Upvotes: 1

Related Questions