0

I have a Wordpress site that needed to have 9,000+ users imported to set up a Directory Listing. I imported them from an existing MySQL (non-wordpress) database and everything imported just fine. Usernames, passwords and display names (MD5 hashed, but I tested and WP will change the password to phpass on the first login).

Thing is... They have all been imported with a user role of NONE. I need to change this to Subscriber. Now, I can get into my phpMyAdmin and I can see the field wp_capabilities in the wp_usermeta table. I can see that my admin accounts are a:1:{s:13:"administrator";b:1;} and I know that Subscribers are a:1:{s:10:"subscriber";b:1;}.

I can also see that there are many values entered into each user_id. Question is? How can I write a bulk MySQL command to add in the value wp_capabilites='a:1:{s:10:"subscriber";b:1;}' into each user_id except 1, 2 and 3 ie. the newly imported users?

Thanks in advanced!

3

How can I write a bulk MySQL command to add in the value wp_capabilites='a:1:{s:10:"subscriber";b:1;}' into each user_id except 1, 2 and 3 ie. the newly imported users?

You don't. That is a serialized array which is a PHP construct. MySQL has no idea what to do with it. To the database, it is just an string. To PHP it is a representation-- a diagram-- of an array and PHP takes it seriously. If you get a single character wrong in will not unserialize. If you were just copying a fixed string you might be able to pull this off with SQL but you are not-- "I can also see that there are many values entered into each user_id". To "insert" date into those strings you would have to reproduce PHP serialize in SQL.

Let the Core do the work for you:

$args = array(
  'exclude' => array(1,2,3),
);
$users = get_users($args);
foreach ($users as $u) {
  $u->set_role('subscriber');
}

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.