At the moment I have a page which displays a list of orders, with the following query :
<?php
mysql_select_db($database_connOriental, $connOriental);
$query_rsOrders = "SELECT *, date_format(OrderDate, '%d %M %Y') as formatted_date FROM Orders INNER JOIN Customers WHERE OrderCustomerID = CustomerID ORDER BY OrderID ASC";
$rsOrders = mysql_query($query_rsOrders, $connOriental) or die(mysql_error());
$row_rsOrders = mysql_fetch_assoc($rsOrders);
$totalRows_rsOrders = mysql_num_rows($rsOrders);
?>
Then a page that displays the Order Details, using the following :
<?php
$maxRows_rsOrderDetails = 10;
$pageNum_rsOrderDetails = 0;
if (isset($_GET['pageNum_rsOrderDetails'])) {
$pageNum_rsOrderDetails = $_GET['pageNum_rsOrderDetails'];
}
$startRow_rsOrderDetails = $pageNum_rsOrderDetails * $maxRows_rsOrderDetails;
$maxRows_rsOrderDetails = 10;;
$pageNum_rsOrderDetails = 0;
if (isset($_GET['pageNum_rsOrderDetails'])) {
$pageNum_rsOrderDetails = $_GET['pageNum_rsOrderDetails'];
}
$startRow_rsOrderDetails = $pageNum_rsOrderDetails * $maxRows_rsOrderDetails;
$colname_rsOrderDetails = "1";
if (isset($_GET['ID'])) {
$colname_rsOrderDetails = (get_magic_quotes_gpc()) ? $_GET['ID'] : addslashes($_GET['ID']);
}
mysql_select_db($database_connOriental, $connOriental);
$query_rsOrderDetails = sprintf("SELECT *, DetailPrice * DetailQuantity AS lineTotal FROM OrderDetails WHERE DetailOrderID = %s", $colname_rsOrderDetails);
$query_limit_rsOrderDetails = sprintf("%s LIMIT %d, %d", $query_rsOrderDetails, $startRow_rsOrderDetails, $maxRows_rsOrderDetails);
$rsOrderDetails = mysql_query($query_limit_rsOrderDetails, $connOriental) or die(mysql_error());
$row_rsOrderDetails = mysql_fetch_assoc($rsOrderDetails);
if (isset($_GET['totalRows_rsOrderDetails'])) {
$totalRows_rsOrderDetails = $_GET['totalRows_rsOrderDetails'];
} else {
$all_rsOrderDetails = mysql_query($query_rsOrderDetails);
$totalRows_rsOrderDetails = mysql_num_rows($all_rsOrderDetails);
}
$totalPages_rsOrderDetails = ceil($totalRows_rsOrderDetails/$maxRows_rsOrderDetails)-1;
$colname_rsOrders = "1";
if (isset($_GET['ID'])) {
$colname_rsOrders = (get_magic_quotes_gpc()) ? $_GET['ID'] : addslashes($_GET['ID']);
}
mysql_select_db($database_connOriental, $connOriental);
$query_rsOrders = sprintf("SELECT * FROM Orders WHERE OrderID = %s", $colname_rsOrders);
$rsOrders = mysql_query($query_rsOrders, $connOriental) or die(mysql_error());
$row_rsOrders = mysql_fetch_assoc($rsOrders);
$totalRows_rsOrders = mysql_num_rows($rsOrders);
?>
I'm trying to add in the customer details (name address etc) to the Order Details page as well, but can't seem to get it right, by trying to add in a left join in existing queries, or create a new one like linking on OrderCustomerID from my orders tabel and customerID from my customers table.
The tables are :
Orders
OrderID (int, PK)
OrderCustomerID (int)
OrderDate
etc
Customers
CustomerID (int, PK)
FirstName
LastName
etc
OrderDetails
DetailOrderID (int, PK)
DetailProductName
etc
Any ideas?
