0

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

0

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.