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
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
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
none where the
rcp_expiration and the corresponding
meta_value is not
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!
View more TIL posts