[ACCEPTED]-getting customer info from magento table structure-magento

Accepted answer
Score: 26

It's possible to extract some data from 25 Magento MySQL DB, but before executing a 24 queries below notice following:

1) Magento 23 is built to be configurable, so that you 22 must not fetch data from MySQL, but you 21 must use Magento Customer module (models, resource 20 models) and Magento configuration data to 19 retrieve customer data. Any straight query 18 is not guaranteed to be compatible with 17 different Magento versions and even installations 16 of same version.

2) Magento EAV structure 15 does not allow you to fetch all customer 14 data in a nice form within one query, because 13 table names are matched to attributes dynamically.

3) You 12 cannot extract ALL customer data just by 11 one query or even multiple queries. Because 10 many informational objects are created within 9 models and only model has the logic to gather 8 all data within one customer object. I talk 7 about shipping/billing addresses, store 6 credit, reward points (for EE version) and 5 so on.

However just to fetch all customers' varchar 4 attributes you can use following code (not 3 guaranteed to work due to mentioned above 2 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 1 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'
Score: 9

Just to complement @Stefano´s response, i've 4 made a big query, just to extract some other 3 important details, like address and phone. Some 2 of these details will only make sense on 1 my database, but can help someone:

SELECT c.entity_id,c.email, 
            (
            SELECT fn.value
            FROM customer_entity_varchar fn
            WHERE c.entity_id = fn.entity_id AND 
             fn.attribute_id = 12) AS password_hash, 
             (
            SELECT fn.value
            FROM customer_entity_varchar fn
            WHERE c.entity_id = fn.entity_id AND
             fn.attribute_id = 5) AS name, 
             (
            SELECT fn.value
            FROM customer_entity_varchar fn
            WHERE c.entity_id = fn.entity_id AND 
             fn.attribute_id = 7) AS lastname,
             (
            SELECT fn.value
            FROM customer_entity_varchar fn
            WHERE c.entity_id = fn.entity_id AND 
             fn.attribute_id = 150) AS cpfcnpj, 
             (
            SELECT fn.value
            FROM customer_address_entity_varchar fn
            WHERE ca.entity_id = fn.entity_id AND 
             fn.attribute_id = 149) AS cpfcnpj2,
             (
            SELECT fn.value
            FROM customer_address_entity_varchar fn
            WHERE ca.entity_id = fn.entity_id AND 
             fn.attribute_id = 145) AS rg,
             (
            SELECT fn.value
            FROM customer_address_entity_varchar fn
            WHERE ca.entity_id = fn.entity_id AND 
             fn.attribute_id = 151) AS phone1,
             (
            SELECT fn.value
            FROM customer_address_entity_varchar fn
            WHERE ca.entity_id = fn.entity_id AND 
             fn.attribute_id = 31) AS phone2,
             (
            SELECT fn.value
            FROM customer_address_entity_varchar fn
            WHERE ca.entity_id = fn.entity_id AND 
             fn.attribute_id = 27) AS country,
             (
            SELECT fn.value
            FROM customer_address_entity_varchar fn
            WHERE ca.entity_id = fn.entity_id AND 
             fn.attribute_id = 28) AS state,
             (
            SELECT fn.value
            FROM customer_address_entity_varchar fn
            WHERE ca.entity_id = fn.entity_id AND 
             fn.attribute_id = 26) AS city,                 
             cat.value AS address,
             (
            SELECT fn.value
            FROM customer_address_entity_varchar fn
            WHERE ca.entity_id = fn.entity_id AND 
             fn.attribute_id = 30) AS cep
            FROM customer_entity AS c
            LEFT JOIN customer_address_entity AS ca ON c.entity_id = ca.parent_id
            LEFT JOIN customer_address_entity_text AS cat ON cat.entity_id = ca.entity_id                

            GROUP BY entity_id
Score: 8

This is a little query to extract the customer 1 details from magento tables:

select c.*, 
  (select fn.value from customer_entity_varchar fn where c.entity_id = fn.entity_id and
    fn.attribute_id = 5) as Name, 
  (select fn.value from customer_entity_varchar fn where c.entity_id = fn.entity_id and 
    fn.attribute_id = 7) as Lastname
from customer_entity c
Score: 3

I read what's above and I feel much better 55 knowing that some people out there are trying 54 to figure out the issue as mine. Well, As 53 you may know, you can't find the whole information 52 about your clients in magento back-office. I 51 wanted to have a very big table where I 50 can see all of my clients (those who have 49 an account= registred) ( and those who just 48 registred to news letters newsletter_subscribers)

Since 47 I don't really know how magento provides 46 those information, I wrote som SQL code 45 to extract data.

As It is said above you 44 can't have all the information about clients 43 in one query. Actually, you will need as 42 much queries as the clients Attributes. If 41 the entity client has 45 attributes (name, lastname, middlename, email 40 .....) then you will need 45 queries to 39 extract them... My question was HOW ???

The 38 answer is VIEWS !! Actually, I wrote Views 37 to extract each attribute information. That 36 means That I wrote 45 veiws, each view selects 35 an attribute

I don't know How it is done 34 in your database, but, You may know that 33 Each entity has a entity_type_id. In my 32 case, I'm interested in the customer_entity 31 and the customer_address_entity. the customer_entity 30 has a entity_type_id = 1 the customer_address_entity 29 has an entity_type_id = 2

Each entity has 28 attributes. So, if you want to see what 27 are the attributes available for the customer 26 entity run the query :

Select attribute_code, attribute_id, backend_type from eav_attribute where entity_type_id =1 Same thing for address 25 entity by replacing the 1 by 2.

The column 24 attributes_id is very important because 23 this will enable you to find your attributes 22 by id the tables : entity_customer_vachar, entity_customer_int, entity_customer_text, entity_customer_datetime You'll 21 maybe find out the the firstname attribute_id 20 = 5 in this table as Joseph Mastey wrote 19 in he's query...

The information are dispatched 18 by type in the tables above. When you run 17 the query above, you will see that each 16 attribute has a backendtype. that means 15 that the informations you are looking for 14 is one of the tables suffixed by its type. For 13 example, if you are looking for firstname 12 which type is varchar, then you'll find 11 it in customer_entity_varchar and so on.

If 10 you are still reading, that may mean you 9 understand what I'm talking about. Other 8 wise you need to look at the EAV model And 7 the magento 1.3.2.4 database schema at http://inchoo.net/wp-content/uploads/2010/09/MAGENTO_v1.3.2.4-Database_Diagram.pdf

I 6 can't post the whole solution because It's 5 not ready yet, I'll put a zip file if that 4 can help.

Meanwhile, If someone knows how 3 the use magento objects wiht a php file 2 to get to the data without editing the magento 1 Core, that would be great! Thanks

Score: 2

Are you looking for actual SQL for this 15 or some Magento code? The actual SQL is 14 going to get messy, and probably won't ever 13 look the way you want it to.

Since there's 12 no "pivot" in MySQL, you can basically ignore 11 eav_attribute in the actual query. You'll be looking 10 at it to find the attribute IDs you want. Here's 9 a trivial example of how to select this 8 data:

select c.*, fn.value firstname
    from customer_entity c
    join customer_entity_varchar fn
        on c.entity_id = fn.entity_id and fn.attribute_id = 5
;

Keep adding those join clauses for 7 every attribute, and you'll have your query. Basically, EAV 6 is not optimized for this kind of access.

If 5 you give a little more detail about what 4 you are trying to do (or whether PHP code 3 in Magento will work for your purposes), we 2 may be able to help you further.

Hope that 1 helps!

Thanks, Joe

Score: 2

I was specifically attempting to export 17 customers mobile phone numbers from my Magento 16 store.

Firstly you can do this using Data 15 profiles as it extends to the Customer data 14 set.

In my case the data profiles function 13 was not working for some reason and as I 12 did not have time to work out why I went 11 directly to MySQL to get at my data.

The 10 following MySQL query will enable you to 9 extract customers with UK mobile phone numbers. Note: you 8 may need to work out which attribute_id 7 the data you need is and update in the query 6 accordingly.

attribute_id value
26 Country
19 First_Name
21 Surname
31 Telephone

QUERY:-

SELECT 
`firstname`.`value` as `First_Name`, 
`surname`.`value` as `Surname`, 
`telephone`.`value` as `Telephone`,
`customer_entity`.`created_at`,
`customer_entity`.`updated_at` 
FROM 
`customer_address_entity_varchar` as `country` 
INNER JOIN 
`customer_address_entity_varchar` as  `firstname` USING (`entity_id`) 
INNER JOIN 
`customer_address_entity_varchar` as  `surname` USING (`entity_id`) 
INNER JOIN 
`customer_address_entity_varchar` as  `telephone` USING (`entity_id`) 
INNER JOIN 
`customer_entity` USING (`entity_id`) 
WHERE 
`country`.`attribute_id` = 26 && 
`country`.`value`="GB" && 
`firstname`.`attribute_id` = 19  && 
`surname`.`attribute_id` = 21  && 
`telephone`.`attribute_id` = 31  && 
`telephone`.`value` LIKE "07%"  
GROUP BY `telephone`.`value` 
limit 0,10;

Note the limit and the 5 where statement limiting the result to specific 4 data, the group stops duplicate numbers 3 as the actual data I want is the phone number. You 2 could add INTO OUTFILE to export result 1 set to CSV.

Hope this helps someone...

Score: 1
SELECT customer_address_entity_varchar.value, customer_entity.entity_id
FROM customer_address_entity_varchar

LEFT JOIN customer_address_entity 
ON customer_address_entity_varchar.entity_id = customer_address_entity.entity_id

LEFT JOIN customer_entity 
ON customer_address_entity.parent_id = customer_entity.entity_id

WHERE attribute_id =31

0

Score: 1

Another sample sql code for getting attribute 2 values. I used only int and varchar attributes, you 1 can also add others.

select c.entity_id, c.email, 
    a.attribute_id, a.attribute_code, a.frontend_label, a.backend_type, a.frontend_input, 
    case a.backend_type 
        when 'int' then coalesce(o.value, cei.value)
        when 'varchar' then cev.value
    end as value
from customer_entity c
left join eav_attribute a on a.entity_type_id = c.entity_type_id
left join customer_entity_int cei on     a.backend_type = 'int'     and cei.attribute_id = a.attribute_id and cei.entity_id = c.entity_id and cei.entity_type_id = c.entity_type_id
left join customer_entity_varchar cev on a.backend_type = 'varchar' and cev.attribute_id = a.attribute_id and cev.entity_id = c.entity_id and cev.entity_type_id = c.entity_type_id
left join eav_attribute_option_value o on a.backend_type = 'int' and a.frontend_input = 'select' /* and o.store_id = c.store_id */ and o.option_id = cei.value
-- where c.entity_id = 17651
having value is not null
order by c.entity_id, a.attribute_code;
Score: 0

I modified the great answer of Andrey Tserkus. And 2 added a new WHERE clause. Now you just need to replace the product SKU and you will get all customer recordsets, which bought the product. Works 1 fine for me in Magento 1.5.1

/* Just replace the value for i.sku in line 32, with the corresponding sku of the product you want to get the buyers from*/

SELECT
    `e`.*,
    `at_prefix`.`value` AS `prefix`,
    `at_firstname`.`value` AS `firstname`,
    `at_middlename`.`value` AS `middlename`,
    `at_lastname`.`value` AS `lastname`,
    `at_suffix`.`value` AS `suffix`,
    CONCAT(IF(at_prefix.value IS NOT NULL AND at_prefix.value != '',
        CONCAT(LTRIM(RTRIM(at_prefix.value)), ' '),
        ''),
        LTRIM(RTRIM(at_firstname.value)), ' ',
        IF(at_middlename.value IS NOT NULL AND at_middlename.value != '',
        CONCAT(LTRIM(RTRIM(at_middlename.value)), ' '),
        ''),
        LTRIM(RTRIM(at_lastname.value)),
        IF(at_suffix.value IS NOT NULL AND at_suffix.value != '',
        CONCAT(' ', LTRIM(RTRIM(at_suffix.value))),
        '')
    ) AS `name`
FROM `customer_entity` AS `e`
    LEFT JOIN `customer_entity_varchar` AS `at_prefix` ON (`at_prefix`.`entity_id` = `e`.`entity_id`) AND (`at_prefix`.`attribute_id` = '4')
    LEFT JOIN `customer_entity_varchar` AS `at_firstname` ON (`at_firstname`.`entity_id` = `e`.`entity_id`) AND (`at_firstname`.`attribute_id` = '5')
    LEFT JOIN `customer_entity_varchar` AS `at_middlename` ON (`at_middlename`.`entity_id` = `e`.`entity_id`) AND (`at_middlename`.`attribute_id` = '6')
    LEFT JOIN `customer_entity_varchar` AS `at_lastname` ON (`at_lastname`.`entity_id` = `e`.`entity_id`) AND (`at_lastname`.`attribute_id` = '7')
    LEFT JOIN `customer_entity_varchar` AS `at_suffix` ON (`at_suffix`.`entity_id` = `e`.`entity_id`) AND (`at_suffix`.`attribute_id` = '8')
WHERE (`e`.`entity_type_id` = '1') 
AND `e`.`entity_id` IN ( 
                SELECT DISTINCT o.customer_id FROM sales_flat_order_item i
                INNER JOIN sales_flat_order o ON o.entity_id = i.order_id
                WHERE o.customer_id IS NOT NULL
                AND i.sku = '10-10-10101-4'
            )
LIMIT 1000
Score: 0

I believe this could be a more simpler query 1 with no sub queries:

SELECT c.email, cv.value as fname, cv2.value as lname
FROM customer_entity c, customer_entity_varchar cv, customer_entity_varchar cv2
WHERE c.entity_id = $user_id
AND c.entity_id = cv.entity_id 
AND cv.attribute_id = 5
AND c.entity_id = cv2.entity_id 
AND cv2.attribute_id = 7
Score: 0

Below is the code you can fetch the customer detail in Magento

if (Mage::getSingleton('customer/session')->isLoggedIn()) {
$customer = Mage::getSingleton('customer/session')->getCustomer();
$customerData = Mage::getModel('customer/customer')->load($customer->getId())->getData();
Mage::log($customerData);}

Its output will be in array like below:-

 Array
(
    [entity_id] => 1
    [entity_type_id] => 1
    [attribute_set_id] => 0
    [website_id] => 1
    [email] => test@example.com
    [group_id] => 1
    [increment_id] => 000000001
    [store_id] => 1
    [created_at] => 2007-08-30 23:23:13
    [updated_at] => 2008-08-08 12:28:24
    [is_active] => 1
    [firstname] => Test
    [lastname] => User
    [password_hash] => 204948a40200e4238db2277d5:eg
    [prefix] => 
    [middlename] => 
    [suffix] => 
    [taxvat] => 
    [default_billing] => 274
    [default_shipping] => 274
)

0

More Related questions