I have a SQL query which does exactly what I want if I run it directly in the database:
UPDATE wp_pmpro_memberships_users
SET wp_pmpro_memberships_users.status = 'admin_cancelled'
WHERE wp_pmpro_memberships_users.user_id IN
(
/*Select all users that have an active Membership but no active Subscription*/
SELECT pmpro.user_id FROM wp_pmpro_memberships_users as pmpro
JOIN wp_pmpro_membership_levels as pmprol ON pmprol.id=pmpro.membership_id
JOIN wp_users ON pmpro.user_id = wp_users.ID
WHERE pmpro.status = 'active'
AND pmpro.user_id NOT IN
(
/*Select all users that have an active Subscription*/
SELECT meta.meta_value
FROM wp_posts as p
JOIN wp_postmeta as meta
ON meta.post_id = p.id
JOIN wp_users as u
ON meta.meta_value = u.id
WHERE post_type LIKE 'shop_subscription'
AND post_status LIKE 'wc-active'
AND meta.meta_key = '_customer_user'
)
)
And have added this code to my functions.php file in my child theme:
add_action("cron_expire_nonactive_memberships", "cron_expire_nonactive_memberships");
function cron_expire_nonactive_memberships() {
global $wpdb;
$sqlQuery = "UPDATE {$wpdb->wp_pmpro_memberships_users}
SET wp_pmpro_memberships_users.status = 'admin_cancelled'
WHERE wp_pmpro_memberships_users.user_id IN
(
/*Select all users that have an active Membership but no active Subscription*/
SELECT pmpro.user_id FROM {$wpdb->wp_pmpro_memberships_users} as pmpro
JOIN {$wpdb->wp_pmpro_membership_levels} as pmprol ON pmprol.id=pmpro.membership_id
JOIN {$wpdb->wp_users} ON pmpro.user_id = wp_users.ID
WHERE pmpro.status = 'active'
AND pmpro.user_id NOT IN
(
/*Select all users that have an active Subscription*/
SELECT meta.meta_value
FROM {$wpdb->wp_posts} as p
JOIN {$wpdb->wp_postmeta} as meta
ON meta.post_id = p.id
JOIN {$wpdb->wp_users} as u
ON meta.meta_value = u.id
WHERE post_type LIKE 'shop_subscription'
AND post_status LIKE 'wc-active'
AND meta.meta_key = '_customer_user'
)
)";
}
But I'm trying to get it to run using a plugin called 'WP Crontrol'.
It has an option for adding a new Cron Event and so I'm using the hook cron_expire_nonactive_memberships
and have been running it manually but nothing happens.
I'm running as a localhost if that makes any difference.
I'm not sure if I have a problem with my php code that's attempting to run my SQL - how can I debug what's going on?
Edit:
Hmm, I can see a lot of the following in the error logs but I'm unsure how to resolve:
PHP Notice: Undefined property: wpdb::$wp_users in C:\xampp\htdocs\myproject\wp-includes\wp-db.php on line 648