php 从 magento 表结构中获取客户信息

getting customer info from magento table structure

提问by Andrey Tserkus

I want to get rows for all customer details from magento tables.

我想从 magento 表中获取所有客户详细信息的行。

Can anyone give me the query?

有人可以给我查询吗？

Tables are:

表是：

• customer_entity
• eav_attribute
• customer_entity_varchar

• 客户实体
• eav_属性
• customer_entity_varchar

回答by Andrey Tserkus

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

可以从 Magento MySQL DB 中提取一些数据，但在执行以下查询之前，请注意以下事项：

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.

1) Magento 被构建为可配置的，因此您不能从 MySQL 获取数据，但必须使用 Magento 客户模块（模型、资源模型）和 Magento 配置数据来检索客户数据。任何直接查询都不能保证与不同的 Magento 版本甚至相同版本的安装兼容。

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.

2) Magento EAV 结构不允许您在一个查询中以一种很好的形式获取所有客户数据，因为表名与属性动态匹配。

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.

3）您不能仅通过一个查询甚至多个查询来提取所有客户数据。因为在模型中创建了许多信息对象，并且只有模型具有在一个客户对象中收集所有数据的逻辑。我谈论送货/账单地址、商店信用、奖励积分（EE 版本）等等。

However just to fetch all customers' varchar attributes you can use following code (not guaranteed to work due to mentioned above in 1):

然而，只是为了获取所有客户的 varchar 属性，您可以使用以下代码（由于上面在 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'

回答by Pablo S G Pacheco

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

只是为了补充@Stefano 的回复，我做了一个很大的查询，只是为了提取一些其他重要的细节，比如地址和电话。其中一些细节只对我的数据库有意义，但可以帮助某人：

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

回答by Stefano Gaviglia

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

这是一个从 magento 表中提取客户详细信息的小查询：

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

回答by Mehdi

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

我阅读了上面的内容，知道外面的一些人试图找出我的问题，我感觉好多了。好吧，正如您所知，您无法在 magento 后台找到有关客户的全部信息。我想要一个非常大的桌子，在那里我可以看到我所有的客户（那些拥有帐户=注册的客户）（以及那些刚刚注册新闻通讯 newsletter_subscribers 的客户）

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

由于我真的不知道 magento 如何提供这些信息，我编写了一些 SQL 代码来提取数据。

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

如上所述，您无法在一个查询中获得有关客户的所有信息。实际上，您将需要与客户端属性一样多的查询。如果实体客户端有 45 个属性（姓名、姓氏、中间名、电子邮件……），那么您将需要 45 个查询来提取它们……我的问题是如何？

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

答案是观点！！其实我写了Views来提取每个属性信息。这意味着我写了 45 个视图，每个视图选择一个属性

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

我不知道它是如何在您的数据库中完成的，但是，您可能知道每个实体都有一个 entity_type_id。就我而言，我对 customer_entity 和 customer_address_entity 感兴趣。customer_entity 有一个 entity_type_id = 1 customer_address_entity 有一个 entity_type_id = 2

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

每个实体都有属性。因此，如果您想查看客户实体可用的属性是什么，请运行查询：

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

Select attribute_code, attribute_id, backend_type from eav_attribute where entity_type_id =1通过将 1 替换为 2，地址实体也是如此。

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

列attributes_id 非常重要，因为这将使您能够通过id 表找到您的属性： entity_customer_vachar、entity_customer_int、entity_customer_text、entity_customer_datetime 您可能会在此表中找到名字attribute_id = 5，正如Joseph Mastey 在他的查询中所写的那样。 ..

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

信息按上表中的类型发送。当您运行上面的查询时，您将看到每个属性都有一个后端类型。这意味着您要查找的信息是以其类型为后缀的表之一。例如，如果您要查找类型为 varchar 的名字，那么您将在 customer_entity_varchar 等中找到它。

If you are still reading, that may mean you understand what I'm talking about. Other wise you need to look at the EAV model And 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

如果你还在阅读，那可能意味着你明白我在说什么。其他方面，您需要查看 EAV 模型和http://inchoo.net/wp-content/uploads/2010/09/MAGENTO_v1.3.2.4-Database_Diagram.pdf 上的 magento 1.3.2.4 数据库架构

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

我无法发布整个解决方案，因为它还没有准备好，如果有帮助，我会放一个 zip 文件。

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

同时，如果有人知道如何在不编辑 magento Core 的情况下使用带有 php 文件的 magento 对象来获取数据，那就太好了！谢谢

回答by Joseph Mastey

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

您是否正在为此或某些 Magento 代码寻找实际的 SQL？实际的 SQL 会变得混乱，而且可能永远不会像您希望的那样显示。

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

由于 MySQL 中没有“pivot”，您eav_attribute在实际查询中基本上可以忽略。您将查看它以查找所需的属性 ID。下面是一个关于如何选择此数据的简单示例：

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 every attribute, and you'll have your query. Basically, EAV is not optimized for this kind of access.

继续为每个属性添加这些连接子句，你就会有你的查询。基本上，EAV 并未针对此类访问进行优化。

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

如果您提供更多有关您尝试执行的操作的详细信息（或者 Magento 中的 PHP 代码是否适合您的目的），我们或许能够为您提供进一步帮助。

Hope that helps!

希望有帮助！

Thanks, Joe

谢谢，乔

回答by Flipmedia

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

我专门尝试从我的 Magento 商店导出客户的手机号码。

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

首先，您可以使用数据配置文件执行此操作，因为它扩展到客户数据集。

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

就我而言，由于某种原因，数据配置文件功能不起作用，因为我没有时间弄清楚为什么我直接转到 MySQL 来获取我的数据。

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

以下 MySQL 查询将使您能够提取具有英国手机号码的客户。注意：您可能需要确定您需要的数据是哪个 attribute_id 并在查询中相应地更新。

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 where statement limiting the result to specific data, the group stops duplicate numbers as the actual data I want is the phone number. You could add INTO OUTFILE to export result set to CSV.

请注意将结果限制为特定数据的限制和 where 语句，该组停止重复数字，因为我想要的实际数据是电话号码。您可以添加 INTO OUTFILE 将结果集导出到 CSV。

Hope this helps someone...

希望这可以帮助某人...

回答by Manas Sahoo

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

回答by Cavit Keskin

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

另一个用于获取属性值的示例 sql 代码。我只使用了 int 和 varchar 属性，您也可以添加其他属性。

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;

回答by Sebastian Buckpesch

I modified the great answer of Andrey Tserkus. And 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 fine for me in Magento 1.5.1

我修改了 Andrey Tserkus 的精彩答案。并添加了一个新的 WHERE 子句。现在您只需要替换产品 SKU，您将获得购买该产品的所有客户记录集。在 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

回答by Disha Goyal

I believe this could be a more simpler query 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