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

[ccw0103]

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

[ccw0103]

anyone? please

[ccw0103]

.....anyone

[ccw0103]

please

[ccw0103]

?????

[www.harryfay.co.uk]

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.

[ccw0103]

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

[p00pstar]

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.

[KevinNash]

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 November 24, 2014 by KevinNash (see edit history)

[p00pstar]

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);

[KevinNash]

Thank you so much for this time saver modification

 

Works like a charm under PS 1.5.4.1

 

Bow down to you

[KevinNash]

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 :

 

 

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

[KevinNash]

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 ?

[ccw0103]

awesome stuff!

[wilson113311]

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?

[ccw0103]

in 1.6.14 it now shows blank page any fix???

[ccw0103]

 

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

[wilson113311]

Can anybody help regards searching admin by customer postcode?

[vekia]

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

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

[wilson113311]

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?

[siomosp]

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

[sondiva]

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);

[b.tsvetkov]

 

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?

[sondiva]

 

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?

[siomosp]

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.

[TheNstam]

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!