I want to get rows for all customer details from magento tables.
Can anyone give me the query?
Tables are:
- customer_entity
- eav_attribute
- customer_entity_varchar
I want to get rows for all customer details from magento tables.
Can anyone give me the query?
Tables are:
It's possible to extract some data from Magento MySQL DB, but before executing a queries below notice following:
1) Magento is built to be configurable, so that you must not fetch data from MySQL, but you must use Magento Customer module (models, resource models) and Magento configuration data to retrieve customer data. Any straight query is not guaranteed to be compatible with different Magento versions and even installations of same version.
2) Magento EAV structure does not allow you to fetch all customer data in a nice form within one query, because table names are matched to attributes dynamically.
3) You cannot extract ALL customer data just by one query or even multiple queries. Because many informational objects are created within models and only model has the logic to gather all data within one customer object. I talk about shipping/billing addresses, store credit, reward points (for EE version) and so on.
However just to fetch all customers' varchar attributes you can use following code (not guaranteed to work due to mentioned above in 1):
SELECT ce.*, ea.attribute_code, cev.value
FROM customer_entity AS ce
LEFT JOIN eav_attribute AS ea ON ce.entity_type_id = ea.entity_type_id AND ea.backend_type = 'varchar'
LEFT JOIN customer_entity_varchar AS cev ON ce.entity_id = cev.entity_id AND ea.attribute_id = cev.attribute_id
Also it's possible to use such a query to extract all attributes for a customer
SELECT ce.*, ea.attribute_code,
CASE ea.backend_type
WHEN 'varchar' THEN ce_varchar.value
WHEN 'int' THEN ce_int.value
WHEN 'text' THEN ce_text.value
WHEN 'decimal' THEN ce_decimal.value
WHEN 'datetime' THEN ce_datetime.value
ELSE NULL
END AS value
FROM customer_entity AS ce
LEFT JOIN eav_attribute AS ea ON ce.entity_type_id = ea.entity_type_id
LEFT JOIN customer_entity_varchar AS ce_varchar ON ce.entity_id = ce_varchar.entity_id AND ea.attribute_id = ce_varchar.attribute_id AND ea.backend_type = 'varchar'
LEFT JOIN customer_entity_int AS ce_int ON ce.entity_id = ce_int.entity_id AND ea.attribute_id = ce_int.attribute_id AND ea.backend_type = 'int'
LEFT JOIN customer_entity_text AS ce_text ON ce.entity_id = ce_text.entity_id AND ea.attribute_id = ce_text.attribute_id AND ea.backend_type = 'text'
LEFT JOIN customer_entity_decimal AS ce_decimal ON ce.entity_id = ce_decimal.entity_id AND ea.attribute_id = ce_decimal.attribute_id AND ea.backend_type = 'decimal'
LEFT JOIN customer_entity_datetime AS ce_datetime ON ce.entity_id = ce_datetime.entity_id AND ea.attribute_id = ce_datetime.attribute_id AND ea.backend_type = 'datetime'