Deleting empty customer accounts from database

[mcpisik]

Hello! We have a few hundred empty spam accounts to delete.

All fields apart from name are empty, and cannot be deleted from admin.
So I need to delete them from the database 'customers' and 'customers_info' tables.

Is there any sql I coould insert, to delete a record where 'customers_email_address' = empty, for example.
(Sorry I don't know php)

We no longer get the spam accounts since upgrading, so would like to remove them now.

Thanks for any ideas.

[swguy]

Take a look at Delete Spam Customers.
https://www.zen-cart.com/downloads.php?do=file&id=2253

[mcpisik]

Yes I have that installed and it worked on all spam customers that aren't 'empty'
But doesn't touch these sadly.

[swguy]

You can just tweak it to meet your own needs.
delete_spam_customers.php line 39 change it to look for firstname = "" or lastname = "".

[mcpisik]

Thanks so much.
They all have first and last names, so if I wanted to look for Date of account creation instead (Only these spam ones have this field blank) could I change it to the following and use the customers_info table instead?
$query = $db->Execute("SELECT * FROM " . TABLE_CUSTOMERS_INFO . " WHERE customers_info_date_account_created LIKE """);

Please let me know if this is suitable and I probably have too many speech marks at the end or not enough spaces?

[swguy]

This mod has a readme.
In the readme is a link to an extended help page on my site.
In that page is an example of how to customize the mod to do date based deletion.

[carlwhat]

Hello! We have a few hundred empty spam accounts to delete.

All fields apart from name are empty, and cannot be deleted from admin.
So I need to delete them from the database 'customers' and 'customers_info' tables.

Is there any sql I coould insert, to delete a record where 'customers_email_address' = empty, for example.
(Sorry I don't know php)

We no longer get the spam accounts since upgrading, so would like to remove them now.

Thanks for any ideas.

if you can get access to phpmyadmin (or some other way to access the database), i would think the following would remove most if not all of what you want:

delete from customers where customers_email_address = '';

delete from customers_info where customers_info_id not in (select customers_id from customers);
delete from address_book where customers_id not in (select customers_id from customers);

backups are CRUCIAL prior to running sql commands. in addition if you have a prefix in your zen-cart tables, you would need to add that.

good luck and be careful!

no implied warranty for any SQL commands.

"to err is human... but to really mess things up you need a computer..... and to mess things up royally, you need SQL...."
william shakespeare

[mcpisik]

This mod has a readme.
In the readme is a link to an extended help page on my site.
In that page is an example of how to customize the mod to do date based deletion.

Great thanks. I used the last example there (And filled in the missing ); at the end, and edited to find the field with NULL that I needed.
I learned that I don't use the = "" , but instead use IS NULL.
In case I need it again or for anyone else, this seemed to work in the delete_spam_customers.php:
$query = $db->Execute("SELECT customers_info_id as customers_id FROM " . TABLE_CUSTOMERS_INFO . " WHERE customers_info_date_account_created IS Null");

I'm not sure why when pressing Delete Spam customers the first time it found about 398 and deleted. There were another 13 left and pushing it again deleted those?
Seems to be all good with a nice tidy customer list now.
Appreciate the mod and your replies thanks!

[mcpisik]

delete from customers where customers_email_address = '';

delete from customers_info where customers_info_id not in (select customers_id from customers);
delete from address_book where customers_id not in (select customers_id from customers);

Thanks for the bit of sql and your reply. I managed to do it with the Delete spam customers mod but thanks all the same.

[lankeeyankee]

if you can get access to phpmyadmin (or some other way to access the database), i would think the following would remove most if not all of what you want:

delete from customers where customers_email_address = '';

delete from customers_info where customers_info_id not in (select customers_id from customers);
delete from address_book where customers_id not in (select customers_id from customers);

Thanks, Carl. I adapted it and it worked a treat to clear out COWOA accounts that never ordered. And then followed the other SQL commands to clear out the customer info, address book, customer basket, customer basket attributes, customer currency tables of these deleted COWOA accounts and cleared quite a lot of dross from the database.

In case anyone needs this for the same thing, I first used: delete from customers where customers_id not in (select customers_id from orders) and cowoa_account = 1; Then I used Carl's other SQL lines to clear out the other tables.