Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I have a PHP/MySQL query that returns to an HTML table, and I'm stuck on a part where I need to make a second while loop in that query. I'm not sure where to go from here. I've tried a couple of different ways.

I want it to loop and give me the first set of data, then use the "Order_ID" and get a second set of data and put that second set in the first loop, then do it again.

Here's what I have...

<?php
    $arrayLC = array();

    $OrdersToShip = mysql_query("
        SELECT *
        FROM Orders o
        WHERE o.LotCoded = 0 ORDER BY o.Order_ID");

    if ($OrdersToShip) {
        while ($info = mysql_fetch_array($OrdersToShip))
        {
            $Order_ID = $info['Order_ID'];
            $Customer_ID = $info['Customer_ID'];
            $OrderDate = $info['OrderDate'];

            $lotCodes = mysql_query("SELECT lotCode, Qty FROM `OrdersLotCodes` WHERE `Order_ID` = '".$Order_ID."'");

            if($lotCodes) {
                while ($info = mysql_fetch_array($lotCodes))
                {
                    $lotCode = $info['lotCode'];
                    $Qty = $info['Qty'];
                    array_push($arrayLC, $lotCode, $Qty);
                }
            }

            echo '<tr class="OLine">
                 <td><input type="button" class="button viewThis" value="VIEW"></td>
                 <td>'.$Order_ID.'</td>
                 <td>'.$Customer_ID.'</td>
                 <td>'.$OrderDate.'</td>
                 <td>'.print_r($arrayLC).'</td>
                 </tr>';
        }
    }
    else {
        echo "encountered an error.".mysql_error();
    }
    mysql_close($conn);
?>

What am I missing? What should I do?

::EDIT::

I've changed the mysql_query to:

SELECT o.Order_ID, o.Customer_ID, o.OrderDate, olc.lotCode, olc.qty
FROM Orders o
LEFT JOIN OrdersLotCodes olc ON o.Order_ID = olc.Order_ID
WHERE o.LotCoded = 0 ORDER BY o.Order_ID

Now, how would I take the output with the OrderLotCodes and put them into an array to be printed in the table? How would I put them in an array then bring the related one by Order_ID?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

One problem that I can see is that you are not resetting $arrayLC inside the outer while loop; therefore, the codes from each order get appended to those of the previous one, ad infinitum.

if($OrdersToShip) {
    while ($info = mysql_fetch_array($OrdersToShip)) {

    $arrayLC = array(); // SHOULD BE IN HERE!

Apart from that, when you get this code working you should think about the fact that for N orders, this code executes N + 1 queries (1 to get the orders and one per order to get the items). This is a really inefficient way to do things considering that you can retrieve the same information with just one query if you perform a left outer join. So your next stop should be reading up on SQL joins and how they can help you retrieve associated data.

share|improve this answer
    
Please see the edits. –  Monty Mar 26 '12 at 17:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.