Selecting the most recent order status...

[retched]

Would anyone happen to have an example of a SELECT query that returns the most recent order status of all orders?

[Ajeh]

Not sure where you are wanting this to run ... phpMyAdmin or in your Zen Cart Admin ...

This should work in phpMyAdmin ...

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;

I added a few fields but you can add more if you need them ...

[retched]

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.

[AATECH]

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.

[Ajeh]

Start with the original SQL and add to the WHERE the orders_status = 11 and then start adding in the fields you want included:

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;

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 ...

[AATECH]

Start with the original SQL and add to the WHERE the orders_status = 11 and then start adding in the fields you want included:

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;

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 ...

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.

[Ajeh]

I cannot reproduce an error ... it works for me just fine ...

Test and see what happens if you use the 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;

and change the 11 to 3 ... just to see if it works ...

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 ...

[AATECH]

I cannot reproduce an error ... it works for me just fine ...

Test and see what happens if you use the 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;

and change the 11 to 3 ... just to see if it works ...

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 ...

lol... The problem was the space between = and 11 so...

When I changed this:
o.orders_status = 11
to this:
o.orders_status =11

it Worked.

Thanks Ajeh!

[Ajeh]

Weird ... thanks for the update that this is now working for you ...