Help with deleting all users with a specific role
Hi ive been migrating a customer from phpbb2 to D6. Was sposed to go live on sunday... Ive had numerous issues (mainly due to poor sql performance on ec2 instances!) and the rest is due to be finished tonight starting 10pm uk time....one problem is that the customer has been using the test site to create nodes, while i stupidly had test forum data on there. I have managed to delete all the test forum data, but im left with 1700 users who need to be readded from the most recent cut of the phpbb database. Anyways, longer story shorter - does anyone know the SQL i can use to delete all authetnicated users, all their post settings (e.g. user_titles_posts) and related tables etc? I assume there is a drupal function that i can loop through somewhere, but im running out of time and wondered if anyone had done this before, and if they could point me in the right direction! cheers! -- -- -- Steve Power Principal Consultant Mobile: +44 (0) 7747 027 243 Initsix Technology and Media -- "So Linux is bulletproof? No. Bulletproof is one of the last stages of drunkenness, not a state of security" http://www.linux.com/feature/60208
You can use user_delete which besides deleting the user, also fires off the user hook with the delete op, so any other modules that are listening will clean up whatever they need to clean up. The implementation of hook_user in the node module will set the uid of existing nodes to 0 in this case, but not delete them. So if you want to eliminate all the nodes for the users, you would want to do that first (node_delete) before doing the user_delete. You'd wrap this all up in a while loop that iterated over each existing uid. On Thu, Mar 12, 2009 at 2:46 PM, Steve Power <steev@initsix.co.uk> wrote:
Hi
ive been migrating a customer from phpbb2 to D6. Was sposed to go live on sunday... Ive had numerous issues (mainly due to poor sql performance on ec2 instances!) and the rest is due to be finished tonight starting 10pm uk time....one problem is that the customer has been using the test site to create nodes, while i stupidly had test forum data on there. I have managed to delete all the test forum data, but im left with 1700 users who need to be readded from the most recent cut of the phpbb database.
Anyways, longer story shorter - does anyone know the SQL i can use to delete all authetnicated users, all their post settings (e.g. user_titles_posts) and related tables etc? I assume there is a drupal function that i can loop through somewhere, but im running out of time and wondered if anyone had done this before, and if they could point me in the right direction!
cheers!
-- -- -- Steve Power Principal Consultant Mobile: +44 (0) 7747 027 243 Initsix Technology and Media -- "So Linux is bulletproof? No. Bulletproof is one of the last stages of drunkenness, not a state of security" http://www.linux.com/feature/60208
-- [ Drupal support list | http://lists.drupal.org/ ]
excellent thankyou. I assume i can create a page with php input, and select users by role with a form. I will try and post the code if it works! cheers On Thu, Mar 12, 2009 at 7:03 PM, William Smith <william.darren@gmail.com>wrote:
You can use user_delete which besides deleting the user, also fires off the user hook with the delete op, so any other modules that are listening will clean up whatever they need to clean up. The implementation of hook_user in the node module will set the uid of existing nodes to 0 in this case, but not delete them. So if you want to eliminate all the nodes for the users, you would want to do that first (node_delete) before doing the user_delete.
You'd wrap this all up in a while loop that iterated over each existing uid.
On Thu, Mar 12, 2009 at 2:46 PM, Steve Power <steev@initsix.co.uk> wrote:
Hi
ive been migrating a customer from phpbb2 to D6. Was sposed to go live on sunday... Ive had numerous issues (mainly due to poor sql performance on ec2 instances!) and the rest is due to be finished tonight starting 10pm uk time....one problem is that the customer has been using the test site to create nodes, while i stupidly had test forum data on there. I have managed to delete all the test forum data, but im left with 1700 users who need to be readded from the most recent cut of the phpbb database.
Anyways, longer story shorter - does anyone know the SQL i can use to delete all authetnicated users, all their post settings (e.g. user_titles_posts) and related tables etc? I assume there is a drupal function that i can loop through somewhere, but im running out of time and wondered if anyone had done this before, and if they could point me in the right direction!
cheers!
-- -- -- Steve Power Principal Consultant Mobile: +44 (0) 7747 027 243 Initsix Technology and Media -- "So Linux is bulletproof? No. Bulletproof is one of the last stages of drunkenness, not a state of security" http://www.linux.com/feature/60208
-- [ Drupal support list | http://lists.drupal.org/ ]
-- -- -- Steve Power Principal Consultant Mobile: +44 (0) 7747 027 243 Initsix Technology and Media -- "So Linux is bulletproof? No. Bulletproof is one of the last stages of drunkenness, not a state of security" http://www.linux.com/feature/60208
excellent thankyou. I assume i can create a page with php input, and select users by role with a form.
$drupal_sql = 'select users.uid from {users} left join {users_roles} on users.uid = users_roles.uid where rid=4 '; // where 4 is your role $r = db_query($drupal_sql); while ($row = db_fetch_array($r)) { user_delete($row->uid); } Sumthin' like that.
if you want to delete the content first, modify code like this:
$drupal_sql = 'select users.uid from {users} left join {users_roles} on users.uid = users_roles.uid where rid=4 '; // where 4 is your role $r = db_query($drupal_sql); while ($row = db_fetch_array($r)) { $usersnodes = db_query("SELECT nid FROM {node} WHERE uid=%d", $row->uid); while($usernode = db_fetch_object($usersnodes)){ node_delete($usernode->nid); } user_delete($row->uid); }
Sumthin' like that. -- [ Drupal support list | http://lists.drupal.org/ ]
pretty rough, but i think it'll work -- Brett Evanson brettev@gmail.com
many thanks to everyone On Thu, Mar 12, 2009 at 9:07 PM, Brett Evanson <brettev@gmail.com> wrote:
if you want to delete the content first, modify code like this:
$drupal_sql = 'select users.uid from {users} left join {users_roles} on users.uid = users_roles.uid where rid=4 '; // where 4 is your role $r = db_query($drupal_sql); while ($row = db_fetch_array($r)) { $usersnodes = db_query("SELECT nid FROM {node} WHERE uid=%d", $row->uid); while($usernode = db_fetch_object($usersnodes)){ node_delete($usernode->nid); } user_delete($row->uid); }
Sumthin' like that. -- [ Drupal support list | http://lists.drupal.org/ ]
pretty rough, but i think it'll work
-- Brett Evanson brettev@gmail.com
-- -- -- Steve Power Principal Consultant Mobile: +44 (0) 7747 027 243 Initsix Technology and Media -- "So Linux is bulletproof? No. Bulletproof is one of the last stages of drunkenness, not a state of security" http://www.linux.com/feature/60208
You'll definitely want to do this through Drupal's API somehow. At admin/user/user, you can select the users you want to delete and then choose to "Delete the selected users" from the update options. It looks like you're only going to see 50 rows at a time, which will take 34 runs through the screen. Not too bad, but if you make a quick temporary hack (there are 2 places where you'll see the number 50) to the user_admin_account() function in user.admin.inc you should be able to bump up the number of users that you can administer at one time. - Marc William Smith wrote:
You can use user_delete which besides deleting the user, also fires off the user hook with the delete op, so any other modules that are listening will clean up whatever they need to clean up. The implementation of hook_user in the node module will set the uid of existing nodes to 0 in this case, but not delete them. So if you want to eliminate all the nodes for the users, you would want to do that first (node_delete) before doing the user_delete.
You'd wrap this all up in a while loop that iterated over each existing uid.
On Thu, Mar 12, 2009 at 2:46 PM, Steve Power <steev@initsix.co.uk <mailto:steev@initsix.co.uk>> wrote:
Hi
ive been migrating a customer from phpbb2 to D6. Was sposed to go live on sunday... Ive had numerous issues (mainly due to poor sql performance on ec2 instances!) and the rest is due to be finished tonight starting 10pm uk time....one problem is that the customer has been using the test site to create nodes, while i stupidly had test forum data on there. I have managed to delete all the test forum data, but im left with 1700 users who need to be readded from the most recent cut of the phpbb database.
Anyways, longer story shorter - does anyone know the SQL i can use to delete all authetnicated users, all their post settings (e.g. user_titles_posts) and related tables etc? I assume there is a drupal function that i can loop through somewhere, but im running out of time and wondered if anyone had done this before, and if they could point me in the right direction!
cheers!
-- -- -- Steve Power Principal Consultant Mobile: +44 (0) 7747 027 243 Initsix Technology and Media -- "So Linux is bulletproof? No. Bulletproof is one of the last stages of drunkenness, not a state of security" http://www.linux.com/feature/60208
-- [ Drupal support list | http://lists.drupal.org/ ]
------------------------------------------------------------------------
-- [ Drupal support list | http://lists.drupal.org/ ]
participants (6)
-
bamlhs＠hotmail.com -
Brett Evanson -
Fred Jones -
Marc Poris -
Steve Power -
William Smith