How to remove all customers who have never ordered before?

My site is hit by registration spam, and removing each customer manually from the admin is not feasible.

I am now thinking about using phpmyadmin to do the job. What should be the mysql line to remove these spam from database?

Thanks in advanced.

There are a number of tables that need to be addressed:
address_book
customers
customers_info
customers_basket
customers_basket_attributes
products_notifications

You would need to verify the customers_id against the table:
orders

If nothing is found, then clean up the customer tables ...

It would be a good idea to also check against the table:
customers_info

for when the customer's account was created so that you do not delete customers that are new, without orders, but may have not had time to complete or make an order yet ...

I tell you what ive found with the spammers, is they always select the first country on your country list. So I have made my first country option 'Please Select' so then I know which ones are spam.

Thanks for replies.
Actually the spammers on my site come from the same source and use the exact same address when registration. But because I'm not very familiar with server programming, I still cannot figure out a way to mass delete them from database.

ebookba:

Thanks for replies.
Actually the spammers on my site come from the same source and use the exact same address when registration. But because I'm not very familiar with server programming, I still cannot figure out a way to mass delete them from database.

Well thats easy then. If they all use the same address just search for that address in the customers table, and it will bring up all the customer with that address in a big list. Then you can mass delete.

Sounds like there are quite a few of us having the same bogus customer issues. Mine are all coming from China. I have recorded the ip addresses and have blocked them on my hosting server. This has taken care of it for me. I blocked out the entire range of them. I know, this stinks.

Hiya,

I seem to have the same problem on one of my sites, starting about the 10th of the month. I've successfully deleted all of the offending "customers," but from what I can tell, they've set up a proxy in Sweden. I don't want to block potential customers coming from Sweden, so I'm loath to set up an .htaccess deny. Does anyone have others ideas how to prevent more of this from trickling in?

Thanks in advance...

I am having the same problem. How do I do the search in myPHPadmin? They use one of two addresses with bogus names. And they are all from China.

I couldn't do the search as it didn't turn up anything, but I did manage to do bulk deletes by going into the address book in phpMyAdmin and deleting entire pages of spam customers. 30 at a time is much quicker than one. I am going to try the suggestion that makes them pick a country. Maybe that will slow them down. (Thanks for that suggestions, BTW.) :)

freya:

I couldn't do the search as it didn't turn up anything, but I did manage to do bulk deletes by going into the address book in phpMyAdmin and deleting entire pages of spam customers. 30 at a time is much quicker than one. I am going to try the suggestion that makes them pick a country. Maybe that will slow them down. (Thanks for that suggestions, BTW.) :)

It works, I confirmed it last night, with no sign ups. Go into phpadmin and change the '-Please Select' country ID to 0. Thats all you have to do. You don't need to chanege the code in create_account.php.

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.

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

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.

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

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

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

DrByte:

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!

carlwhat:

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.

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

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

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