Jump to content
Latest News: (loading..)
Sign in to follow this  
Brian-Bear

Admin Customer Search

Recommended Posts

Has anyone created a module in which a store admin can search for a customer by postcode or phone number.

I found this contribution by Hurl customer search on orders page would it be easy to add postcode and telephone number search to this.

 

Any help greatly appreciate. I think it could be a useful contribution.

Edited by Brian-Bear

Share this post


Link to post
Share on other sites

Its a pretty easy fix... just open up:

 

 

/admin/customers.php

 

look for this sql query

 

$search = "where c.customers_lastname like '%" . $keywords . "%' or c.customers_firstname like '%" . $keywords . "%' or c.customers_email_address like '%" . $keywords . "%'";

 

and replace with this:

 $search = "where c.customers_lastname like '%" . $keywords . "%' or c.customers_firstname like '%" . $keywords . "%' or c.customers_email_address like '%" . $keywords . "%' or c.customers_telephone like '%" . $keywords . "%' or entry_postcode like '%" . $keywords . "%'";

 

thats is, now be able to search by phone or zip code

 

Cheers

Edited by sublok

Share this post


Link to post
Share on other sites

Thanks - this was very helpful!

 

I'd also like to be able to search comments. We use it for storing tracking numbers and product serial numbers and sometimes I need to hunt down a sale by one of these. Is this possible?

Share this post


Link to post
Share on other sites

Hi

 

I was searching the forums to make another mod to customers.php, perhaps you could help. I've made a similar to change to the one above so that I can search by company name as most of our transactions are with companies. The let down of the the search results is that the tablet shows only the first/last name. I'd like to show the company name in the results.

 

I found the relevant parts of customers.php

 

             <tr class="dataTableHeadingRow">
               <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_LASTNAME; ?></td>
               <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_FIRSTNAME; ?></td>
               <td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_ACCOUNT_CREATED; ?></td>
               <td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_ACTION; ?> </td>
             </tr>

 

and modified to add a column heading as follows

 

 

 

             <tr class="dataTableHeadingRow">
               <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_LASTNAME; ?></td>
               <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_FIRSTNAME; ?></td>

               <td class="dataTableHeadingContent">Company Name</td>

               <td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_ACCOUNT_CREATED; ?></td>
               <td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_ACTION; ?> </td>
             </tr>

 

 

 

and then about 40 rows further down my code, found the results

 

               <td class="dataTableContent"><?php echo $customers['customers_lastname']; ?></td>
               <td class="dataTableContent"><?php echo $customers['customers_firstname']; ?></td>
               <td class="dataTableContent" align="right"><?php echo tep_date_short($info['date_account_created']); ?></td>

 

 

so I added in this line in (naive) hope

 

               <td class="dataTableContent"><?php echo $address_book['entry_company']; ?></td>

 

but alas it does not work.

 

between the code for the table heading and tablet content there is a bunch of queries that (I assume) call the data to populate the tablet. So I recklessly added a.entry_company into the following query:

 

   $customers_query_raw = "select c.customers_id, c.customers_lastname, c.customers_firstname, c.customers_email_address, a.entry_company, a.entry_country_id from " . TABLE_CUSTOMERS . " c left join " . TABLE_ADDRESS_BOOK . " a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id " . $search . " order by c.customers_lastname, c.customers_firstname";

 

I have no idea if I am making the correct approach or not but my steps seem logical. Perhaps I've added to the wrong query or need different syntax to display the field? Any help appreciated, I'm assuming someone with just a passing familiarity with the coding conventions here will spot what needs to be done and help me along my way.

 

Thanks in advance!

Share this post


Link to post
Share on other sites

I have answered my own question, just needed to change the code for displaying the field:

 

<td class="dataTableContent"><?php echo $customers['entry_company']; ?></td>

 

hey presto, life is easier

Share this post


Link to post
Share on other sites

If you want to limit your customer phone number or zip code search to people who have actually placed an order, another way to do it would be with a contrib I created:

 

http://addons.oscommerce.com/info/8281

 

It is geared towards an email and name search now but the fields you are after are in the orders table too, so you could just replace the customer_email_address and customer_name fields in the where clause in step 3 with customers_telephone and whichever one of the three postcode fields you want. Or you could add a couple more 'or' clauses in the 'where' clause and have it search on all four fields: email, name, phone and zip...

 

This contrib creates a new search box at the top of the Orders view in Admin. (Right above the Order ID search box.) One box, no matter how many different Orders fields you want to check for a given value. Just type the text string you want to search for and hit enter...


Add-Ons personally installed:

Step by Step Manual Order -- Request Reviews -- Reviews in Product Listing -- Reviews in Product Display -- Review Approval System -- Leverage Browser Cache --
Header Tag Controller -- Multilayer SEO Pop Out Menu -- Follow Us Box -- View All Products -- USPS Shipping Labels -- UPS Shipping Labels -- Monthly Sales/Tax Report --
htacess Optimisation -- Remove Unused Images -- Master Password -- Admin Change Customer Password -- Database Backup Manager -- Zero Stock Report --
Searchbox Search In Descriptions -- Easy Populate 2.76i -- Barcode Rendering -- Admin Sort By Model -- Products Purchased Report

Add-Ons personally developed:

Search for email address, etc in Orders -- Discontinue Product

Add-Ons installed by others:

View Counter -- Site Monitor -- Image Thumbnailer -- Database Optimizer -- Recaptcha -- Discount Coupons -- Add More Fields

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×