Showing multiple order/orderdetails on single page

[skinnyguy76]

I'm a designer who muddles in PHP when necessary and understand the basics of MySQL queries. I have an order details page generated with a WebAssist extension for Dreamweaver. My client wants to have a 'print page' that pulls ALL unprocessed orders to print, rather than pulling and printing them one at a time. I understand the two queries from the pertinent part of the page below, but I'm  at a loss as to how to query both the order and all the order details (individual ordered items). I'm wondering if this is something I can handle, or I just need to hire a developer to help. Any suggestions would be appreciated.

 

<?php
$ParamOrderID_WADAorders = "-1";
if (isset($_GET['OrderID'])) {
  $ParamOrderID_WADAorders = $_GET['OrderID'];
}
$ParamSessionOrderID_WADAorders = "-1";
if (isset($_SESSION['WADA_Insert_orders'])) {
  $ParamSessionOrderID_WADAorders = $_SESSION['WADA_Insert_orders'];
}
$ParamOrderID2_WADAorders = "-1";
if (isset($_GET['OrderID'])) {
  $ParamOrderID2_WADAorders = $_GET['OrderID'];
}
mysql_select_db($database_adventcms, $adventcms);
$query_WADAorders = sprintf("SELECT OrderID, OrderBusiness, OrderUserID, OrderDate, OrderShipped, OrderAmount, OrderShipName, OrderShipAddress, OrderShipAddress2, OrderCity, OrderState, OrderZip, OrderCountry, OrderPhone, OrderFax, OrderShipping, OrderTax, OrderEmail, OrderTrackingNumber FROM orders WHERE OrderID = %s OR ( -1= %s AND OrderID= %s)", GetSQLValueString($ParamOrderID_WADAorders, "int"),GetSQLValueString($ParamOrderID2_WADAorders, "int"),GetSQLValueString($ParamSessionOrderID_WADAorders, "int"));
$WADAorders = mysql_query($query_WADAorders, $adventcms) or die(mysql_error());
$row_WADAorders = mysql_fetch_assoc($WADAorders);
$totalRows_WADAorders = mysql_num_rows($WADAorders);

$colname_rsOrderDetails = "-1";
if (isset($_GET['OrderID'])) {
  $colname_rsOrderDetails = $_GET['OrderID'];
}
mysql_select_db($database_adventcms, $adventcms);
$query_rsOrderDetails = sprintf("SELECT * FROM orderdetails WHERE DetailOrderID = %s", GetSQLValueString($colname_rsOrderDetails, "int"));
$rsOrderDetails = mysql_query($query_rsOrderDetails, $adventcms) or die(mysql_error());
$row_rsOrderDetails = mysql_fetch_assoc($rsOrderDetails);
$totalRows_rsOrderDetails = mysql_num_rows($rsOrderDetails);?>

 

mod edit: code in code tags please

[spiderwell]

one way to approach it would be to put the order items details enquiry in the loop of the first query, so as you iterate through all the orders, make a call to the details part as you loop through the recordset of the orders.

 

first query here for list of orders

while (looping through the orders)

{

  use the second query here to get item details by passing the id of the order.

}

 

 

[skinnyguy76]

That is the general direction I was thinking, but have no idea how to code that.

[Psycho]

first query here for list of orders

while (looping through the orders)

{

  use the second query here to get item details by passing the id of the order.

}

 

@spiderwell: Not to be rude, but that is terrible advice. You should never run queries in loops, they are terribly inefficient.

 

@skinnyguy76: This is a common pattern that is easily solved. The solution is actually very simple. But, almost everyone has to be shown the first time. What you want to do is query ALL the records you need. Typically this requires a JOIN query. That will result in some data being repeated in the results. In this case you will have the order info repeated for every item in the order details. But, that's OK. The trick is to simply set a flag variable to track the last parent record (order info). Then iterate through all the records to display the child records (order details). The trick is to check if the current parent (order info) is different from the last one - if so, show the parent info.

[spiderwell]

you are correct, no offence taken.

[Psycho]

OK, I looked closer at the code so I could provide an example and am seeing something else that should be changed

 

The logic on the WHERE clause is confusing. It looks like the logic is to select records where the OrderID = the order ID passed in the $_GET parameter OR if no ID is sent then use the orderID in the session. You made that logic way more complicated than it needs to be. You could have just had a single parameter in the where clause such as WHERE OrderID = $orderID and then define $orderID using

//Set order ID
if(isset($_GET['OrderID'])) {
    $orderID = intval($_GET['OrderID']);
} elseif(isset($_SESSION['WADA_Insert_orders'])) {
    $orderID = intval($_SESSION['WADA_Insert_orders']);
}

 

So, if the order ID is set in the GET array it uses that value, else it uses the session value. But, that would only work if the session value is always set. Your current code doesn't account for the GET var and the SESSION var not being set. I've solved that below as well.

 

<?php

//Set order ID
if(isset($_GET['OrderID'])) {
    $orderID = intval($_GET['OrderID']);
} elseif(isset($_SESSION['WADA_Insert_orders'])) {
    $orderID = intval($_SESSION['WADA_Insert_orders']);
}
//Check that an Order ID was set from GET or SESSION
if(!isset($orderID))
{
    die('Unable to get records. No Order ID available.');
}

mysql_select_db($database_adventcms, $adventcms);
//Create and run ONE query to get all the data needed
$query = "SELECT o.OrderID, o.OrderBusiness, o.OrderUserID, o.OrderDate, o.OrderShipped,
                 o.OrderAmount, o.OrderShipName, o.OrderShipAddress, o.OrderShipAddress2,
                 o.OrderCity, o.OrderState, o.OrderZip, o.OrderCountry, o.OrderPhone,
                 o.OrderFax, o.OrderShipping, o.OrderTax, o.OrderEmail, o.OrderTrackingNumber.
                 od.*
          FROM orders AS o
          LEFT JOIN orderdetails as od
            ON od.orderdetails = o.OrderID
          WHERE o.OrderID = {$orderID}
          ORDER BY o.OrderID";
$result = mysql_query($query, $adventcms) or die(mysql_error());

if(!mysql_num_rows($result))
{
    echo "No records found";
}
else
{
    //Create flag to track change in Orders
    $lastOrderID = false;
    while($row = mysql_fetch_assoc($result))
    {
        //Check if the order is different from last
        if($lastOrderID !== $row['OrderID'])
        {
            //This order is different from last.
            //Display the order info
            ##INSERT CODE HERE TO DISPLAY ORDER INFO

            //Set flag so additional details of this order
            //don't display the order info again
            $lastOrderID = $row['OrderID'];
        }

        //Display the order details
        ##INSERT CODE HERE TO DISPLAY ORDER DETAILS
    }
}

?>

[skinnyguy76]

I think I'm getting it. Thanks for the help! My only other question would be can I somehow jump out of the php in the body to structure the tables, divs etc. with the echo's  within. I'm used to just including "<?php echo $row_Recordset1['OrderBusiness']; ?>" within tables/divs.

 

Here's what I have in my head code:

 

<?php

mysql_select_db($database_adventcms, $adventcms);

//Create and run ONE query to get all the data needed

$query_Recordset1 = "SELECT o.OrderID, o.OrderBusiness, o.OrderUserID, o.OrderDate, o.OrderShipped,

                o.OrderAmount, o.OrderShipName, o.OrderShipAddress, o.OrderShipAddress2,

                o.OrderCity, o.OrderState, o.OrderZip, o.OrderCountry, o.OrderPhone,

                o.OrderFax, o.OrderShipping, o.OrderTax, o.OrderEmail, o.OrderTrackingNumber,

                od.*

          FROM orders AS o

          LEFT JOIN orderdetails AS od

            ON od.DetailOrderID = o.OrderID

          WHERE o.OrderShipped = 1

          ORDER BY o.OrderID";

$Recordset1 = mysql_query($query_Recordset1, $adventcms) or die(mysql_error());

$row_Recordset1 = mysql_fetch_assoc($Recordset1);

$totalRows_Recordset1 = mysql_num_rows($Recordset1);

?>

 

And here's what I have in the body as a test:

 

<?php

if(!mysql_num_rows($Recordset1))

{

    echo "No records found";

}

else

{

    //Create flag to track change in Orders

    $lastOrderID = false;

    while($row = mysql_fetch_assoc($Recordset1))

    {

        //Check if the order is different from last

        if($lastOrderID !== $row['OrderID'])

        {

            //This order is different from last.

            //Display the order info

 

 

            echo $row_Recordset1['OrderBusiness']; echo "<br />";         

echo $row_Recordset1['OrderState']; echo "<br />";

            echo $row_Recordset1['OrderAmount']; echo "<br />";

echo "<br />";

 

            //Set flag so additional details of this order

            //don't display the order info again

            $lastOrderID = $row['OrderID'];

        }

 

        //Display the order details

       

echo $row_Recordset1['DetailProductID']; echo "<br />";

echo $row_Recordset1['DetailPrice']; echo "<br />";

echo "<br />";

    }

}

 

?> 

[Psycho]

I highly suggest you get into the habit of separating the logic (i.e. core PHP) and the presentation (i.e. the HTML output). It makes it much easier to maintain and enhance your code. In fact, I actually separate my PHP code and output code into separate files.

 

So, what I suggest is in the loop for generating the order data, you add the output to a variable. Then the only PHP code you need in the presentation (i.e. HTML) are only simple echo statements.

[skinnyguy76]

Agreed. I'm just not skilled enough in PHP to know how to do that. I don't want to take any more of your time. Appreciate all the help.

[spiderwell]

it will come with time! I got there eventually!

[Psycho]

Agreed. I'm just not skilled enough in PHP to know how to do that. I don't want to take any more of your time. Appreciate all the help.

 

I had started to write some code, but your current output wouldn't work. If you are outputting this into a table then you need to have the same number of columns. You have 4 fields in the header rows and only two for the data. I had no clue how they should be structured so I didn't take any time to provide anything.