By v8powerage on

Hi
I need a list of all users separated by commas (I need send privmessage to all registered users, but there's no such functionality in this module, so

I figured out that I will simply paste all their nicks into "to:" field, but don't know what query should I run in phpmyadmin to get desired list?

⋅ Categories: Drupal 6.x

Comments

Mark Theunissen’s picture

Mark Theunissen commented

You can get the data from the 'users' table in phpMyAdmin, that has the user information.

v8powerage’s picture

v8powerage commented

I know that, but I need a php code with SQL query, which will print users separated by commas.

Mark Theunissen’s picture

Mark Theunissen commented

So you can use the db_query() and db_result() function to get the data using your SQL (which you can get from phpMyAdmin).

See the following:

http://api.drupal.org/api/group/database/6

Mark Theunissen’s picture

Mark Theunissen commented

And the SQL will be:

SELECT name FROM users

v8powerage’s picture

v8powerage commented

I've been trying some php snippets which I found, and they're returning lists of admins, and moderators, but got stuck at rid 2 (authenticated users), perhaps it's because this role is too populated and it's getting jammed?

I've been trying this one for instance

$rid = 2;
$result = db_query("SELECT u.uid, u.name, u.status FROM {users} u
INNER JOIN {users_roles} ur ON u.uid=ur.uid WHERE ur.rid = %d
AND u.status = 1 ORDER BY u.name ASC", $rid);
while ($u = db_fetch_object($result)) {
  $items[] = l($u->name, "user/" . $u->uid);
}
return theme('item_list', $items);
Mark Theunissen’s picture

Mark Theunissen commented

You can try adding a LIMIT clause to the end of your SQL to limit the number of rows returned.

http://php.about.com/od/mysqlcommands/g/Limit_sql.htm

v8powerage’s picture

v8powerage commented

OK I comprehend this LIMIT 0, 10 gives me records from 1 to 10 then LIMIT 10, 20 gives me records 10 to 20 but where in this code should I paste this, since I can't get it working :P

Mark Theunissen’s picture

Mark Theunissen commented

At the end of your SQL:

$rid = 2;
$result = db_query("SELECT u.uid, u.name, u.status FROM {users} u
INNER JOIN {users_roles} ur ON u.uid=ur.uid WHERE ur.rid = %d
AND u.status = 1 ORDER BY u.name ASC LIMIT 0,30", $rid);
while ($u = db_fetch_object($result)) {
  $items[] = l($u->name, "user/" . $u->uid);
}
return theme('item_list', $items);
v8powerage’s picture

v8powerage commented

I tried the same, and again it works for admins, but not for authenticated users, weird…

Mark Theunissen’s picture

Mark Theunissen commented

Maybe the problem is that Authenticated User role is applied dynamically, it's not a "real" role, because any user gets it as soon as they are logged in.

Why not just leave the role stuff off?

Or make it select all users that are NOT in this role?

Or even better, install the views module and use that to get a view of all users instead of trying to fiddle with SQL queries?

v8powerage’s picture

v8powerage commented

Sure, You're right!
I was gluing my shoes and I inhalated smell of super glue, so I'm not thinking straigh today :D