Converting subscribe2 subscribers

The subscribe2 email subscription plugin allows for two kinds of subscribers.  Normal registered wordpress users can subscribe to email notices when new posts are posted.  A simpler process is available where people can subscribe with just an email address.   Similarly (and unfortunately if you have both happening on  one website), there are two unsubscribe processes.  Registered users have to login in to unsubscribe.

The motivation

Two ballet school websites I look after no longer needed their clients to ‘register’.  The clients tend to be there for several years depending how many of the offspring dance and how great the age gap.  New clients used the public easy subscribe option.

At the beginning of the year when updates start going out, the families that have moved on want to unsubscribe.   In the email template, there was an explanation of how to unsubscribe, but of course people couldn’t remember how they had subscribed and would try both.  Some would get caught in the login lockdown/spam traps – forgot those passwords and generally got quite frustrated.   We had to fix this!

So as a quick solution we decided to convert everyone to public subscribers so that there was one quick easy unsubscribe process.

Destination Tables

Subscribe 2 subscribers database table structure

Subscribe 2 subscribers database table structure

Source Tables:

wp usermeta structure

wp usermeta structure

 

wp users table structure

wp users table structure

 

In the longer run I’ll probably complete the Subscribe 2 add-on that was 80% there and add in some unsubscribe tweaks.  I did also consider moving everyone to mail chimp, but for these business owners keeping it all in one place made more sense.

The SQL code

It took a bit of SQL fiddling.  Here is the code.  Hope into phpmyadmin.  I ran the sql queries step by step on a localhost exported copy of the live databases – yes I am into risk minimisation – rather be safe than sorry.      So, use this code at your own risk, backup your data first and ideally run it all on a local exported copy of the user and usermeta first.  Beware (voice of experience speaking here:)  if you do restore, copy a table back, make sure you have copied it with or set the auto increment! 

Convert_Reg_users_to_public_subscribe2 (zip file)

/* Sequence of SQL statements to convert wordpress registered 
subscribers from users into the subscribe2 table 
and clean up afterwards */

/* First make up copies of the tables.  If you need them , 
just drop the other table and rename these bcak to the original name */

/* I suggest you also mae a record of how many users, 
how many non-subsribers, subsribers in the user table, 
how many S2 subscribers in the subscribe2 table etc, 
so you can do some reconciliation checking*/

/* Put site into maintenance mode*/ 

CREATE TABLE wp_subscribe2_backup AS 
SELECT * from wp_subscribe2;

CREATE TABLE wp_users_backup AS 
SELECT * from wp_users;

CREATE TABLE wp_usermeta_backup AS 
SELECT * from wp_usermeta;

/* copy the main subscription data ONLY for the users 
who want to get emails - ONLY RUN IT ONCE,!!*/

INSERT INTO wp_subscribe2 
(email, active, date, time, conf_date, conf_time)
SELECT wp_users.user_email, 
'1',
SUBSTRING(wp_users.user_registered,1, 10),
SUBSTRING(wp_users.user_registered,11, 6),
SUBSTRING(wp_users.user_registered,1, 10),
SUBSTRING(wp_users.user_registered,11, 6)
 FROM wp_users 
INNER JOIN wp_usermeta 
ON wp_users.ID=wp_usermeta.user_id
 WHERE
 wp_usermeta.meta_key = 's2_subscribed'
AND ((wp_usermeta.meta_value IS NOT NULL) 
AND (wp_usermeta.meta_value != ''));
/* note: this includes admin above */

/* optionally copy across the IP address data */
UPDATE wp_subscribe2
SET 
    ip=(SELECT wp_usermeta.meta_value from wp_usermeta 
        WHERE 
            wp_usermeta.user_id = (
                SELECT wp_users.ID 
                FROM wp_users 
                WHERE wp_users.user_email=wp_subscribe2.email)
        AND
            wp_usermeta.meta_key = 'signup_ip')
WHERE (wp_subscribe2.ip = '' );

/* optionally set the confirmation IP data to the same ip */
UPDATE wp_subscribe2
SET 
    conf_ip=ip
WHERE (conf_ip IS NULL);

/* Now comes the scary part */
/* Delete all users with role subscriber */

DELETE FROM wp_users
WHERE wp_users.ID IN (
    SELECT user_id 
    FROM wp_usermeta
    WHERE meta_key = 'wp_capabilities'
    AND meta_value LIKE 'a:1:{s:10:"subscriber"%'
);
/* end part of the capability array was different 
for some subscribers, so use LIKE*/

/* Delete all user meta for users who no longer exist */
DELETE FROM wp_usermeta 
WHERE user_id NOT IN (
    SELECT ID 
    FROM wp_users
);

/* CHECK, CHECK EVERYTHING  */
/* REMEMBER to switch registration off now */