Updating User Meta with SQL Query

Question

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!

Answer 1

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');
}