You are viewing an old revision of this post, from October 26, 2015 @ 17:28:45. See below for differences between this version and the current revision.

How to fetch complete customer data in magento

I need to fetch whole customer data from mysql by query. I need to get data by entity_id. Please help   -------------------------------- Answer: Here is what you can do. Activate the sql query log. Edit /lib/Varien/Db/Adapter/Pdo/Mysql.php and set the values for $_debug and $_logAllQueriesto true. then create a custom script that just calls 
$customerId = 1;
Mage::getModel('customer/customer')->load($customerId);
exit;
Then you should see in var/debug/pdo_mysql.log the queries that are executed. You will see some 'noise' queries but the last ones should be what you need. Here is what I got: The first query retrieves the attributes, the second one the data: 
SELECT `main_table`.`attribute_id`, `main_table`.`entity_type_id`, `main_table`.`attribute_code`, `main_table`.`attribute_model`, `main_table`.`backend_model`, `main_table`.`backend_type`, `main_table`.`backend_table`, `main_table`.`frontend_model`, `main_table`.`frontend_input`, `main_table`.`frontend_label`, `main_table`.`frontend_class`, `main_table`.`source_model`, `main_table`.`is_required`, `main_table`.`is_user_defined`, `main_table`.`default_value`, `main_table`.`is_unique`, `main_table`.`note`, `additional_table`.`is_visible`, `additional_table`.`input_filter`, `additional_table`.`multiline_count`, `additional_table`.`validate_rules`, `additional_table`.`is_system`, `additional_table`.`sort_order`, `additional_table`.`data_model`, `scope_table`.`website_id` AS `scope_website_id`, `scope_table`.`is_visible` AS `scope_is_visible`, `scope_table`.`is_required` AS `scope_is_required`, `scope_table`.`default_value` AS `scope_default_value`, `scope_table`.`multiline_count` AS `scope_multiline_count` FROM `eav_attribute` AS `main_table`
 INNER JOIN `customer_eav_attribute` AS `additional_table` ON additional_table.attribute_id = main_table.attribute_id
 LEFT JOIN `customer_eav_attribute_website` AS `scope_table` ON scope_table.attribute_id = main_table.attribute_id AND scope_table.website_id = :scope_website_id WHERE (main_table.entity_type_id = :mt_entity_type_id)
BIND: array (
  ':mt_entity_type_id' => 1,
  ':scope_website_id' => 1,
)
If you remove the bindings you end up with this: 
SELECT `main_table`.`attribute_id`, `main_table`.`entity_type_id`, `main_table`.`attribute_code`, `main_table`.`attribute_model`, `main_table`.`backend_model`, `main_table`.`backend_type`, `main_table`.`backend_table`, `main_table`.`frontend_model`, `main_table`.`frontend_input`, `main_table`.`frontend_label`, `main_table`.`frontend_class`, `main_table`.`source_model`, `main_table`.`is_required`, `main_table`.`is_user_defined`, `main_table`.`default_value`, `main_table`.`is_unique`, `main_table`.`note`, `additional_table`.`is_visible`, `additional_table`.`input_filter`, `additional_table`.`multiline_count`, `additional_table`.`validate_rules`, `additional_table`.`is_system`, `additional_table`.`sort_order`, `additional_table`.`data_model`, `scope_table`.`website_id` AS `scope_website_id`, `scope_table`.`is_visible` AS `scope_is_visible`, `scope_table`.`is_required` AS `scope_is_required`, `scope_table`.`default_value` AS `scope_default_value`, `scope_table`.`multiline_count` AS `scope_multiline_count` FROM `eav_attribute` AS `main_table`
     INNER JOIN `customer_eav_attribute` AS `additional_table` ON additional_table.attribute_id = main_table.attribute_id
     LEFT JOIN `customer_eav_attribute_website` AS `scope_table` ON scope_table.attribute_id = main_table.attribute_id AND scope_table.website_id = 1 WHERE (main_table.entity_type_id = 1);
and the query to retrieve all data: 
SELECT `customer_entity_varchar`.* FROM `customer_entity_varchar` WHERE (entity_id ='1') 
    UNION ALL SELECT `customer_entity_int`.* FROM `customer_entity_int` WHERE (entity_id ='1') 
    UNION ALL SELECT `customer_entity_datetime`.* FROM `customer_entity_datetime` WHERE (entity_id ='1') 
    UNION ALL SELECT `customer_entity_text`.* FROM `customer_entity_text` WHERE (entity_id ='1')
replace entity_id = 1 withe your customer id. but there is a catch. You have to now to find a way to map the results from the first select, the one that retrieves the attributes with the one that retrieves the values.      

Revisions

  • October 26, 2015 @ 17:28:45 [Current Revision] by admin
  • October 26, 2015 @ 17:28:45 by admin

Revision Differences

There are no differences between the October 26, 2015 @ 17:28:45 revision and the current revision. (Maybe only post meta information was changed.)

No comments yet.

Leave a Reply