Update user subscriptions

[shgysk8zer0]

Write PHP function using prepared statement to update subscription status of registered users.

Update subscribers in WordPress

wp_users

MariaDB [kernvalleysun]> DESCRIBE wp_users;

---

Field	Type	Null	Key	Default	Extra
ID	bigint(20) unsigned	NO	PRI	NULL	auto_increment
user_login	varchar(60)	NO	MUL
user_pass	varchar(255)	NO
user_nicename	varchar(50)	NO	MUL
user_email	varchar(100)	NO	MUL
user_url	varchar(100)	NO
user_registered	datetime	NO		0000-00-00 00:00:00
user_activation_key	varchar(255)	NO
user_status	int(11)	NO		0
display_name	varchar(250)	NO

wp_usermeta

MariaDB [kernvalleysun]> DESCRIBE wp_usermeta;

---

Field	Type	Null	Key	Default	Extra
umeta_id	bigint(20) unsigned	NO	PRI	NULL	auto_increment
user_id	bigint(20) unsigned	NO	MUL	0
meta_key	varchar(255)	YES	MUL	NULL
meta_value	longtext	YES		NULL

wp_pmpro_memberships_users

MariaDB [kernvalleysun]> DESCRIBE wp_pmpro_memberships_users;

---

Field	Type	Null	Key	Default	Extra
id	bigint(20) unsigned	NO	PRI	NULL	auto_increment
user_id	int(11) unsigned	NO	MUL	NULL
membership_id	int(11) unsigned	NO	MUL	NULL
code_id	int(11) unsigned	NO	MUL	NULL
initial_payment	decimal(10,2)	NO		NULL
billing_amount	decimal(10,2)	NO		NULL
cycle_number	int(11)	NO		NULL
cycle_period	enum('Day','Week','Month','Year')	NO		Month
billing_limit	int(11)	NO		NULL
trial_amount	decimal(10,2)	NO		NULL
trial_limit	int(11)	NO		NULL
status	varchar(20)	NO	MUL	active
startdate	datetime	NO		NULL
enddate	datetime	YES	MUL	NULL
modified	timestamp	NO	MUL	CURRENT_TIMESTAMP	on update CURRENT_TIMESTAMP

15 rows in set (0.00 sec)

User meta

MariaDB [kernvalleysun]> SELECT DISTINCT(meta_key) FROM wp_usermeta;

---

meta_key
nickname
first_name
last_name
description
rich_editing
comment_shortcuts
admin_color
use_ssl
show_admin_bar_front
wp_capabilities
wp_user_level
dismissed_wp_pointers
show_welcome_panel
session_tokens
wp_dashboard_quick_press_last_post_id
pmpro_visits
pmpro_views
pmpro_logins
pmpro_stripe_updates
wp_user-settings
wp_user-settings-time
closedpostboxes_post
metaboxhidden_post
pmpro_bfirstname
pmpro_blastname
pmpro_baddress1
pmpro_baddress2
pmpro_bcity
pmpro_bstate
pmpro_bzipcode
pmpro_bcountry
pmpro_bphone
pmpro_bemail
pmpro_CardType
pmpro_AccountNumber
pmpro_ExpirationMonth
pmpro_ExpirationYear
default_password_nag
closedpostboxes_page
metaboxhidden_page
meta-box-order_post
screen_layout_post
meta-box-order_dashboard

43 rows in set (0.00 sec)

user_capabilities

MariaDB [kernvalleysun]> SELECT DISTINCT(meta_value) FROM wp_usermeta WHERE meta_key = 'wp_capabilities';

---

meta_value
a:1:{s:13:"administrator";b:1;}
a:1:{s:8:"Follower";b:1;}
a:2:{s:13:"Administrator";b:1;s:13:"administrator";b:1;}
a:1:{s:10:"subscriber";b:1;}

4 rows in set (0.00 sec)

WP PMPRO Membership Status

MariaDB [kernvalleysun]> SELECT DISTINCT status FROM wp_pmpro_memberships_users;

---

status
active
admin_changed

2 rows in set (0.00 sec)

[kvsun-editor]

There is an add-on for the plugin to import users. Looking into using that. Requires creating a CSV. Update: The plugin only imports regular users. To import as members requires a
Paid Memberships Pro - Import Users from CSV Add On

[kvsun-editor]

Code for creating new users seems to be

$wpdb->prepare("
                INSERT INTO {$wpdb->pmpro_memberships_users}
                ( `user_id`, `membership_id`, `code_id`, `initial_payment`, `billing_amount`, `cycle_number`, `cycle_period`, `billing_limit`, `trial_amount`, `trial_limit`, `startdate`, `enddate`)
                    VALUES 
                    ( %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %s, %s )",
                $user_id,
                $level_id,
                '0',
                '0',
                '0',
                '0',
                '0',
                '0',
                '0',
                '0',
                current_time('mysql'),
                '0000-00-00 00:00:00'
            );

[kvsun-editor]

Update query seems to be

$wpdb->prepare("
                    INSERT INTO {$wpdb->pmpro_memberships_users}
                    (`user_id`, `membership_id`, `code_id`, `initial_payment`, `billing_amount`, `cycle_number`, `cycle_period`, `billing_limit`, `trial_amount`, `trial_limit`, `startdate`, `enddate`)
                    VALUES
                    ( %d, %d, %d, %s, %s, %d, %s, %d, %s, %d, %s, %s )",
                $level['user_id'], // integer
                $level['membership_id'], // integer
                $level['code_id'], // integer
                $level['initial_payment'], // float (string)
                $level['billing_amount'], // float (string)
                $level['cycle_number'], // integer
                $level['cycle_period'], // string (enum)
                $level['billing_limit'], // integer
                $level['trial_amount'], // float (string)
                $level['trial_limit'], // integer
                $level['startdate'], // string (date)
                $level['enddate'] // string (date)
            );

[shgysk8zer0]

Trying to update all users using UPDATE wp_pmpro_memberships_users SET membership_id = 5 WHERE membership_id > 7;