Sometimes when launching a site you need to import users from another site. User Import module makes that job quite simple. A year goes by and request comes in to delete all imported users who have never logged in. You have the following options:

• User Prune
  • pros - deletion based on criteria
  • cons - no batch API
• Custom module with hook_cron implementation
  • pros - very customizable, allows calling user_delete
  • cons - depends on cron running frequency and capacity

If neither of these options fit and there is no button clicking money around to submit a form hundreds of times to delete users manually one might be tempted to bypass Drupal and PHP completely by executing a query in the db.

Note - I do not recommend this option.

In my case I was dealing with a MySQL 5 database. One could attempt to do this by running multiple queries which include all tables where user data is stored or one could write a stored procedure to automate it even more. Below is a stored procedure I wrote to delete all users who:
1. have never logged in
2. do not have admin permissions - users_roles.rid NOT IN (3, 4, 11, 12)
3. have user ID less than 154116 (I know this because I recorded my initial import's results)

I'm only deleting data from 3 tables - user_profile_values since we imported first and last name, user_roles and users.
If I was dealing with users who have actually logged in there would be more tables involved.

CREATE PROCEDURE `remove_inactive_users`() READS SQL DATA
BEGIN
DECLARE userID INT;
-- Declare variables used just for cursor and loop control
DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
-- Declare the cursor
DECLARE users_cur CURSOR FOR
SELECT DISTINCT(users.uid) AS uid FROM users
LEFT JOIN users_roles ON users.uid = users_roles.uid AND (users_roles.rid NOT IN (3, 4, 11, 12)) WHERE (users.login = 0) AND (users.uid BETWEEN 2 AND 154116) ORDER BY uid LIMIT 0, 5000;
-- Declare 'handlers' for exceptions
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;
-- 'open' the cursor and capture the number of rows returned
-- (the 'select' gets invoked when the cursor is 'opened')
OPEN users_cur;
SELECT FOUND_ROWS() into num_rows;
user_loop: LOOP
FETCH users_cur
INTO userID;
-- break out of the loop if
-- 1) there were no records, or
-- 2) we've processed them all
IF no_more_rows THEN
CLOSE users_cur;
LEAVE user_loop;
END IF;
-- Remove profile_value, users, users_roles records 
DELETE FROM profile_values WHERE uid = userID;
DELETE FROM users_roles WHERE uid = userID;
DELETE FROM users WHERE uid = userID;
-- count the number of times looped
SET loop_cntr = loop_cntr + 1;
END LOOP user_loop;
-- Print the counters so we can see they are the same + the last user ID parsed
SELECT num_rows, loop_cntr, userID;
END

If you are lucky and your database setup allows it you could get away with running your stored procedure just once. In my case it was best to distribute the load hence deleting 5000 users each time the stored procedure is run.

• katrin's blog
• 
• print
• email