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

How do I create my own SQL query to extract order info? 
 
IrishC
Sr. Member
 
Avatar
Total Posts:  89
Joined:  2008-01-29
 

OK, I’ve done enough ranting on other threads about how dissapointed I am with a lack of a few key features (i.e. good report exporting) but its time to stop complaining and do something about it.  I want to create a script which extracts the order information of orders with a status of “Proccessing” from the Magento database as an interim solution until what we need is in the admin panel.

The information I extract will be used to build a simple CSV file containing the order info, customer details, shipping address, sku codes, quantities etc.  I dont care if its integrated into the magento backend.  Just a simple standalone script for now will do - that sits on my server, connects to the database and with the help of a little simple PHP, spits out a simple but useable CSV. 

HELP NEEDED....

As the Magento database is so big, I need some help to identify the correct tables and values that will need to form the basis of an SQL query.  If anyone here, magento team especially could provide some guidance that would be great.  I can handle the PHP and most probably the SQL coding too, provided I get some pointers on where to start digging.

I have identified that the “sales_flat_order_item” table is a good place to start pulling information from - however I cant seem to find the field, (on this table or in an associatied table) which signifies the order status.  Obviously I only want to find orders yet to be shipped from the admin panel. 

I think a lot of stores would benefit from this, so as soon as its done I’l post the file so others can use this whilst we wait for the functionality to be in the box.

Thanks

C

 
Magento Community Magento Community
Magento Community
Magento Community
 
bradfordw
Jr. Member
 
Total Posts:  14
Joined:  2008-06-10
 

Boy, where to begin...your status will be stored in sales_order_entity_varchar this is where you can link the order_id column in sales_flat_order_item to the entity_id column of sales_order_entity_varchar.  The eav_entity_type_id you’re interested will be the one that keys up with ‘order_status_history’ (you can find out what your id is by running: select entity_type_id, entity_type_code, entity_model,entity_table from eav_entity_type) On my system it’s listed as id: 15.

Finally, in order to find the actual attribute field that lends you the appropriate attribute_id for your order’s status you’ll need to look at eav_attribute and look for an attribute that has an entity_type_id of your above-mentioned id for order_status_history and the attribute code of ‘status’.  In my case I can issue a query of select * from eav_attribute where entity_type_id = 15; and see the appropriate attributes.

Clear as mud?

 
Magento Community Magento Community
Magento Community
Magento Community
 
battarro
Jr. Member
 
Total Posts:  3
Joined:  2009-01-28
 

What about getting the “Paid” Status of an invoice?

Where can that be? ALso how can i tie an invoice to a order?
For example sales_order contains my order with entity_type of order, but i cant find where is the link between my order and invoice.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Jeremiah
Member
 
Avatar
Total Posts:  34
Joined:  2008-01-10
 

Were you ever able to come up with anything for this? I have a solution culled together from a bunch of sources, plus my own custom code, that might help you out, if you’re still looking for something.

 
Magento Community Magento Community
Magento Community
Magento Community
 
vscotth
Jr. Member
 
Total Posts:  4
Joined:  2012-08-24
 

@Jeremiah - I need to do something similar, generate/export an “open orders” report that lists all non-shipped orders.  Any suggestions or help you can provide would be most appreciated.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Jeremiah
Member
 
Avatar
Total Posts:  34
Joined:  2008-01-10
 

Oh man, this was a long time ago. I’ll see if I can dig it up and post some code…

 
Magento Community Magento Community
Magento Community
Magento Community
 
Jeremiah
Member
 
Avatar
Total Posts:  34
Joined:  2008-01-10
 

I’ve bundled the two relevant files I used for this little app in a zip.
You’ll have to go in and update your database info and make a password to actually access the page. Your best bet is to go thru the code (which isn’t great, I admit) and see what it’s doing. For what you’re looking for, you may have to update the query to pull up specific shipment data you’re looking for…

The query I’m using is this:

$myquery="SELECT * FROM sales_flat_order
    INNER JOIN sales_flat_invoice_grid
    ON sales_flat_order.entity_id=sales_flat_invoice_grid.order_id
    INNER JOIN sales_flat_order_address
    ON sales_flat_order.billing_address_id=sales_flat_order_address.entity_id
    INNER JOIN sales_flat_order_item
    ON sales_flat_order_item.order_id=sales_flat_invoice_grid.order_id
    WHERE (status = 'complete' OR status = 'pending') AND
    sales_flat_order.created_at <= '
$end' AND sales_flat_order.created_at >= '$start'
    ORDER BY 'increment_id' DESC"
;

Which doesn’t discriminate by shipped/non-shipped items. I think you’d just need to update this line:

WHERE (status 'complete' OR status 'pending') AND
It shouldn’t be too difficult to write a new inner join for that. Let me know if you need any help navigating the code.
File Attachments
magento_reports.zip  (File Size: 5KB - Downloads: 104)
 
Magento Community Magento Community
Magento Community
Magento Community
 
vscotth
Jr. Member
 
Total Posts:  4
Joined:  2012-08-24
 

Thanks, much appreciated!  I’ll give it a go, and let you know how it works.

 
Magento Community Magento Community
Magento Community
Magento Community
 
vscotth
Jr. Member
 
Total Posts:  4
Joined:  2012-08-24
 

Not sure why, but page submit returned this error page:

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Strict//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="<br />
<b>Fatal error</b>:  Using $this when not in object context in <b>/hermes/bosoraweb082/b1468/ipw.imtcorp/public_html/magento/reports.php</b> on line <b>2</b><br />

I’ll research, but in the meantime… any thoughts?
Thanks.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Jeremiah
Member
 
Avatar
Total Posts:  34
Joined:  2008-01-10
 

I think it’s the html header. Make this line

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="<?php echo $this->getLang() ?>” lang="<?php echo $this->getLang() ?>">

into this:

<html xmlns="http://www.w3.org/1999/xhtml">

It’s b/c I built the original file inside Magento, so that $this-> is a class reference to a piece of Magento code.

 
Magento Community Magento Community
Magento Community
Magento Community
 
vscotth
Jr. Member
 
Total Posts:  4
Joined:  2012-08-24
 

Thanks, Jeremiah—that did the trick (also removed one other class reference).  Works like a champ!  Thanks for the help - this saved major time futzing with the database.

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