Hello.
I was hoping I might be able to pick the brain of one of you geniuses. I am running Zen Cart 1.5.6c, I have added an extra box to the checkout page which allows a customer to add a PO number to their order. I added a columnat the end of the ORDERS_STATUS_HISTORY table called "po_number" to store the data in. This has all worked correctly, the data is being stored correctly in the database, and all the order details still correlate correctly etc.
The problem I have is with querying the information back for the "My Account" page. I really don't understand how the query works, with some guesswork I have managed to get the PO number to display in its own column on the account page, but it has screwed up how the rest of the information is displayed.
Here is the code I have used, slightly modified from the original ZC code, I have highlighted my three additions in red:
Code:
$orders_query = "SELECT o.orders_id, o.date_purchased, o.delivery_name,
o.delivery_country, o.billing_name, o.billing_country,
ot.text as order_total, s.orders_status_name, sh.po_number
FROM " . TABLE_ORDERS . " o, " . TABLE_ORDERS_TOTAL . " ot, " . TABLE_ORDERS_STATUS . " s, " . TABLE_ORDERS_STATUS_HISTORY . " sh
WHERE o.customers_id = :customersID
AND o.orders_id = ot.orders_id
AND ot.class = 'ot_total'
AND o.orders_status = s.orders_status_id
AND s.language_id = :languagesID
ORDER BY orders_id DESC LIMIT 3";
$orders_query = $db->bindVars($orders_query, ':customersID', $_SESSION['customer_id'], 'integer');
$orders_query = $db->bindVars($orders_query, ':languagesID', $_SESSION['languages_id'], 'integer');
$orders = $db->Execute($orders_query);
$ordersArray = array();
while (!$orders->EOF) {
if (zen_not_null($orders->fields['delivery_name'])) {
$order_name = $orders->fields['delivery_name'];
$order_country = $orders->fields['delivery_country'];
} else {
$order_name = $orders->fields['billing_name'];
$order_country = $orders->fields['billing_country'];
}
$ordersArray[] = array('orders_id'=>$orders->fields['orders_id'],
'date_purchased'=>$orders->fields['date_purchased'],
'order_name'=>$order_name,
'order_country'=>$order_country,
'orders_status_name'=>$orders->fields['orders_status_name'],
'po_number'=>$orders->fields['po_number'],
'order_total'=>$orders->fields['order_total']
);
$orders->MoveNext();
}
// This should be last line of the script:
$zco_notifier->notify('NOTIFY_HEADER_END_ACCOUNT');
And below is a screenshot of the result (with customer name redacted for privacy). Basically, the column for the PO number is now the only column with the correct data in it, all the other data is being pulled from the order with ID no.5, ignoring the correct data (which I assure is correctly stored in the database). Can somebody point me in the right direction for how to build this query?
Bookmarks