Order of Invoice *by custom new field

[sbbemn]

So I have added a new product field successfully which is "Room Number" - an inside field used for picking from the invoice.

This does not appear on the invoice at all, but I would like the invoice to sort by that custom field = products_room, then by products_model

BUT, here is the code with the altered code to sort by products_model, intead of the stock orders_products_id:

admin/includes/classes/order.php

$orders_products = $db->Execute("select *
from " . TABLE_ORDERS_PRODUCTS . "
where orders_id = '" . (int)$order_id . "'
order by products_model");

I changed this to order by products_room,products_model but got 1054 Unknown column 'products_room' in 'order clause'

What am I missing here to get this to work right?

Thanks!

[Ajeh]

You added the new field:
products_room

to, what I am assuming, the table:
products

Did you also add that field to the table:
orders_products

and customize the order class to add that to the table:
orders_products

when the Order is made?

[sbbemn]

Thanks,

Yes, I added the field to the products table with the following:

ALTER TABLE products ADD products_room varchar(32) NULL default NULL after products_model;


"Did you also add that field to the table:
orders_products"

No, do I use this sql patch then?

ALTER TABLE orders_products ADD products_room varchar(32) NULL default NULL after products_model;

"and customize the order class to add that to the table:
orders_products

when the Order is made?"

not sure I fully understand that one.

[Ajeh]

The new field is added to the products table ...

The code is trying to get information from the orders_products table ...

If you do not add that new field to the orders_products table and customize the code in the order class to add that information to the orders_products table when the Order is created, then it does not know the information as it is in another table ...

You could look up the information from the products table, but then, if the location changes from where it was when the Order was made, you have to decide which information would be "more correct" ... should you pull the information from the products table based on *Today* or know what the information was based on when the Order was made?

[sbbemn]

I see - good stuff.

So if we were to ever change the info from the product level after an order was made, it would be different information.

I would prefer orders to pull it's information from *today* and use current information. If we were to ever change models or room numbers, then invoices/admin orders could be presented differently, but that is o.k. with me unless there is some reason not to that I am not seeing.

How do i go about changing the code to force it to pull from the current product information table, not orders_products... this?

$products = $db->Execute("select *
from " . TABLE_PRODUCTS . "
where orders_id = '" . (int)$order_id . "'
order by products_room,products_model");

[Ajeh]

You would need to customize that SELECT statement to pull from both the orders_products table and the products table and then you should be able to change the ORDER by statement ...

[sbbemn]

I must admit I'm not up to par with the select statement to generate this code suggestion myself.

[Ajeh]

Just guessing off the top of my head what will work for you ... but try:

      $orders_products = $db->Execute("select o.orders_products_id, o.products_id, o.products_name, o.products_model,
                                              o.products_price, o.products_tax, o.products_quantity,
                                              o.final_price, o.onetime_charges,
                                              o.product_is_free
                                       from " . TABLE_ORDERS_PRODUCTS . " o, " . TABLE_PRODUCTS . " p
                                       where orders_id = '" . (int)$order_id . "'
                                       and o.products_id = p.products_id
                                       order by p.products_room, p.products_model");

[sbbemn]

Now that's a beautiful thing.

Worked like a charm.

Any reason why my original code was this, seems a bit stripped down.

// $orders_products = $db->Execute("select *
// from " . TABLE_ORDERS_PRODUCTS . "
// where orders_id = '" . (int)$order_id . "'
// order by orders_products_id");

[sbbemn]

Well, no matter... it works right?!

Thank you kindly for your help on this Ajeh!


Summary:

This code helped me get my invoices sort ordered by my custom field "Room Number" from the products table and then ordered by "Model" number that was from the orders_products table.

This helps picking the items by room, then model on the shelf.

Slick, absolutely slick.