Magento Question posted in
The official documentation can be found here.

Get customer names, emails and phone numbers from Magento 1.9 database

I have two different SQL codes, one to get user names and emails, and the other one just phone numbers.

Can you help to combine it with one script so it will be possible to get a list of user names, emails, and phone numbers?

Here is the query to get User names and Emails (by @Steve Robbins):

select ce.entity_id, concat(cevf.value, ' ', cevl.value) fullname, ce.email
from customer_entity ce
inner join customer_entity_varchar cevf
    on ce.entity_id = cevf.entity_id
inner join eav_attribute eaf
    on eaf.attribute_id = cevf.attribute_id
inner join customer_entity_varchar cevl
    on ce.entity_id = cevl.entity_id
inner join eav_attribute eal
    on eal.attribute_id = cevl.attribute_id
inner join eav_entity_type eet
    on eet.entity_type_id = eal.entity_type_id = eaf.entity_type_id
where
    eet.entity_type_code = 'customer'
    and eaf.attribute_code = 'firstname'
    and eal.attribute_code = 'lastname'
order by ce.entity_id

And this one for phone numbers only (by @zaka47):

select customer_address_entity_varchar.value from customer_address_entity_varchar
left join  customer_entity on customer_entity.entity_id = "your_customer_id"
left join  customer_address_entity on customer_address_entity.parent_id = customer_entity.entity_id
join eav_attribute on eav_attribute.attribute_code="telephone"
where customer_address_entity_varchar.attribute_id = eav_attribute.attribute_id

My goal is to get the following table structure:

------------------------------------------------------
Full Name  |   Email Address     | Phone Number 
------------------------------------------------------
David Ros  |   [email protected]   | 02-30493929  
Joe Pratt  |   [email protected]     | 03-20392030   
------------------------------------------------------

Thanks.

Tags:

2

Answers


  1. 0

    Here you go in your first query include 2 more joins (customer_address_entity_varchar and eav_attribute) to get attribute telephone 

    select ce.entity_id, 
       concat(cevf.value, ' ', cevl.value) fullname,
       ce.email,
       caev.value telephone
from customer_entity ce
inner join customer_entity_varchar cevf
    on ce.entity_id = cevf.entity_id
inner join eav_attribute eaf
    on eaf.attribute_id = cevf.attribute_id
inner join customer_entity_varchar cevl
    on ce.entity_id = cevl.entity_id
inner join eav_attribute eal
    on eal.attribute_id = cevl.attribute_id
inner join eav_entity_type eet
    on eet.entity_type_id = eal.entity_type_id = eaf.entity_type_id
inner join customer_address_entity_varchar caev
    on ce.entity_id = caev.entity_id
inner join eav_attribute eat 
    on caev.attribute_id = eat.attribute_id 
where
    eet.entity_type_code = 'customer'
    and eaf.attribute_code = 'firstname'
    and eal.attribute_code = 'lastname'
    and eat.attribute_code='telephone'
order by ce.entity_id
    Login or Signup to reply.
  2. 0

    The above answer still wrong, the customer entity id is link in customer_address_entity instead customer_address_varchar.

    Corrent query is as below:

    select ce.entity_id, 
   concat(cevf.value, ' ', cevl.value) fullname,
   ce.email,
   caev.value telephone from customer_entity ce
inner join customer_entity_varchar cevf
    on ce.entity_id = cevf.entity_id
inner join eav_attribute eaf
    on eaf.attribute_id = cevf.attribute_id
inner join customer_entity_varchar cevl
    on ce.entity_id = cevl.entity_id
inner join eav_attribute eal
    on eal.attribute_id = cevl.attribute_id
inner join eav_entity_type eet
    on eet.entity_type_id = eal.entity_type_id = eaf.entity_type_id
inner join customer_address_entity cae 
    on cae.parent_id = ce.entity_id
inner join customer_address_entity_varchar caev
    on cae.entity_id = caev.entity_id
inner join eav_attribute eat 
    on caev.attribute_id = eat.attribute_id 
where
    eet.entity_type_code = 'customer'
    and eaf.attribute_code = 'firstname'
    and eal.attribute_code = 'lastname'
    and eat.attribute_code='telephone'
order by ce.entity_id desc limit 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search