从magento表结构获取客户信息

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

任何人都可以给我查询吗?</ p>

表是:</ p>


  • customer_entity </ li>
  • eav_attribute </ li>
  • customer_entity_varchar </ li>
    </ ul>
    </ div>

展开原文

原文

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

douhuang4166
douhuang4166 您可以发布表格的架构,以便我们可以使用吗?
接近 10 年之前 回复

11个回答

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'

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)

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

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 ???

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

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

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 =1 Same thing for address entity by replacing the 1 by 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...

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.

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

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

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代码的实际SQL吗? 实际的SQL会变得混乱,并且可能永远不会像你想要的那样。</ p>

由于MySQL中没有“枢轴”,你基本上可以忽略 实际查询中的eav_attribute </ code>。 您将查看它以查找所需的属性ID。 以下是如何选择此数据的简单示例:</ p>

 选择c。*,fn.value firstname 
from customer_entity c
join customer_entity_varchar fn
on c.entity_id = fn.entity_id和fn.attribute_id = 5
;
</ code> </ pre>

继续为每个属性添加这些连接子句,您将获得查询。 基本上,EAV没有针对这种访问进行优化。</ p>

如果你提供一些关于你要做什么的更多细节(或者Magento中的PHP代码是否适用于你的目的) ,我们或许可以帮助你。</ p>

希望有所帮助!</ p>

谢谢,
Joe </ p>
</ div>

展开原文

原文

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.

Since there's no "pivot" in MySQL, you can basically ignore eav_attribute in 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:

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.

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.

Hope that helps!

Thanks, Joe

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

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.

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.

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.

Hope this helps someone...



  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
</ code> </ pre>
</ div>

展开原文

原文

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

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:

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
dsadsadsa1231
dsadsadsa1231 这正是我所寻找的。 我花了几个小时尝试使用SOAP API获取相同的信息,但是请求需要5分钟才能完成。 这肯定是更好的,可以说是最好的。
大约 2 年之前 回复
douzi9430
douzi9430 试试这个:在GROUP BY entity_id行之前写WHERE phone1 = '1111-1111'。 我没有测试它,但我认为它可以工作
大约 5 年之前 回复
dqlxtv1452
dqlxtv1452 我想设置一个where子句,这样我就可以通过在where子句中传递一个电话号码来获取用户的数据,这样我就可以根据他们的电话号码获得特定客户而不是获得所有客户
大约 5 年之前 回复
dongxing7083
dongxing7083 谢谢。 你只想要电话号码。 而已?
大约 5 年之前 回复
doucuodan0897
doucuodan0897 这对我来说完全是我想要的,但是请你告诉我如何更改这个以便我可以设置电话号码的位置,因为它现在提供完整的数据?
大约 5 年之前 回复



这是一个从magento表中提取客户详细信息的小查询:</ p>

  选择c。*,
(从customer_entity_varchar fn中选择fn.value,其中c.entity_id = fn.entity_id和
fn.attribute_id = 5)作为Name,
(从customer_entity_varchar fn中选择fn.value,其中c.entity_id = fn.entity_id和
fn.attribute_id = 7)作为姓氏
来自customer_entity c
</ code> </ pre>
</ div>

展开原文

原文

This is a little query to extract the customer 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



我修改了Andrey Tserkus的精彩答案。 并添加了一个新的WHERE子句。 现在您只需要更换产品SKU,您将获得购买产品的所有客户记录集</ strong>。 在Magento 1.5.1中可以正常使用</ p>

 
/ *只需将第32行中i.sku的值替换为您希望从中获取买家的产品的相应sku * /

SELECT
e。*,
at_prefix .valueAS前缀
at_firstnamevalue` AS`名字`,
`at_middlename` .value `AS`interminname`,
`at_lastname
valueAS lastname
at_suffix.value AS后缀
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。 值IS NOT NULL和at_middlename.value!='',
CONCAT(LTRIM(RTRIM(at_middlename.value)),''),
''),
LTRIM(RTRIM(at_lastname.value)),\ n IF(at_suffix.value IS not NULL和at_suffix.value!='',
CONCAT('',LTRIM(RTRIM(at_suffix.value))),
'')
)ASname
FROM customer_entity ASe
LEFT JOINcustomer_entity_varchar AS at_prefix ON(at_prefixentity_id =e .entity_id)AND(at_prefix.attribute_id ='4')
LEFT JOINcustomer_entity_varchar ASat_firstnameON(at_firstname.entity_id =e .entity_id)AND(at_firstname.attribute_id ='5')
LEFT JOINcustomer_entity_varchar ASat_middlenameON(at_middlenameentity_id =eentity_id)AND(at_middlename .attribute_id='6')
LEFT JOIN
customer_entity_varcharASat_lastnameON(at_lastnameentity_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_suffixattribute_id ='8')
WHERE(eentity_type_id ='1')
ANDeentity_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
</ pre>
</ div>

展开原文

原文

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

/* 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



我相信这可能是一个更简单的查询,没有子查询:</ p>

   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
</ code> </ pre>
</ div>

展开原文

原文

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

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
)
共11条数据 1 尾页
立即提问