mySQL query help

[BarneyJoe]

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?

 

[tauchai83]

try this..

 

 

$query_rsOrders = "SELECT *, date_format(OrderDate, '%d %M %Y') as formatted_date FROM Orders A LEFT JOIN Customers B WHERE A.OrderCustomerID = B.CustomerID ORDER BY OrderID ASC";

 

 

[BarneyJoe]

Would that go into my existing rsOrders query?

 

I've tried that with no joy (errors)

 

Parse error: syntax error, unexpected T_VARIABLE in /home/qdiizyfg/public_html/admin/orderdetails.php on line 8

 

But that already has :

 

SELECT *
FROM Orders
WHERE OrderID = colname

 

where colname is the OrderID on the previous page.

 

So that's pretty much where I get confused with it all, as I also want to add in a different WHERE clause to display the Customer details.