Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Order status in database
 
IrishC
Sr. Member
 
Avatar
Total Posts:  89
Joined:  2008-01-29
 

Hi,

Trying to develop my own custom script to do something but running into a problem.  I know that the “sales_order” table holds the order information, but I cant seem to determine how or where the order status is set i..e Pending, Processing, Completed.

I only want to query for “Processing” Orders.  Does anyone know which table in the database this value can be pulled from?

Thanks

C

 
Magento Community Magento Community
Magento Community
Magento Community
 
IrishC
Sr. Member
 
Avatar
Total Posts:  89
Joined:  2008-01-29
 

Has anyone any idea about this?

The script is basically complete now and working, but it just grabs ALL the orders rather than the ones which have yet to be shipped. 

IN IMAGINARY PSUEDO CODE...

SELECT FROM sales_order WHERE order_status 'processing'

Can any of the Magento team please tell me where I check the order status?  Would be very much appreciated.

C

 
Magento Community Magento Community
Magento Community
Magento Community
 
IrishC
Sr. Member
 
Avatar
Total Posts:  89
Joined:  2008-01-29
 

Not sure if this the actual solution or a silly way to get the same answer, but....

WHERE sales_flat_order_item.qty_shipped sales_flat_order_item.qty_ordered

Seems to only return to the items that haven’t been shipped.  Never thought to have to do a comparison - assumed a state would be set in a column.

Still like to know from anyone if this is right way to achieve this.

 
Magento Community Magento Community
Magento Community
Magento Community
 
mike222
Member
 
Total Posts:  41
Joined:  2008-04-07
Austria
 

check table eav_entity_type for row with entity_model = ‘sales_order_status_history’
In my case the id of the row (entity_type_id) is 15

filter the table sales_order_entity for entity_type_id = 15
The column parent_id corresponds to the id of the order (entity_id of table sales_order)

go to table sales_order_entity_varchar and find the row with the entity_id which corresponds to the column entity_id of step 2

voila, the column value describes the stati for your order.

Hope this helps a bit.

 
Magento Community Magento Community
Magento Community
Magento Community
 
MrShim
Jr. Member
 
Total Posts:  7
Joined:  2009-01-02
 

Ran across a similar issue doing an order export to CSV for orders with a current status of ‘processing’. Based on Mike222’s info above… Ended up with this:

SELECT sales_order_entity.* 
FROM sales_order_entity 
INNER JOIN 
(SELECT max(entity_id) AS entity_id FROM sales_order_entity GROUP by parent_identity_ids 
ON sales_order_entity
.entity_id entity_ids.entity_id 
INNER JOIN sales_order_entity_varchar 
ON sales_order_entity
.entity_id=sales_order_entity_varchar.entity_id 
WHERE sales_order_entity
.entity_type_id '15' 
AND sales_order_entity_varchar.value 'processing'

From here I’ll use the parent_id to query mg_sales_flat_order_item and other tables for the order details based on BlastedThing’s order exporter script. The status selection was similar to your example above with shipped vs ordered quantities but what I needed it to do was status specific:
http://www.blastedthing.com/magento/admin/mag-export-list-of-magento-orders-in-csv-format/

I’m sure there’s probably a cleaner way to make that select statement… but its working for me so far. I hope that helps someone.

 
Magento Community Magento Community
Magento Community
Magento Community
 
subesh
Sr. Member
 
Avatar
Total Posts:  145
Joined:  2009-01-08
 

$collection Mage::getModel('sales/order')->getCollection()
                        ->
addAttributeToSelect('*')
                         ->
addAttributeToFilter(array(array('attribute'=>'status''Processing')))
                        ->
joinAttribute('status''order/status''entity_id'null'left');

This is the Magento Way!

 
Magento Community Magento Community
Magento Community
Magento Community
 
rune00
Member
 
Total Posts:  39
Joined:  2008-08-03
 

I modified this to get order_id and customer e-mail:

SELECT sales_order_entity.parent_id, sales_order_varchar.value
FROM sales_order_entity
INNER JOIN (SELECT max(entity_id) AS entity_id FROM sales_order_entity GROUP by parent_id) entity_ids
ON sales_order_entity.entity_id = entity_ids.entity_id
INNER JOIN sales_order_entity_varchar
ON sales_order_entity.entity_id=sales_order_entity_varchar.entity_id INNER JOIN sales_order_varchar ON sales_order_entity.parent_id=sales_order_varchar.entity_id
WHERE sales_order_entity.entity_type_id = ‘15’
AND sales_order_entity_varchar.value = ‘processing’ AND sales_order_varchar.attribute_id = ‘195’

Would be great if we could modify it even further to return customer e-mails from orders containing a specific product_id.

 
Magento Community Magento Community
Magento Community
Magento Community
 
beatboxio
Jr. Member
 
Total Posts:  2
Joined:  2009-01-03
 

This post saved my night.  Thanks everyone for sharing your code.

I used a variation on the supplied query to just grab status from order ID.  I thought someone else might want it:

$statusquery  "
                        SELECT sales_order_entity_varchar.value as status 
                        FROM sales_order_entity 
                        INNER JOIN (SELECT max(entity_id) AS entity_id FROM sales_order_entity GROUP by parent_id) entity_ids 
                        ON sales_order_entity.entity_id = entity_ids.entity_id 
                        INNER JOIN sales_order_entity_varchar 
                        ON sales_order_entity.entity_id=sales_order_entity_varchar.entity_id 
                        WHERE sales_order_entity.entity_type_id = '15' 
                        AND sales_order_entity.parent_id = 
$orderid 
                        
                "
;
                
$statusresult mysql_query($statusquery);        // Run the query
        
$item = @mysql_fetch_array($statusresult); // Place the item in an array for processing
                
$orderstatus $item["status"];
 
Magento Community Magento Community
Magento Community
Magento Community
 
MagePsycho
Moderator
 
Avatar
Total Posts:  1702
Joined:  2009-06-23
 

Just using Magento Utilities:

$statusresult Mage::getSingleton('core/resourc\')->getConnection()->fetchRow($sql);
//print_r($statusresult);
$orderstatus $statusresult ['status'];
 
Magento Community Magento Community
Magento Community
Magento Community
 
plu_australia
Sr. Member
 
Total Posts:  132
Joined:  2010-04-20
 

Running this query returns :
#1146 - Table \’mydB_mage16.sales_order_entity\’ doesn\’t exist

We have a problem where add orders for $0 via Admin to facilitate returns and promotional giveaways get stuck and beleive the only option is to change the order status in the dB direct from \’processing\’ to \’complete\’

Any help would be much appreciated.

Thanks.

MP- by the way Magento Utilities is what exactly?

 
Magento Community Magento Community
Magento Community
Magento Community
 
maheshmagento
Jr. Member
 
Total Posts:  2
Joined:  2011-08-21
 

because that table is not available in 1.4.x version

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top