At Praxis we use Restrict Content Pro as the membership system for our curriculum portal. We decided that all grads get access for life, not just during the program. So, I needed a way to clear over 200 member expiration dates. The only bulk method available through the WordPress interface is to set the expiration dates to another date in the future, which would just kick my problem further down the road. So I needed to dust off my SQL knowledge and directly edit the database.
Step 1: Back up the database.
Don’t be a fool. Back up your database and test the queries on a local development version first. Never run queries for the first time on production. The backup is also a failsafe that you can restore if something goes wrong despite your testing.
Step 2: Find the data.
I saw that all data related to Restrict Content Pro usually had rcp somewhere
in the table, column, or key. So I started with the rcp tables. They had nothing
to do with expiration dates, so I checked the wp_usermeta table since RCP
extends the WordPress users with more functionality. Bingo. There was a column
called meta_key with rcp_expiration in it with corresponding date values.
Step 3: Make sure you are editing the correct data by running a SELECT statement first.
Sure, you could run your UPDATE statement first, but I like to make sure I am editing the correct data by running a SELECT statement first and then using the same WHERE clause for my UPDATE statement.
After a few stupid syntax errors, here is the SELECT statement that got exactly
what I wanted. This shows the user ID so I can spot check, restricts searching
to the rcp_expiration meta key, and looks for values that are not none.
SELECT user_id, meta_key, meta_value FROM wp_usermeta
WHERE meta_key = 'rcp_expiration' AND meta_value != 'none';This returned 176 results. When I changed it to show only meta values that were
none, I got 31 values. 31+176=207, which is the total number of users. Looking
good.
Step 4: Craft your UPDATE statement.
Now that we know we selected the correct data with our previous statement, it is time to craft our UPDATE statement.
Here I’m updating the wp_usermeta table and setting the meta_value to none
where the meta_key is rcp_expiration and the corresponding meta_value is
not none.
UPDATE wp_usermeta SET meta_value = 'none' WHERE meta_key
= 'rcp_expiration' AND meta_value != 'none';I tested this on my local machine and it updated 176 rows. Just like we wanted.
Step 5: Run the same query on production.
Now that we’ve tested the query in our development environment and verified that we got the results we wanted, we can run the query on the production database. If you use phpmyadmin and want to triple check that you aren’t messing anything up, you can click the “Simulate Query” button first. (I did.)
Step 6: Verify things worked.
Log in to WordPress and check the RCP membership area. Verify that all
expiration dates are now set to none. Also verify that your users can still
log in. You should have a few user test logins specifically for this purpose.
You can also check your site logs throughout the day to make sure people are
still logging in. You can’t count on them always letting you know when something
doesn’t work. More often than not they will just stop using it. It is up to you
to verify everything works as it should!
