Bulk Clearing Expiration Dates in Restrict Content Pro

December 4, 2017
Category: TIL
Tags: SQL and WordPress

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!

Find this post useful?

Buy me a coffeeBuy me a coffee