The view_orders.php script purposefully does not include pagination or a way for the administrator to sort by column (that is, by the customer’s last name or zip code).
Both of these features, and many, many more, can easily be added to the page by using one of the available table plug-ins for jQuery. I’ve used, for example, Datatables (www.datatables.net) before, with great success. See the Datatables Web site, or the book’s corresponding site, for how it might be integrated into view_orders.php.
ptg
Viewing One Order
The view_order.php script receives the order ID in the URL and displays all the order’s details (Figure 11.12). The administrator can then click a button to mark the order as shipped. At that point, the order cycle will be complete.
Figure 11.12
To start this process, the next series of steps will explain how to display the order’s contents.
1. Create a new PHP script in your text editor or IDE to be named view_order.php and stored in the administrative directory.
2. Include the configuration file and the header:
<?php
require ('../includes/config.inc.php');
$page_title = 'View An Order';
include ('./includes/header.html');
3. Validate the order ID:
$order_id = false;
if (isset($_GET['oid']) && (filter_var($_GET['oid'], FILTER_VALIDATE_INT,
➥array('min_range' => 1))) ) {
$order_id = $_GET['oid'];
$_SESSION['order_id'] = $order_id;
} elseif (isset($_SESSION['order_id']) && (filter_var($_SESSION[
➥'order_id'], FILTER_VALIDATE_INT, array('min_range' => 1))) ) {
$order_id = $_SESSION['order_id'];
}
The script can’t function at all if it does not have access to a valid order ID
ptg it should receive an order ID in the URL (from the link on view_orders.php).
If that’s the case, the local $order_id variable is created for use in a query later in the script, and the order ID is stored in the session for use when the page is submitted back to itself.
If the order ID is not in the URL but is in the session, that order ID value is assigned to a local variable and will be used by the page instead. This would be the case when the administrator clicks the Ship This Order button.
4. Stop the page if the $order_id is not valid:
if (!$order_id) {
echo '<h3>Error!</h3><p>This page has been accessed in error.</p>';
include ('./includes/footer.html');
exit( );
}
If the page does not have a valid order ID, there’s no point in continuing. An error will be printed, the footer included, and the script terminated.
5. Require the database connection:
require(MYSQL);
6. Define and execute the query:
$q = 'SELECT total, shipping, credit_card_number, DATE_FORMAT(
➥order_date, "%a %b %e, %Y at %h:%i%p") AS od, email,
➥CONCAT(last_name, ", ", first_name) AS name, CONCAT_WS(" ",
➥address1, address2, city, state, zip) AS address, phone, customer_id,
➥CONCAT_WS(" - ", ncc.category, ncp.name) AS item, ncp.stock,
➥quantity, price_per, DATE_FORMAT(ship_date, "%b %e, %Y") AS sd
➥FROM orders AS o INNER JOIN customers AS c ON (o.customer_id = c.id)
➥INNER JOIN order_contents AS oc ON (oc.order_id = o.id) INNER JOIN
➥non_coffee_products AS ncp ON (oc.product_id = ncp.id AND
➥oc.product_type="other") INNER JOIN non_coffee_categories AS ncc
➥ON (ncc.id = ncp.non_coffee_category_id) WHERE o.id=' . $order_id . ' UNION
SELECT total, shipping, credit_card_number, DATE_FORMAT(
➥order_date, "%a %b %e, %Y at %l:%i%p"), email,
➥CONCAT(last_name, ", ", first_name), CONCAT_WS(" ", address1,
➥address2, city, state, zip), phone, customer_id, CONCAT_WS(" - ",
➥gc.category, s.size, sc.caf_decaf, sc.ground_whole) AS item, sc.stock,
➥quantity, price_per, DATE_FORMAT(ship_date, "%b %e, %Y") FROM
➥orders AS o INNER JOIN customers AS c ON (o.customer_id = c.id)
➥INNER JOIN order_contents AS oc ON (oc.order_id = o.id) INNER (continues on next page)
ptg
➥JOIN specific_coffees AS sc ON (oc.product_id = sc.id AND
➥oc.product_type="coffee") INNER JOIN sizes AS s ON (s.id=sc.size_id)
➥INNER JOIN general_coffees AS gc ON (gc.id=sc.general_coffee_id)
➥WHERE o.id=' . $order_id;
$r = mysqli_query($dbc, $q);
This query is similar to those in Chapter 9, “Building a Shopping Cart,”
in that it requires a UNION of two SELECT statements. Unlike that chap- ter’s queries, this query must also join in the customers, orders, and order_contents tables. Figure 11.13 shows the result of running this query.
Figure 11.13
7. If rows were returned, start a form:
if (mysqli_num_rows($r) > 0) { echo '<h3>View an Order</h3>
<form action="view_order.php" method="post" accept-charset=
➥"utf-8">
<fieldset>';
The form posts back to this same page and only contains, as written, a submit button.
8. Fetch the first returned row and display the general information:
$row = mysqli_fetch_array($r, MYSQLI_ASSOC);
echo "<p><strong>Order ID</strong>: $order_id<br /><strong>
➥Total</strong>: \${$row['total']}<br /><strong>Shipping
➥</strong>: \${$row['shipping']}<br /><strong>Order Date
➥</strong>: {$row['od']}<br /><strong>Customer Name
➥</strong>: {$row['name']}<br /><strong>Customer Address
➥</strong>: {$row['address']} <br /><strong>Customer Email
➥</strong>: {$row['email']}<br /><strong>Customer Phone
➥</strong>: {$row['phone']}<br /><strong>Credit Card Number
➥Used</strong>: *{$row['credit_card_number']}</p>";
The query will return the general order and customer information once for each item in the order (see Figure 11.13). To display the general information only once, and first, the first returned row is immediately fetched, outside of any loop. You’ll see how and why this works shortly.
tip
As a fraud-prevention technique, you could retrieve the billing address from the payment transaction and compare it to the shipping address, looking for suspicious differences.
ptg 9. Create the table:
echo '<table border="0" width="100%" cellspacing="2"
➥cellpadding="2">
<thead>
<tr>
<th align="center">Item</th>
<th align="right">Price Paid</th>
<th align="center">Quantity in Stock</th>
<th align="center">Quantity Ordered</th>
<th align="center">Ship?</th>
</tr>
</thead>
<tbody>';
The table lists the ordered items, along with the price paid, the quantity currently in stock, the quantity ordered, and when the item has shipped, if applicable.
10. Create a flag variable to track if the order has already shipped:
$shipped = true;
The administrator is going to be given the option of processing the pay- ment for this order only if it hasn’t already shipped. The assumption will be that it has, and later code will change this setting if that’s not the case.
11. Print each item:
do {
echo '<tr>
<th align="left">' . $row['item'] . '</thd>
<th align="right">' . $row['price_per'] . '</thd>
<th align="center">' . $row['stock'] . '</thd>
<th align="center">' . $row['quantity'] . '</thd>
<th align="center">' . $row['sd'] . '</td>
</tr>';
Because one row has already been fetched, the less common do…while loop will be used to navigate the remaining query results. This construct performs some actions first and checks the conditional last, thereby guar- anteeing that the code within the loop will be executed at least one time.
Within the loop, each value is displayed within a table row.
12. Update the shipping status:
if (!$row['sd']) $shipped = false;
ptg If $row['sd'] is NULL (for any item in the order), then the entire order has
not been shipped yet, and the flag variable should indicate such.
13. Complete the loop and the table:
} while ($row = mysqli_fetch_array($r));
echo '</tbody></table>';
After the contents of the loop are executed, the condition is checked. The specific condition is the fetching of another array from the query results. If another array can be found, the loop will be repeated again.
14. If the order hasn’t entirely shipped, create the submit button:
if (!$shipped) {
echo '<div class="field"><p class="error">Note that actual
➥payments will be collected once you click this button!</p>
➥<input type="submit" value="Ship This Order " class="button" />
➥</div>';
}
For orders that have completely shipped, no submit button will exist (Figure 11.14).
Figure 11.14 15. Complete the form:
echo '</fieldset>
</form>';
16. Complete the mysqli_num_rows( ) conditional:
} else { // No records returned!
echo '<h3>Error!</h3><p>This page has been accessed in error.
➥</p>';
ptg include ('./includes/footer.html');
exit( );
}
This else clause applies if no records were returned by the query.
17. Complete the page:
include ('./includes/footer.html');
?>
18. Save the file and test it in your Web browser.
At this point, clicking the submit button will have no effect, however.