0

I need to export from Magento the orders´ details (SKU and Quantity) and the shipping address (name, street, phone, etc)

i more or less got it but, I can´t join the tables. I guess I must find a common atribute for all tables but don´t know which one is it.

Any idea?

That is the code so far:

SELECT ce.entity_id AS ID,adr.created_at as Fecha ,fn.value as Nombre,ln.value as Apellido,telephone.value as Telefono,ce.email AS EMAIL, ce.store_id AS Store_ID, 
-- concat(fn.value, ' ' , ln.value) AS Addressee, 
REPLACE(
SUBSTRING(
SUBSTRING_INDEX(str.value, '\n', 1),
CHAR_LENGTH(SUBSTRING_INDEX(str.value, '\n', 0)) + 1),
'\n', '') AS street1,

REPLACE(
SUBSTRING(
SUBSTRING_INDEX(str.value, '\n', 2),
CHAR_LENGTH(SUBSTRING_INDEX(str.value, '\n', 1)) + 1),
'\n', '') AS street2,
region.value AS Region,
city.value AS City,
postcode.value AS Postcode -- ,
-- IF(count(*)=1, IF(addrId.attribute_id=13, 'yes', 'no'), 'yes') AS defalut_billing,
-- IF(count(*)=1, IF(addrId.attribute_id=14, 'yes', 'no'), 'yes') AS defalut_shipping
FROM mage_customer_entity ce
LEFT JOIN mage_customer_entity_int addrId ON (addrId.entity_id = ce.entity_id )-- AND addrId.attribute_id IN (14)) 
LEFT JOIN mage_customer_address_entity adr ON (addrId.value = adr.entity_id) 
LEFT JOIN mage_customer_address_entity_varchar fn ON (fn.entity_id = adr.entity_id AND fn.attribute_id = 20)
LEFT JOIN mage_customer_address_entity_varchar ln ON (ln.entity_id = adr.entity_id AND ln.attribute_id = 22) 
LEFT JOIN mage_customer_address_entity_text str ON (str.entity_id = adr.entity_id AND str.attribute_id = 25) 
LEFT JOIN mage_customer_address_entity_varchar region ON (region.entity_id = adr.entity_id AND region.attribute_id = 28)
LEFT JOIN mage_customer_address_entity_varchar city ON (city.entity_id = adr.entity_id AND city.attribute_id = 26) 
LEFT JOIN mage_customer_address_entity_varchar telephone ON (telephone.entity_id = adr.entity_id AND telephone.attribute_id = 31) 
LEFT JOIN mage_customer_address_entity_varchar postcode ON (postcode.entity_id = adr.entity_id AND postcode.attribute_id = 30)
-- 'where 
-- cast(adr.created_at as date)= current_date()'
GROUP BY(addrId.value);

Thanks!

DCortes
  • 1
  • 2

1 Answers1

0

I recently getting some data from orders of Magento for analyze, so I might help you a little bit.

First of all, you can find more detail in sales_flat_order table in the database, this tables includes many information to join other tables, such as customer id, date and entity id(more useful in eva model).

Also there are other tables prefix start with: sale_flat_order_, such as sales_flat_order_item and sales_flat_order_address

I have run your SQL which only return the customer information. It seems you need more data to achieve your goal. But you need tell us what kind of output format looks like you want.

Wei Jing
  • 613
  • 5
  • 11
  • Hi, thanks for replying. This query only retrieves customer information, but I got other SQL that retrieves order information. The problem is how to join them. Using customer_id, I believe that there could be errors if a customer places to orders in different dates. – DCortes May 25 '15 at 11:16
  • you may want to combine two selects into one query, here is the [link](http://stackoverflow.com/a/6035918/3992524) might help. And for different date, you can set a period of date to select data you want. – Wei Jing May 26 '15 at 05:29