orders.php: Help with sorting status pulldown menu

[Guest]

I'm trying to sort the order status display using orders_status_name by modifying the SQL call from (line ~20, orders.php):

 

SELECT orders_status_id, orders_status_name FROM " . TABLE_ORDERS_STATUS . " WHERE language_id = '" . $languages_id . "

to

SELECT orders_status_id, orders_status_name FROM " . TABLE_ORDERS_STATUS . " WHERE language_id = '" . $languages_id . " ORDER BY 2 ASC

This SQL statement produces the correct output when tested (from the mysql prompt) but the pull down menu display in orders.php (action == edit) is not sorted. We have adopted a standard for status naming convention:

 

1.a. Pending approval

1.b. Order declined

2.a. blah

2.b. blah

...

I couldn't see any code manipulating the order or display after it was assigned the array $orders_statuses. I need help!

[zzfritz]

If I understand correctly, that you want to order the drop-down list, it is built in orders.php 1.106 by this fragment of line 332:

tep_draw_pull_down_menu('status', tep_array_merge(array(array('id' => '', 'text' => TEXT_ALL_ORDERS)), $orders_statuses), '', 'onChange="this.form.submit();"')

 

So, I believe you want to change $orders_statuses to sort($orders_statuses) there.

[Guest]

sort() does not a return array so you cannot use it that way. However, I tried adding this:

asort($orders_statuses);

reset($orders_statuses);

 

at the top of the file just after:

  while ($orders_status = tep_db_fetch_array($orders_status_query)) {

   $orders_statuses[] = array('id' => $orders_status['orders_status_id'],

                              'text' => $orders_status['orders_status_name']);

   $orders_status_array[$orders_status['orders_status_id']] = $orders_status['orders_status_name'];

 }

but it still doesn't work. Any more ideas? :roll:

[zzfritz]

Here is what I use in my Monthly Sales & Tax Report contribution, just before the statement that builds the dropdown from orders_statuses:

// get list of orders_status names for dropdown selection

     $orders_statuses = array();

     $orders_status_array = array();

     $orders_status_query = tep_db_query("select orders_status_id, orders_status_name from " . TABLE_ORDERS_STATUS . " where language_id = '" . $languages_id . "'");

     while ($orders_status = tep_db_fetch_array($orders_status_query)) {

       $orders_statuses[] = array('id' => $orders_status['orders_status_id'],

           'text' => $orders_status['orders_status_name']);

  	 $orders_status_array[$orders_status['orders_status_id']] = $orders_status['orders_status_name'];

     };

 

Just add an ORDER BY clause to the sql query.

[Guest]

Hmm... strange :? If you look at my first post, I already used ORDER BY clause but it didn't work the first time so I added a new field in the orders_status table called sort_order which gives me more control over the display order.

[Christian Lescuyer]

I'm trying to sort the order status display using orders_status_name by modifying the SQL call from (line ~20, orders.php)

This works for me:

  $orders_status_query = tep_db_query("select orders_status_id, orders_status_name from " . TABLE_ORDERS_STATUS . " where language_id = '" . $languages_id . "' ORDER BY orders_status_name");

Please note that the default entry for a list box is not necessarily the first one. Thus after this modification, the default entry is still Pending, but the order is correct.

 

Considering the order status is mailed to the client, I wouldn't use a code like you do. I would just add an orders_status_sort field in the database and ORDER BY that.

[Guest]

That's what I did -- I created a new field in the orders_status table called sort_order which can be defined when you create or edit an order status then ran the SQL query and did the ORDER BY using the sort_order field.

[zzfritz]

Sorry for my last post, not paying attention and distracted.

 

But I have verified that adding "ORDER BY orders_status_name" to the query really does work as Christian says. For me, it changed the dropbox list so the status values are in alphabetic order after the merged All Orders choice.