How to remove all customers who have never ordered before?

[jgold723]

It was easy to scan the customers table and find the fakes -- so I simply deleted them and figured I was done.

But as others have noted, there are other tables in the database that refer to the (now deleted) customer ids.

My question is -- will this cause any problems down the road? I did this several weeks ago and we haven't experienced any issues, so I'm guessing we're OK. But if there's some kind of cleanup or database realignment that needs doing, I've love to know.

[DrByte]

YES, you should clean up the rest of the data. NEVER BLINDLY HACK OUT RECORDS IN THE DATABASE WITHOUT BEING SURE YOU KNOW WHAT YOU'RE DOING!!!!!

[jgold723]

Message heard and lesson learned (the way I learn most of my lessons, the hard way).

But, this raises two questions:

1) I'm assuming I'd need to:
-- Find the IDs of all the records I deleted;
-- Find the tables where those IDs are still in existence;
-- Delete the records with those IDs from the tables

This would be incredibly time consuming, not to mention I increase the possibility of deleting records that shouldn't be. Is there a utility that can do these comparisons and clean up the database?

2) Is there (and if there isn't, why?) a ZC extension that will facilitate the deletion of customers? Right now the only options seem to be to ban the customer or limit their shopping options. A utility that would allow customer records to be easily deleted enmasse would be incredibly useful.

[fakeDecoy]

I'm having this issue too with lots of spam customers I need to delete. Basically ones using the company name of AT&T, microsoft, and apple.

It looks like it would just take a good SQL DELETE query with the right JOIN keywords to clean up these tables. Has anyone come up with one?

address_book
customers
customers_info
customers_basket
customers_basket_attributes
products_notifications

[DrByte]

I'm having this issue too with lots of spam customers I need to delete. Basically ones using the company name of AT&T, microsoft, and apple.

It looks like it would just take a good SQL DELETE query with the right JOIN keywords to clean up these tables. Has anyone come up with one?

address_book
customers
customers_info
customers_basket
customers_basket_attributes
products_notifications

I find that usually when people are asking something this specific they've already started deleting customer records from the customers table using some tool like phpMyAdmin (or even better something like Navicat or SqlYog or SequelPro). In such cases, running the following will very quickly (in a few seconds) clean up (delete) the associated records from the other related tables:

update reviews set customers_id = null where customers_id not in (select customers_id from customers);delete from address_book where customers_id not in (select customers_id from customers);
delete from customers_info where customers_info_id not in (select customers_id from customers);
delete from customers_basket where customers_id not in (select customers_id from customers);
delete from customers_basket_attributes where customers_id not in (select customers_id from customers);
delete from whos_online where customer_id not in (select customers_id from customers);
delete from products_notifications where customers_id not in (select customers_id from customers);

[carlwhat]

I find that usually when people are asking something this specific they've already started deleting customer records from the customers table using some tool like phpMyAdmin (or even better something like Navicat or SqlYog or SequelPro). In such cases, running the following will very quickly (in a few seconds) clean up (delete) the associated records from the other related tables:

update reviews set customers_id = null where customers_id not in (select customers_id from customers);delete from address_book where customers_id not in (select customers_id from customers);
delete from customers_info where customers_info_id not in (select customers_id from customers);
delete from customers_basket where customers_id not in (select customers_id from customers);
delete from customers_basket_attributes where customers_id not in (select customers_id from customers);
delete from whos_online where customer_id not in (select customers_id from customers);
delete from products_notifications where customers_id not in (select customers_id from customers);

i think what is missing from here is the initial delete.... if you wanted to delete all customers who have not ordered, you could use something like:

delete from customers where customers_id not in (select customers_id from orders);

and then follow with all of the remaining sql statements.

if you wanted to delete ones with the company name, it gets a little trickier as the company name is stored in the address_book table and we would have to incorporate that into the initial delete.

as with all SQL statements, i encourage you to remember an old Shakespearean saying:

"to err is human... to screw things up, you need a computer.... and to screw things up royally, you need sql...."

good luck!

[DrByte]

i think what is missing from here is the initial delete

Yes. That was quite intentional, as giving an initial delete is destructive without any constraints, and could be terribly harmful if someone "just does it" without understanding the bigger picture.

As you said, doing specific deletions based on specific criteria, is a separate exercise.

[fakeDecoy]

Cool, thanks! I think that will do the trick in helping me to clean spam out of my customer db.

[fakeDecoy]

FYI, my queries to delete customers with zero lifetime orders and with no activity for 6 months:

DELETE from zen_customers
WHERE customers_id IN
	(SELECT customers_info_id from zen_customers_info where TIMESTAMPDIFF(MONTH,customers_info_date_account_created, NOW()) > 6
AND TIMESTAMPDIFF(MONTH,customers_info_date_of_last_logon, NOW()) > 6)	
AND customers_id NOT IN
	(SELECT customers_id FROM zen_orders);


update zen_reviews set customers_id = null where customers_id not in (select customers_id from zen_customers);
delete from zen_address_book where customers_id not in (select customers_id from zen_customers);
delete from zen_customers_info where customers_info_id not in (select customers_id from zen_customers);
delete from zen_customers_basket where customers_id not in (select customers_id from zen_customers);
delete from zen_customers_basket_attributes where customers_id not in (select customers_id from zen_customers);
delete from zen_whos_online where customer_id not in (select customers_id from zen_customers);
delete from zen_products_notifications where customers_id not in (select customers_id from zen_customers);

[JakeLawless]

Which php file on the ftp does this code go in to?