Delete Spam Customers

[swguy]

It never happened but I had a bad dream about it. You *could* have a customer from Zimbabwe who worked for Google and had a yandex.com email address!

[ianhg]

@swguy
I looked at your delete spam customers as you suggested.
I worked out if I add this it will remove customers who have not ordered, but I would prefer to not delete customers who have not ordered in the last month. Is there a way of amending this:

$queries[] = array(   'name' => 'Accounts with no purchase',    'string' => "SELECT customers_id FROM " . TABLE_CUSTOMERS . " WHERE customers_id NOT IN (SELECT customers_id FROM ".TABLE_ORDERS.")"); 

Assuming this is correct, as the plugin was originally yours do you want to amend it to include this or should I simply submit a revised plugin as Remove Customers who have not ordered?
Thanks for the guidance.

[swguy]

Assuming this is correct, as the plugin was originally yours do you want to amend it to include this or should I simply submit a revised plugin as Remove Customers who have not ordered?
Thanks for the guidance.

Neither. I don't update it every time someone has a new idea for a query.

You said,
> I would prefer to not delete customers who have not ordered in the last month.

Do you mean "delete customers who have never ordered if their accounts are more than a month old?"

If so, see the FAQ on my site has additional examples that might be useful. The last example will point you in the right direction.

Test your query in phpMyAdmin and see if it's satisfactory.

[firebug]

All of my fake accounts have letters in their postal code (YeHsaInklb). Can anyone suggest a query I can add to remove all accounts with letters in the postal code?

[swguy]

use the Fake Google accounts query and change

entry_company = 'google'

to

entry_postcode = 'YeHsaInklb'

(and change the query name too.)

[firebug]

use the Fake Google accounts query and change

entry_company = 'google'

to

entry_postcode = 'YeHsaInklb'

(and change the query name too.)

I should have said all of my fake accounts contain all random letters in their post code but none are exactly the same. I need a query to find random letters in the postal code.

Would this work?

$queries[] = array(
'name' => 'Fake Post Code',
'string' => "SELECT customers_id FROM " . TABLE_ADDRESS_BOOK . " WHERE entry_postcode LIKE '%[^a-z]%'"
);

[swguy]

You'll need to use regular expressions and the syntax is different for different versions of mysql. Use phpMyAdmin to figure out a syntax that works with your version.

[firebug]

You'll need to use regular expressions and the syntax is different for different versions of mysql. Use phpMyAdmin to figure out a syntax that works with your version.

Thank you so much!
I added the following to code 26 times for each letter of the alphabet and it removed all the fake accounts that had a letter in the postal code.

$queries[] = array(
'name' => 'Fake Post Code',
'string' => "SELECT customers_id FROM " . TABLE_ADDRESS_BOOK . " WHERE entry_postcode LIKE 'a%'"
);