Add a new Admin order search criteria

I'm trying to add a new search box to the order Admin screen that can search for orders by a order referrence number that my cart generates when an order is placed.

My Zen Cart generates a random 8 digit order reference number and writes it to table in the database, which is structured like:

TABLE NAME: order_reference_numbers
COLUMES:
refference_order_number (a randaom 8 digit number)
order_id (original order id)

Now I'm trying to figure out the code that needs to be modified in the **orders.php **found in the /admin directory, before I go crazy has anyone done this before and have an example of some code that I can reference?

I understand the Logic of what it has to do, I'm just not sure how to code it! It has to seach this custom table for the reference number then see what the original order id is then simply display that order like the current search by order id works.

Thanks!

Try this:
edit admin/orders.php by replacing this code (lines 35-45 in original v1.3.8 file)```
if (isset($_GET['oID'])) {
$oID = zen_db_prepare_input($_GET['oID']);

$orders = $db->Execute("select orders_id from " . TABLE_ORDERS . "
                        where orders_id = '" . (int)$oID . "'");
$order_exists = true;
if ($orders->RecordCount() <= 0) {
  $order_exists = false;
  if ($action != '') $messageStack->add(sprintf(ERROR_ORDER_DOES_NOT_EXIST, $oID), 'error');
}

}
with this
if (isset($_GET['oID'])) {
$oID = zen_db_prepare_input($_GET['oID']);

$orders = $db->Execute("select orders_id from " . TABLE_ORDERS . "
                        where orders_id = '" . (int)$oID . "'");
$order_exists = true;
if ($orders->RecordCount() <= 0) {
  $reference = $db->Execute("select order_id from " . TABLE_ORDERS_REFERENCE_NUMBER . "
                             where reference_order_number  = '" . (int)$oID . "' LIMIT 1");

  if ($reference->RecordCount() <= 0) {
    $oID = $reference->fields['reference_order_number'];
  } else {
        $order_exists = false;
        if ($action != '') $messageStack->add(sprintf(ERROR_ORDER_DOES_NOT_EXIST, $oID), 'error');
  }
}

}

this code assumes you have defined your new database table name with the constant TABLE_ORDERS_REFERENCE_NUMBER - replace with your constant as needed, and make sure the field names match what you have in your orders reference table. 

Now you should be able to enter either the zen cart order id or your own order reference number into the Order ID search box.

Thank you for your help, I added the code and it did not work?

couple things to mention...

refference_order_number was actually ref_num
order_id was actually ** orders_id**

I changed both in the code, it did not work...seems to not even try to search...when you hit enter nothing happens and what ever was entered in the search field is still there after hitting enter.

I also did not have that table defined, so I checked on how to do that and if you can tell me if I did that right:

I define it by adding a file to directory: /admin/includes/extra_datafiles

FILE NAME: orders_reference_number.php

INSIDE THAT FILE:

Below is the modified code that I tested:

if (isset($_GET['oID'])) {
$oID = zen_db_prepare_input($_GET['oID']);

$orders = $db->Execute("select orders_id from " . TABLE_ORDERS . "
                        where orders_id = '" . (int)$oID . "'");
$order_exists = true;
if ($orders->RecordCount() <= 0) {
  $reference = $db->Execute("select orders_id from " . TABLE_ORDERS_REFERENCE_NUMBER . "
                             where ref_num  = '" . (int)$oID . "' LIMIT 1");

  if ($reference->RecordCount() <= 0) {
    $oID = $reference->fields['ref_num'];
  } else {
        $order_exists = false;
        if ($action != '') $messageStack->add(sprintf(ERROR_ORDER_DOES_NOT_EXIST, $oID), 'error');
  }
}

}

Sorry, it was an error in my code.
Try this:

   if (isset($_GET['oID'])) {
    $oID = zen_db_prepare_input($_GET['oID']);

    $orders = $db->Execute("select orders_id from " . TABLE_ORDERS . "
                            where orders_id = '" . (int)$oID . "'");
    $order_exists = true;
    if ($orders->RecordCount() <= 0) {
	  $reference = $db->Execute("select orders_id from " . TABLE_ORDERS_REFERENCE_NUMBER . "
                                 where ref_num = '" . (int)$oID . "' LIMIT 1");
	
	  if ($reference->RecordCount() <= 0) {
	    $oID = $reference->fields['orders_id '];
	  } else {
            $order_exists = false;
            if ($action != '') $messageStack->add(sprintf(ERROR_ORDER_DOES_NOT_EXIST, $oID), 'error');
	  }
    }
  }

Nope...does not do anything when you hit enter?

give me a few minutes - I'll set up a similar table on my test site and see what happens

ok, after a little test I found my error.
This should work:

  if (isset($_GET['oID'])) {
    $oID = zen_db_prepare_input($_GET['oID']);

    $orders = $db->Execute("select orders_id from " . TABLE_ORDERS . "
                            where orders_id = '" . (int)$oID . "'");
    $order_exists = true;
    if ($orders->RecordCount() <= 0) {
	  $reference = $db->Execute("select orders_id from " . TABLE_ORDERS_REFERENCE_NUMBER . "
                                 where ref_num  = '" . (int)$oID . "' LIMIT 1");
	
	  if ($reference->RecordCount() > 0) {
	    $oID = $reference->fields['orders_id'];
	  } else {
        $order_exists = false;
        if ($action != '') $messageStack->add(sprintf(ERROR_ORDER_DOES_NOT_EXIST, $oID), 'error');
	  }
    }
  }

nice work thanks...worked perfect! I appreciate the help!!