How to delete Multiple Users...

b1driver · New Member Joined: Oct 12, 2008 Posts: 2

Been looking everywhere...I've got about 400 some odd users I need off my site and am going crazy doing this one at a time. Running 2.20.01 now and am thinking about upgrading just dont have the confidence to do it yet. Thanks for any help you guys can give me, I'm going nuts.

Luis

Raven · Posted: Wed May 13, 2009 12:09 am

If you have access to phpMyAdmin you can delete them in mass via SQL or use the Browse function on the users table to go page by page and delete the records as you come to them.

First and foremost, MAKE A BACKUP of your nuke_users table!

You would then want to verify that you will be deleting the right population.

The query you could use for that would be:
SELECT COUNT(`username`) AS `Count`
FROM `nuke_users`
WHERE `username` IN('user1','user2','user3');

If you wanted to verify the usernames then you would change the above query to:
SELECT `username`
FROM `nuke_users`
WHERE `username` IN('user1','user2','user3');

Once you are sure of the population you can use the following query to perform the delete:
DELETE
FROM `nuke_users`
WHERE `username` IN('user1','user2','user3');

You will need to list all 400 usernames in the IN clause unless you have a way of selecting them via a different WHERE clause.

b1driver · New Member Joined: Oct 12, 2008 Posts: 2

THanks raven, that worked

Luis

Raven · Posted: Thu May 14, 2009 1:08 am

jazzfuser · Worker Joined: Mar 30, 2006 Posts: 111

I am surprised there is not an option, designed by someone, much like the Edit Groups, or Comment admin for dealing with user accounts i.e. delete with individual checks, or "check all" option. With all the bot activity out there this seems like a worthy endeavor - if I had the savvy I would do it.

Raven · Posted: Tue Jan 26, 2010 2:54 pm

You might want to add this to the Lust Factors Update at

Only registered users can see links on this board!
Get registered or login to the forums!

wHiTeHaT · Posted: Tue Jan 26, 2010 4:15 pm

3 steps 3 files for quick user delete

STEP 1
create file named:

links.quick_delete_user.php (put it in admin/links/)
inside links.quick_delete_user.php add:

Code:

<?php

if (!defined('ADMIN_FILE')) {
die('Access Denied');
}
global $admin_file;
if ($radminsuper == 1) {
adminmenu($admin_file . '.php?op=Qudel', _SOMETEXT, 'some_image.gif');
}
?>

STEP 2
create file named:

case.quick_delete_user.php (put in admin/case/)
inside add:

Code:

<?php

if ( !defined('ADMIN_FILE') )
{
die("Illegal File Access");
}

switch($op) {

case "Qudel":

include("admin/modules/quick_delete_users.php");
break;

}

?>

STEP 3
create file named

quick_delete_users.php (put in admin/modules/)
inside add:

Code:

<?php

if (!defined('ADMIN_FILE')) {
die('Access Denied');
}
global $prefix, $db, $admin_file;
$aid = substr($aid, 0, 25);
$row = $db->sql_fetchrow($db->sql_query('SELECT radminsuper FROM ' . $prefix . '_authors WHERE aid=\'' . $aid . '\''));
if ($row['radminsuper'] == 1) {
switch ($op) {
   case 'Qudel':
      Qudel();
      break;
}
} else {
echo 'Access Denied';
}
die();

function Qudel(){
global $admin, $prefix, $db, $language, $multilingual, $admin_file;

include_once('header.php');
GraphicAdmin();

$result = $db->sql_query('SELECT * from ' . $prefix . '_users order by user_id');

$count= $db->sql_numrows($result);

?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td><form name="form1" method="post" action="">
<table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td bgcolor="#FFFFFF"> </td>
<td colspan="4" bgcolor="#FFFFFF"><strong>Delete multiple users in Nuke</strong> </td>
</tr>
<tr>
<td align="center" bgcolor="#FFFFFF">#</td>
<td align="center" bgcolor="#FFFFFF"><strong>Id</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Name</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Email</strong></td>
</tr>
<?php
while ($row = $db->sql_fetchrow($result)) {

?>
<tr>
<td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $row['user_id']; ?>"></td>
<td bgcolor="#FFFFFF"><? echo $row['user_id']; ?></td>
<td bgcolor="#FFFFFF"><? echo $row['username']; ?></td>
<td bgcolor="#FFFFFF"><? echo $row['user_email']; ?></td>
</tr>
<?php
}
?>
<tr>
<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="delete"></td>
</tr>
<?

if(isset($_POST['delete'])){
if (!empty($_POST['checkbox'])) {
foreach($_POST['checkbox'] as $v) {
$del_id = mysql_real_escape_string($v);
$sql = 'DELETE FROM ' . $prefix . '_users WHERE user_id=\'' . $del_id . '\'';
$result = $db->sql_query($sql);
}
}

if($result){
Header('Location: ' . $admin_file . '.php?op=Qudel');
}
}
?>
</table>
</form>
</td>
</tr>
</table>
<?php
include_once('footer.php');
}

?>

jazzfuser · Worker Joined: Mar 30, 2006 Posts: 111

Thank you Whitehat. That was quite excellent, and worked perfectly. If convenient, could you add something like "check all" to that? I get so many unwanted users on a couple of sites that it would be nice to check them all, and just deselect the ones I want to keep (would be much faster).

wHiTeHaT · Posted: Tue Jan 26, 2010 5:07 pm

i could do that..... but when i start , i have to quit other projects i'm working on and i create a new your account module.

And i dont think i will do that..... unless you bring a big bag $ Wink

jazzfuser · Worker Joined: Mar 30, 2006 Posts: 111

That would be great, but I'm donating my time to help these people with their sites, and I was looking for something to help cut down my time spent with such tasks.

Thanks again, those suggestions were more than I could have hoped for when I posted.

Cheers!

montego · Posted: Thu Jan 28, 2010 6:26 am

fkelly has actually written a batch process to do exactly this and I think he even posted the code in the forums here. I am sure it will be on the "to consider" list for future releases. However, it is not as common a need as you have suggested as long as you have the captcha enabled and do not open up forums for anonymous posting. At least I have not found the need as of yet.

jazzfuser · Worker Joined: Mar 30, 2006 Posts: 111

Yes, I used that code, thank you.
I have turned off anonymous posting a lont time ago, so that part is under control.

captcha is enabled via Raven, cpanel, or...?

montego · Posted: Fri Jan 29, 2010 7:08 am

RavenNuke(tm) has two different captcha uses:

1) For user/admin registration/login
2) For various modules (e.g., Feedback)

For 1), this is controlled by the settings in config.php (well commented)

For 2), this is controlled by the settings in rnconfig.php (also well commented)

You will see that there is no captcha for Forums... hence why we typically suggest no anonymous posting or you install a 3rd party hack to stop spam there if you need them to be open to anonymous posting.

jazzfuser · Worker Joined: Mar 30, 2006 Posts: 111

Of course, I get it now. I was thinking captcha would be some sort of "option", literally. I see that it is simply the gfx switch in the configs. Sorry for the ignorance, turns out I have been an avid user of "captcha" for some time now - duh.

Thank you

fkelly · Posted: Fri Jan 29, 2010 11:48 am

My code was posted in an internal forum to seek input from the experts here before releasing it in the wild. At least the title of the forum says Internal Forums. The link is:

Only registered users can see links on this board!
Get registered or login to the forums!

Not sure who can see it.

I'll repost it here with emphasizing what I said in the first posting, the effects are irreversible.

Quote:

Over time your site has had a lot of users register and take a look around. Some keep visiting, others disappear into the ether. Your user table grows and grows and there is no systematic way to maintain it easily.

For this situation I have developed two programs that can work together to help you keep the size of your user table manageable. However, I will say this once here as I do in the programs themselves, the effects of running the batch delete program I provide are irreversible. So take care to read and follow the instructions carefully.

The first program in this suite is lastsitevisit.php. It simply reads your users table sorted by lastsitevisit, converts lastsitevisit into a readable date field and outputs the userid, username, email address and last visit date to the screen. Double quotes and spaces are used as delimiters between the fields to facilitate copying and pasting the output into a spreadsheet for further analysis. Alternatively you can use your text editor of choice.

The second program is batch_delete.php. This program reads the file you have saved (after appropriate editing) from lastsitevisit and looks for userids in the first "field" in each line and deletes the users from your system. It uses logic that was taken from various deactivate and remove user programs within the Ravennuke Your Account module ... in other words it takes care of forums tables, forums groups tables, nsngroups tables and private messages tables as well as the users table. The result should be equivalent to your administratively going through the RNYA deactivate and remove user steps for each user.

Documentation is built right into the programs.

I am using an adhocs directory and a separate dbconfig.php program as well as a copy of db.php. I tried to use the standard config.php but then it tries to call rnconfig.php and include a lot of junk that I don't need. So, I said to heck with it and put the code for these right into the adhocs directory.

I open to suggestions as to improving these programs and securing them further. That's why they are being posted internally first.

Without further adieu here is lastsitevisit.php:

Code:

<?php
// this program can be run with the associated batch_delete.php program or just to provide information about your
// users table
// output is fields from the user table surrounded by double quotes to make it easy to parse this into a spreadsheet
// or your favorite editor
// if you use this as input to batch_delete you will need to remove the double quotes and CAREFULLY select only those userids that you really want to delete
// please read instructions in batch_delete for other setup considerations and please remove all these files from your webserver after use
require_once('dbconfig.php');
require_once('db.php');
$sql = "SELECT * FROM ".$user_prefix."_users ORDER BY `lastsitevisit` ASC";
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result)) {
echo '"' . $row['user_id'] . '" "'. $row['username'] . '" "' . $row['user_email'] . '" "' . date('Y-m-d', $row['lastsitevisit']) . '"<br/>';
}
?>

Quote:

and here is batch_delete.php

Code:

<?php
// USAGE
// Put these files in an adhocs directory under your nuke root
// delete them when done -- saving copies on your local machine but not leaving them on your web server
// create a dbconfig.php with database and prefix items from your config.php
// these include: $dbhost = 'xxxx'; $dbuname = 'xxxx'; $dbpass = 'xxxx'; $dbname = 'xxxx'; $prefix = 'xxxx';
// $user_prefix = 'xxxx'; $dbtype = 'MySQL';
// where xxxx is your values
// copy db.php from the /db directory of your distribution into adhocs
// prepare a text file of userids that you want to delete
// each line in the text file must start with the userid (an integer) followed by a blank
// by default the text file is named deleteusers.txt and is in the adhocs directory
// there is a testmode for this program; to start with we recommend you run it with testmode set to true
// realizing that the effects are irreversible, run this with testmode set to false when you are ready
// you might want to back up your users table first, in fact we'd highly recommend this but realize that this program changes other tables as well and is really irreversible.
require_once('dbconfig.php');
require_once('db.php');
$file = 'deleteusers.txt';
$testmode = true;
$res = fopen("$file","r");
if ($res) {
while (!feof($res)) {
$contents = rtrim(fgets($res));
if (empty($contents)) continue; // Allows empty line at end of file
// echo $contents . '<br />';
$blank = strpos($contents, ' ');
$del_uid = intval(substr($contents, 0, $blank));
if ($del_uid == 0) {
echo 'input lines must start with an integer followed by a blank space '. $contents. ' does not <br />';
continue;
}
if ($testmode) {
echo $del_uid . ' ' . $contents . '<br />';
continue;
}
else {
// Delete phpbb groups associated with the user
$result1 = $db->sql_query('SELECT group_id FROM ' . $prefix . '_bbuser_group WHERE user_id = \''.$del_uid.'\'');
if ($db->sql_numrows($result1) > 0) {
while ( $row_grouplist = $db->sql_fetchrow($result1) ) {
$group_list[] = $row_grouplist['group_id'];
}
$db->sql_query('DELETE FROM ' . $prefix . '_bbuser_group WHERE user_id=\''.$del_uid.'\'');
$delete_sql_id = implode(', ', $group_list);
$result2 = $db->sql_query('SELECT group_id FROM ' . $prefix . '_bbgroups WHERE group_moderator = \'0\' AND group_single_user = \'1\' AND group_id IN ('.$delete_sql_id.')');
if ($db->sql_numrows($result2) >= 1) {
list($group_id) = $db->sql_fetchrow($result2);
$db->sql_query('DELETE FROM ' . $prefix . '_bbauth_access WHERE group_id = '.$group_id.'');
}
$db->sql_query('DELETE FROM ' . $prefix . '_bbgroups WHERE group_moderator = \'0\' AND group_single_user = \'1\' AND group_id IN ('.$delete_sql_id.')');
}
// Delete NSN Group Info
$db->sql_query('DELETE FROM ' . $prefix . '_nsngr_users WHERE uid = \'' . $del_uid . '\'');
$db->sql_query('DELETE FROM ' . $user_prefix . '_users_field_values WHERE uid=\'' . $del_uid . '\'');
$db->sql_query('DELETE FROM ' . $user_prefix . '_users WHERE user_id=\'' . $del_uid . '\'');

// Delete other user info before removing user in case there is a problem along the way
$db->sql_query('UPDATE ' . $prefix . '_bbposts SET poster_id = 1, post_username = \'' . $rem_uname . '\' WHERE poster_id = \''. $del_uid.'\'');
$db->sql_query('UPDATE ' . $prefix . '_bbtopics SET topic_poster = 1 WHERE topic_poster = \''. $del_uid.'\'');
$db->sql_query('UPDATE ' . $prefix . '_bbvote_voters SET vote_user_id = 1 WHERE vote_user_id = \''. $del_uid.'\'');
// Remove other phpbb related info
$db->sql_query('DELETE FROM ' . $prefix . '_bbtopics_watch WHERE user_id=\''. $del_uid.'\'');
$db->sql_query('DELETE FROM ' . $prefix . '_bbbanlist WHERE ban_userid=\''. $del_uid.'\'');
$result3 = $db->sql_query('SELECT privmsgs_id FROM ' . $prefix . '_bbprivmsgs WHERE privmsgs_from_userid = \''. $del_uid.'\' OR privmsgs_to_userid = \''. $del_uid.'\'');
if ($db->sql_numrows($result3) > 0) {
while ( $row_privmsgs = $db->sql_fetchrow($result3) ) {
$mark_list[] = $row_privmsgs['privmsgs_id'];
}
$delete_sql_id = implode(', ', $mark_list);
$db->sql_query('DELETE FROM ' . $prefix . '_bbprivmsgs_text WHERE privmsgs_text_id IN ('. $delete_sql_id.')');
$db->sql_query('DELETE FROM ' . $prefix . '_bbprivmsgs WHERE privmsgs_id IN ('.$delete_sql_id.')');
}
// all optimizes
$db->sql_query('OPTIMIZE TABLE ' . $prefix . '_bbprivmsgs_text');
$db->sql_query('OPTIMIZE TABLE ' . $prefix . '_bbprivmsgs');
$db->sql_query('OPTIMIZE TABLE ' . $prefix . '_bbuser_group');
$db->sql_query('OPTIMIZE TABLE ' . $prefix . '_bbauth_access');
$db->sql_query('OPTIMIZE TABLE ' . $prefix . '_bbgroups');
$db->sql_query('OPTIMIZE TABLE ' . $prefix . '_nsngr_users');
$db->sql_query('OPTIMIZE TABLE ' . $user_prefix . '_users_field_values');
$db->sql_query('OPTIMIZE TABLE ' . $user_prefix . '_users');
$db->sql_query('OPTIMIZE TABLE ' . $prefix . '_bbtopics_watch');
$db->sql_query('OPTIMIZE TABLE ' . $prefix . '_bbbanlist');
} // end of else testmode
} //end of the while reading delete input file
} // we can open the input file
?>