Selecting the most recent order status...

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

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

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:

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

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;

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:

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

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


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!

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