Question 1: I would like an sql statement to use in phpMyAdmin to delete the users with the role: authenticated user. Looks like rid = 2.

Related Question 2: I show thousands of users in admin/people but the query: SELECT * FROM drup_users WHERE 1
only shows 30 users. Shouldn't they all be there?

Related Question 3: I created several roles but only the three standard roles appear using: SELECT * FROM drup_role WHERE 1 Where are all the roles I created?

I think I'm having some gross conceptual error. I would appreciate any guidance :) Thanks!

  • 1
    We don't have an SQL writing service here, you'll need to write that yourself (it would be fairly complicated, you'll need to delete field data and take into account data for contrib modules etc if you bypass the API)...regarding the other 2 questions - are you sure you're looking in the right database/tables? Drupal stores users and roles in the DB and I don't think that's pluggable, so it's likely they're in a table somewhere – Clive Jan 29 '15 at 0:58
  • Thanks Clive :) They are just spam bot accounts. There are no nodes associated with them. Looks like the correct database. There is only one with my hosting account. And that db shows my admin account data correctly. As well as the nodes I have added. It has me stumped for sure. – expertalmost Jan 29 '15 at 1:31
  • +1 to @Clive here. You really should not bypass the API. Messing with the database directly could mean bad news. Look at user_delete and EntityFieldQuery to get the users. Do that in an update hook. – webkenny Jan 29 '15 at 1:38
  • Sounds like a plan then. I originally used the module: drupal.org/project/delete_all but I could not log in as admin after that. Even after several hours of various attempts including long calls with my host support. So I had to upload the backup db from that morning. That made me think, maybe easier to go to the db direct :p Have 9,000 spam bot users in 3 months. Ouch! Still cant figure out why what I see in phpMyAdmin looks different than in Drupal. Only one db in there... Any ideas? – expertalmost Jan 29 '15 at 12:53

As mentioned in the comments, directly modifying the tables could be disastrous. If you need to get rid of large numbers of users I would suggestion Using Views with Views Bulk Operations:

You need Views if

You like the default front page view, but you find you want to sort it differently. You like the default taxonomy/term view, but you find you want to sort it differently; for example, alphabetically. You use /tracker, but you want to restrict it to posts of a certain type. You like the idea of the 'article' module, but it doesn't display articles the way you like. You want a way to display a block with the 5 most recent posts of some particular type. You want to provide 'unread forum posts'. You want a monthly archive similar to the typical Movable Type/Wordpress archives that displays a link to the in the form of "Month, YYYY (X)" where X is the number of posts that month, and displays them in a block. The links lead to a simple list of posts for that month.

This module augments Views by allowing bulk operations to be executed on the displayed rows. It does so by showing a checkbox in front of each node, and adding a select box containing operations that can be applied. Drupal Core or Rules actions can be used.

  • Thanks for the guidance Geoff! I will try and figure out how to use it. I've only played with Views a little bit. – expertalmost Jan 29 '15 at 12:55

To answer your second related question, showing records with 30 per page is phpMyAdmin's default behaviour and you can always increase this number or have it show all records.

  • Yes. Thank you. I increased it to 1000 and same result. In the case of rid, still only see 3 role id's. Administrator, authenticated user, anonymous user. I have three other roles defined and they do appear in drupal. Any ideas? – expertalmost Jan 29 '15 at 12:48
  • I've just checked on my own site, and all the roles appear under the table {role}. – Kartagis Jan 29 '15 at 14:25
  • Humm...all I have is drup_role. There is no table "role". Same, I have drup_user which displays 8 of my 9000 spam users :p – expertalmost Jan 29 '15 at 15:12
  • That {role} I mentioned is drup_role in your case. – Kartagis Jan 29 '15 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.