Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Find duplicate customer address in database


Recommended Posts

Before I upload all my customer data to a new version of Phoenix, I am having a bit of a clear out of old and inactive customers. The inactive customers was easy as there was an addon ( I deleted over 800 customers who have never bought anything going back 5 years). Finding duplicate customers is proving to be a bit harder. Customers sometimes cant get the forgotten password link to do anything so create another account using a different email address, and its these that I want to find. Once found I have the addon to link both accounts together.

Does anyone know of an addon or a way to check database entries for the same 1st line of the address. Once I have the customers details I can manually check the rest is the same.

REMEMBER BACKUP, BACKUP AND BACKUP

Link to comment
Share on other sites

This simple script will show all duplication

SELECT entry_street_address, 
 COUNT(entry_street_address) AS NumOccurrences
FROM address_book
GROUP BY entry_street_address
HAVING ( COUNT(entry_street_address) > 1 )

 

 

Link to comment
Share on other sites

Cheers Zahid that worked a treat. Not as many as I thought there would be. Most are where ustomers have duplicated addresses in their accounts.  There are only about 8 that actually have duplicate accounts. Thanks for your help.

REMEMBER BACKUP, BACKUP AND BACKUP

Link to comment
Share on other sites

@14steve14You could install Honeypot. It has an address checker tool in admin. It works without having to enable Honeypot, if that is wanted, and doesn't require any admin file changes to use in Phoenix.

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

All of My Addons

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

SELECT ab.entry_street_address, GROUP_CONCAT(ab.customers_id SEPARATOR ' ')
 FROM (SELECT customers_id, entry_street_address FROM `address_book` GROUP BY customers_id) ab
 GROUP BY ab.entry_street_address
 HAVING COUNT(ab.entry_street_address) > 1 

Might be a little easier to use.  It will skip over duplicated addresses in the address_book for one customer, but show duplicates across customers.  And it gives you the duplicate customer IDs rather than just the count and the duplicate address. 

Always back up before making changes.

Link to comment
Share on other sites

@14steve14 My experience is that the street address entered by the customer can have spelling mistakes, some people don't even know where they live!

Suggest you run these excellent querys on the entry_postcode field instead. Customers tend to get their post code correct however some leave out the space before the last 3 characters which could be an issue. I believe there is an SQL query term "REPLACE" that would solve that problem but I am not sure how to implement it.

Live shop Phoenix 1.0.8.4 on PHP 7.4 Working my way up the versions.

Link to comment
Share on other sites

1 hour ago, mhsuffolk said:

@14steve14 My experience is that the street address entered by the customer can have spelling mistakes, some people don't even know where they live!

Suggest you run these excellent querys on the entry_postcode field instead. Customers tend to get their post code correct however some leave out the space before the last 3 characters which could be an issue. I believe there is an SQL query term "REPLACE" that would solve that problem but I am not sure how to implement it.

I will try using the postcode in the query but I think it may throw some weird results as I know I have some customers who are neighbours. I will let you know how I get on so thanks for the suggestion.

REMEMBER BACKUP, BACKUP AND BACKUP

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...