Help needed with SQL query

[Pingfah]

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:

$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?

[web-project]

reason for it in your SQL statement as it's not link to your table:

 " . TABLE_ORDERS_STATUS_HISTORY . " sh

as you need to use WHERE statement to link the above table

[mc12345678]

reason for it in your SQL statement as it's not link to your table:

 " . TABLE_ORDERS_STATUS_HISTORY . " sh

as you need to use WHERE statement to link the above table

This thread actually was a duplicate of: https://www.zen-cart.com/showthread....with-SQL-query

Unfortunately that means others like yourself trying to help find out that a solution has already been provided.