Come together with the global Drupal community in Rotterdam, 28 Sept – 1 Oct 2026. Sessions, contribution, connection, and Early Bird savings until 8 June.
By madivad on
Can anyone give me the the mysql statement (ie the SELECT statement) to return the number of unauthorised or blocked users?
Comments
Try this
Hello,
Not really sure but try:
SELECT *
FROM `users`
WHERE STATUS = '0';
I have only one person who is blocked in my database and this person have status set to 0.
Hope it helps
Bertrand
PS : do not forgot to select your table before !
status will be 0 if they are
status will be 0 if they are banned
SELECT COUNT(uid) AS numberbanned FROM {users} WHERE uid >= 1 AND status = 0
COUNT() lets SQL do the work, and eliminates extraneous db_query() calls
in my db, there is a uid '0' that is marked as banned, but is not really a user. I don't know why it's there, but using the where clause 'uid >= 1' will avoid this oversight.
works in my test db on test server at home
That's good, and I just tested it on a test server I have at home, but now that I think about it, I am trying to modify a module that someone else has written and they are obviously following some sort of standard. They include a line like:
SELECT n.nid, n.title, n.changed, u.name, u.uid FROM {node} n ...It's this n. u. and c. I see in other places that has me confused. They are not set in this module so I must assume they come from a previous include.
So using
select count(uid) as numberbanned from users where uid >=1 AND status = 0as my query, how do I get it to conform?in your example, "n" is just
in your example, "n" is just an alias for "node", which is defined in the SQL statement itself, just after the table name {node}.
which of these is shorter (and cleaner):
SELECT node.nid, node.title, node.changed, user.name, user.uid FROM {node} ...or
SELECT n.nid, n.title, n.changed, u.name, u.uid FROM {node} n ...aliasing comes in very handy when you are performing complicated joins
I didn't use it for this particular query because it was only referencing one table, and did not need to specify the table name before "uid"
if you absolutely want to use the aliasing so that the query looks like all the others, you can do something like:
SELECT COUNT(u.uid) AS numberbanned FROM {users} u WHERE u.uid >= 1 AND u.status = 0DOH!
It's been so long since I've done anything in SQL I feel like an idiot when someone gives me that little jolt.
Thanks Corey