Web design tutorials - Joomla Wordpress and SEO tips

Delete Spam users from a Joomla Database with phpMyAdmin

Sometimes a Site with registration gets hammered by spam bot registrations.

Removing hundreds or thousands of registrations is very time consuming manually.

This command in sql will delete users with duplicate names

 DELETE FROM`jos_users` WHERE SUBSTRING_INDEX(name," ",1)=SUBSTRING_INDEX(name," ",-1)

Deletes users who have never activated

DELETE FROM `jos_users` WHERE `lastvisitDate` = "0000-00-00 00:00:00";

DELETE FROM `nipd_users` WHERE email like "%.ru"

Next we need to delete all the entries from the jos_users_usergroup_map table  

DELETE FROM `jos_user_usergroup_map` WHERE `user_id` NOT IN (SELECT `id` from `jos_users` );