MySQL queries to delete spam members or users from Joomla / Virtuemart Database

[Juan_Carlos]

A common feature of any spam registration or spam user is that they do not place any orders on Virtuemart, by carefully following this instructions you will erase all users without orders from both the Joomla and the Virtuemart tables. Please note that you will also erase any real users who for whatever reason decided not to place an order.

Notes about these queries:
There is always a risk of damaging your virtuemart installation by directly running MySQL queries in your system. On the other hand it will take a long time to erase spam registrations manually. !!! MAKE SURE TO MAKE A BACKUP OF YOUR DATABASE BEFORE FOLLOWING THIS INSTRUCTIONS!!!, that way if something does not work as planned you have the option of restoring your old database and try again.

If you don't know how to execute MySQL queries on your server, learn how to do it or hire someone to do this for you.

Make sure to execute the MySQL queries in the order provided, from 1 to 9.

Notes about queries:

jos_ : make sure to change this to match the prefix for your particular installation.

> 900 : [Make sure to change this number for my system 900 is the Super User with the highest ID] . It is used to skip superusers, since these type of users normally have no orders
find out the correct number for your installation by going to:
Site > Control Panel > Users
Filter Users by : Super Users

or

Check your list of all users, find the first spam registration and use the ID for such users, make sure to select a number higher than your last Super User anyway.


Test: means run query to find out list of users that will be erased later

DELETE : this is the actual query to remove spam registrations, note that you need to run all DELETE queries to remove all spam registrations.


1st : Test : jos_virtuemart_userinfos
SELECT ui.virtuemart_user_id, ui.name FROM jos_virtuemart_userinfos ui WHERE ui.virtuemart_user_id NOT IN (SELECT o.virtuemart_user_id FROM jos_virtuemart_orders o) AND ui.virtuemart_user_id >  900

2nd : Test : jos_virtuemart_userinfos
SELECT infos.name, infos.virtuemart_user_id FROM jos_virtuemart_userinfos infos WHERE infos.virtuemart_user_id IN
(SELECT ui.virtuemart_user_id FROM jos_virtuemart_userinfos ui WHERE ui.virtuemart_user_id NOT IN (SELECT o.virtuemart_user_id FROM jos_virtuemart_orders o) AND ui.virtuemart_user_id >  900)

3rd : DELETE query # A : jos_virtuemart_userinfos
DELETE FROM jos_virtuemart_userinfos infos WHERE infos.virtuemart_user_id IN
(SELECT ui.virtuemart_user_id FROM jos_virtuemart_userinfos ui WHERE ui.virtuemart_user_id NOT IN (SELECT o.virtuemart_user_id FROM jos_virtuemart_orders o) AND ui.virtuemart_user_id >  900)

4th : Test : jos_virtuemart_vmusers
SELECT u.virtuemart_user_id FROM jos_virtuemart_vmusers u WHERE u.virtuemart_user_id IN
(SELECT ui.virtuemart_user_id FROM jos_virtuemart_userinfos ui WHERE ui.virtuemart_user_id NOT IN (SELECT o.virtuemart_user_id FROM jos_virtuemart_orders o) AND ui.virtuemart_user_id >  900)

5th : DELETE query # B : jos_virtuemart_vmusers
DELETE FROM jos_virtuemart_vmusers u WHERE u.virtuemart_user_id IN
(SELECT ui.virtuemart_user_id FROM jos_virtuemart_userinfos ui WHERE ui.virtuemart_user_id NOT IN (SELECT o.virtuemart_user_id FROM jos_virtuemart_orders o) AND ui.virtuemart_user_id >  900)

6th. : Test : jos_user_usergroup_map
SELECT map.user_id FROM jos_user_usergroup_map map WHERE map.user_id IN
(SELECT ui.virtuemart_user_id FROM jos_virtuemart_userinfos ui WHERE ui.virtuemart_user_id NOT IN (SELECT o.virtuemart_user_id FROM jos_virtuemart_orders o) AND ui.virtuemart_user_id >  900)

7th : DELETE query # C : jos_user_usergroup_map
DELETE FROM jos_user_usergroup_map map WHERE map.user_id IN
(SELECT ui.virtuemart_user_id FROM jos_virtuemart_userinfos ui WHERE ui.virtuemart_user_id NOT IN (SELECT o.virtuemart_user_id FROM jos_virtuemart_orders o) AND ui.virtuemart_user_id >  900)

8th : Test : jos_users
SELECT u.id FROM jos_users u WHERE u.id IN
(SELECT ui.virtuemart_user_id FROM jos_virtuemart_userinfos ui WHERE ui.virtuemart_user_id NOT IN (SELECT o.virtuemart_user_id FROM jos_virtuemart_orders o) AND ui.virtuemart_user_id >  900)

9th : DELETE query # D :
DELETE FROM jos_users u WHERE u.id IN
(SELECT ui.virtuemart_user_id FROM jos_virtuemart_userinfos ui WHERE ui.virtuemart_user_id NOT IN (SELECT o.virtuemart_user_id FROM jos_virtuemart_orders o) AND ui.virtuemart_user_id >  900)

If you have any corrections or comments just let me know.

[stinga]

I just went through all of mine, deleted 2000ish in about 30 minutes, does not take long to do manually.

[digger149]

I am using (your step 3)
DELETE FROM j25_virtuemart_userinfos infos WHERE infos.virtuemart_user_id IN
(SELECT ui.virtuemart_user_id FROM j25_virtuemart_userinfos ui WHERE ui.virtuemart_user_id NOT IN (SELECT o.virtuemart_user_id FROM j25_virtuemart_orders o) AND ui.virtuemart_user_id >  62)

and I get  #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'infos WHERE infos.virtuemart_user_id IN (SELECT ui.virtuemart_user_id FROM j25' at line 1

I have changed the table prefix

Can you suggest a way forward?

Many thanks

[Studio 42]

Hi,
I think the query is very dangerous and can really damage your real registred user.
If you have a blog, a newsletter or something else, this get removed when they don't have do an order.
I think the minimal is to check the registration date > 30 days.