How to run SQL query from Wordpress 'WP Crontrol' plugin

Question

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

Answer 1

I managed to get things working by using:

include_once("wp-config.php");
include_once("wp-includes/wp-db.php");

global $wpdb;

$sql = "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'
        )
    )";
    
$results = $wpdb->get_results($sql);