Would anyone happen to have an example of a SELECT query that returns the most recent order status of all orders?
Printable View
Would anyone happen to have an example of a SELECT query that returns the most recent order status of all orders?
Not sure where you are wanting this to run ... phpMyAdmin or in your Zen Cart Admin ...
This should work in phpMyAdmin ...
I added a few fields but you can add more if you need them ...Code:select o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.shipping_method,
o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name
from orders_status s, orders o
where o.orders_status = s.orders_status_id and s.language_id = '1'
order by o.orders_id DESC;
That works for me Ajeh. I'm trying to write code that validates if a certain order qualifies for a type of transaction for a module I'm trying to write. I can mess with it on my local database and see where it takes me.
Thanks.
Hi Perhaps you can help me too...
Can you tell me how I can have it so I can get the following:
If Order status = 11 (or orders_status_name = 'Shipped')
Then Show Customer name, Company Name and full address? Or perhaps even insert a "between date" (1/1/2010 to 1/1/2011) if possible?
Thanks in advance.
Start with the original SQL and add to the WHERE the orders_status = 11 and then start adding in the fields you want included:
In phpMyAdmin, if you browse the orders table and hit the SQL you will see a list of the field names on the right to help you do this ...Code:select o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.shipping_method,
o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name
from orders_status s, orders o
where o.orders_status = s.orders_status_id and s.language_id = '1'
and o.orders_status = 11
order by o.orders_id DESC;
Thank you Ajeh, I tried that before I poseted this, but I get the following error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 30' at line 7
Any Ideas?
Thanks.
I cannot reproduce an error ... it works for me just fine ...
Test and see what happens if you use the code:
and change the 11 to 3 ... just to see if it works ...Code:select o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.shipping_method,
o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name
from orders_status s, orders o
where o.orders_status = s.orders_status_id and s.language_id = '1'
and o.orders_status = 11
order by o.orders_id DESC;
If still a problem, browse the orders table and click on search ... and find the field orders_status = 11
Check that all fields listed exist in your database ...
Weird ... thanks for the update that this is now working for you ... :smile: