Jump to content
ccw0103

How do I search customer by their phone number in the back office?

Recommended Posts

So, it would be easier to access the customer by searching the mobile phone number as customer sometimes reluctant to say their full name and they expect I can search through their mobile phone number, how do I do that?

 

Thanks

Share this post


Link to post
Share on other sites

I use SQL for that. In Advanced Parameters -> SQL Manager define new query:

select c.id_customer, c.firstname, c.lastname, a.address1, a.address2, a.postcode, a.city,a.postcode, a.phone from ps_customer c, ps_address a where c.id_customer=a.id_customer and a.phone='xxx'

Replace xxx with the number you want and execute. Not the most convenient, but it works.

Share this post


Link to post
Share on other sites

I use SQL for that. In Advanced Parameters -> SQL Manager define new query:

select c.id_customer, c.firstname, c.lastname, a.address1, a.address2, a.postcode, a.city,a.postcode, a.phone from ps_customer c, ps_address a where c.id_customer=a.id_customer and a.phone='xxx'

Replace xxx with the number you want and execute. Not the most convenient, but it works.

Thanks man, appreciate it. I will take whatever works

Share this post


Link to post
Share on other sites

1. Open file "classes/Customer.php"

2. Search for the function named "searchByName"

3. Replace:

 

$sql = 'SELECT *
FROM `'._DB_PREFIX_.'customer`
WHERE (
`email` LIKE \'%'.pSQL($query).'%\'
OR `id_customer` LIKE \'%'.pSQL($query).'%\'
OR `lastname` LIKE \'%'.pSQL($query).'%\'
OR `firstname` LIKE \'%'.pSQL($query).'%\'
)'.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER);
 
with
 
$sql = 'SELECT c.* 
FROM '._DB_PREFIX_.'customer c, '._DB_PREFIX_.'address a 
WHERE ((
c.id_customer = a.id_customer 
AND a.phone LIKE \'%'.pSQL($query).'%\'
 ) 
OR c.email LIKE \'%'.pSQL($query).'%\'
OR c.id_customer LIKE \'%'.pSQL($query).'%\'
OR c.lastname LIKE \'%'.pSQL($query).'%\'
OR c.firstname LIKE \'%'.pSQL($query).'%\'
)'.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER);
 
4. Now you can use the backend search from top left to search for the phone number too.

 

I only did this on 1.6.0.9 so i can't comment on a different version. But it should be similar.

  • Like 5

Share this post


Link to post
Share on other sites

Hi, interesting thread :)

 

How to add phone AND mobile phone search too ?

 

I also saw that the search is very slow after that modification, is that normal ?

 

Edit, I founded why it is so slow, after the modification if you search for an email for example, you'll get infinite results :(

Edited by KevinNash (see edit history)

Share this post


Link to post
Share on other sites
Here' is the fixed one with mobile phone too:

 

$sql = 'SELECT c.* 

FROM '._DB_PREFIX_.'customer c, '._DB_PREFIX_.'address a 

WHERE c.id_customer = a.id_customer AND

(


a.phone LIKE \'%'.pSQL($query).'%\'

OR a.phone_mobile LIKE \'%'.pSQL($query).'%\'


OR c.email LIKE \'%'.pSQL($query).'%\'

OR c.id_customer LIKE \'%'.pSQL($query).'%\'

OR c.lastname LIKE \'%'.pSQL($query).'%\'

OR c.firstname LIKE \'%'.pSQL($query).'%\'

)'.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER);

  • Like 1

Share this post


Link to post
Share on other sites

Thank you so much for this time saver modification :)

 

Works like a charm under PS 1.5.4.1

 

Bow down to you :D

Share this post


Link to post
Share on other sites

Hi, I just saw one thing with the last modification, for some customers ( not all of them, just somes ), there is multiple results for one search :

 

post-603134-0-77538800-1419247101_thumb.jpg

 

Not really bad but a little bit disturbing, any idea why that happens with the last modification ?

Share this post


Link to post
Share on other sites

Another thing :

 

With the modification, if you do a search with an email and if teh customer don't have an adress, the search show 0 results even if the email is good :(

 

Can you take a look p00pstar ?

Share this post


Link to post
Share on other sites

Could somebody help me if possible?

 

I am wanting to search my orders in the back office by postcode is this possible?

 

Could someone tell me what I need to modify to make this possible?

Share this post


Link to post
Share on other sites

 

Here' is the fixed one with mobile phone too:
 
$sql = 'SELECT c.* 
FROM '._DB_PREFIX_.'customer c, '._DB_PREFIX_.'address a 
WHERE c.id_customer = a.id_customer AND
(
a.phone LIKE \'%'.pSQL($query).'%\'
OR a.phone_mobile LIKE \'%'.pSQL($query).'%\'
OR c.email LIKE \'%'.pSQL($query).'%\'
OR c.id_customer LIKE \'%'.pSQL($query).'%\'
OR c.lastname LIKE \'%'.pSQL($query).'%\'
OR c.firstname LIKE \'%'.pSQL($query).'%\'
)'.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER);

 

it now shows blank page in 1.6 version, any fix??? :)

Share this post


Link to post
Share on other sites

customer can use many addresses, so it will be a little hard

especially because you can feetch only one "address" and display it there

Share this post


Link to post
Share on other sites

Surely there must be a change in the code so we can pull up orders using there postcode? I may have to look at a different platform if this cannot be made possible?

Share this post


Link to post
Share on other sites

customer can use many addresses, so it will be a little hard

especially because you can feetch only one "address" and display it there

Vekia , develop a module for customer search with all possible fields! ( e.g , phone , city , address , etc)

I think it should be succesfull :)

  • Like 3

Share this post


Link to post
Share on other sites

Vekia sir

 

Is there any update about this? This really very helpful when we can search a customer by their mobile phone numbers. Thanks a lot.

Share this post


Link to post
Share on other sites
As this given by p00pstar , this code is working in 1.6.1.2, i have tested and had to refresh the page 4 to 5 times, and it works perfectly

 

 

Here' is the fixed one with mobile phone too:

 

$sql = 'SELECT c.* 

FROM '._DB_PREFIX_.'customer c, '._DB_PREFIX_.'address a 

WHERE c.id_customer = a.id_customer AND

(


a.phone LIKE \'%'.pSQL($query).'%\'

OR a.phone_mobile LIKE \'%'.pSQL($query).'%\'


OR c.email LIKE \'%'.pSQL($query).'%\'

OR c.id_customer LIKE \'%'.pSQL($query).'%\'

OR c.lastname LIKE \'%'.pSQL($query).'%\'

OR c.firstname LIKE \'%'.pSQL($query).'%\'

)'.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER);

  • Like 1

Share this post


Link to post
Share on other sites

 

As this given by p00pstar , this code is working in 1.6.1.2, i have tested and had to refresh the page 4 to 5 times, and it works perfectly
 
 
Here' is the fixed one with mobile phone too:
 
$sql = 'SELECT c.* 
FROM '._DB_PREFIX_.'customer c, '._DB_PREFIX_.'address a 
WHERE c.id_customer = a.id_customer AND
(
a.phone LIKE \'%'.pSQL($query).'%\'
OR a.phone_mobile LIKE \'%'.pSQL($query).'%\'
OR c.email LIKE \'%'.pSQL($query).'%\'
OR c.id_customer LIKE \'%'.pSQL($query).'%\'
OR c.lastname LIKE \'%'.pSQL($query).'%\'
OR c.firstname LIKE \'%'.pSQL($query).'%\'
)'.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER);

 

Is it work on versiona 1.6.1.5?

  • Like 1

Share this post


Link to post
Share on other sites

 

As this given by p00pstar , this code is working in 1.6.1.2, i have tested and had to refresh the page 4 to 5 times, and it works perfectly
 
 
Here' is the fixed one with mobile phone too:
 
$sql = 'SELECT c.* 
FROM '._DB_PREFIX_.'customer c, '._DB_PREFIX_.'address a 
WHERE c.id_customer = a.id_customer AND
(
a.phone LIKE \'%'.pSQL($query).'%\'
OR a.phone_mobile LIKE \'%'.pSQL($query).'%\'
OR c.email LIKE \'%'.pSQL($query).'%\'
OR c.id_customer LIKE \'%'.pSQL($query).'%\'
OR c.lastname LIKE \'%'.pSQL($query).'%\'
OR c.firstname LIKE \'%'.pSQL($query).'%\'
)'.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER);

 

 Yes it works fine  in 1.6.1.5?

Share this post


Link to post
Share on other sites

Hello , 

the solution from this post is much simpler to use!

https://www.prestashop.com/forums/topic/534663-solved-how-to-search-customers-by-phone-mobile/

 

Here's a simple override that adds "Phone" and "Mobile" columns to the Customers > Addresses tab in the Back Office so you can filter by phone or mobile. Create override/controllers/admin/AdminAddressesController.php with the following:

<?php

class AdminAddressesController extends AdminAddressesControllerCore
{
public function __construct()
{
parent::__construct();

$this->fields_list['phone'] = array('title' => $this->l('Phone'), 'filter_key' => 'a!phone');
$this->fields_list['phone_mobile'] = array('title' => $this->l('Mobile'), 'filter_key' => 'a!phone_mobile');
}
}

 

 

Remember to go to the Advanced Parameters > Performance tab and click the "Clear cache" button after creating the file.

Share this post


Link to post
Share on other sites

Can you search both PHONE and MOBILE PHONE with an SQL command such as: 

 

select c.id_customer,c.email, c.firstname, c.lastname, a.address1, a.address2, a.postcode, a.city,a.postcode, a.phone from ps_customer c, ps_address a where c.id_customer=a.id_customer and a.phone='xxx'

 

Because I don't have access and I don't wanna mess with code and I'm using this command for almost a year now but it doesn't search for 2nd phone.

 

Thanks in advance!

Share this post


Link to post
Share on other sites

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More