1

I am implementing a new feature to a website which gives users a certain amount of credits every week, if they are of a certain membership level.

We are using Paid Memberships Pro and MyCred. Both are great plugins that are working wonderfullly.

I have a routine that fetches all users at a certain level, then check user meta for the last date where points were awareded. This function is run daily as wrapped in a cron action.

The query to fetch users which is wrapped in a function called dt_get_pmro_users_at_level and is correctly prepared :

SELECT user_id, startdate
FROM wp_pmpro_memberships_users
WHERE membership_id
IN (
  SELECT id
  FROM wp_pmpro_membership_levels
  WHERE name = 'Premium'
)
AND status = 'active'

???? INNER JOIN wp_usermeta ?????

How do I join the query above with the user meta check for last_awarded_points_date, using startdate as a default value (if not exist) ?

The catch: It needs to be backwards compatible. I need to assume that some users will not have a last_awarded_points_date meta.

Im sure its a walk in a park for SQL wizards. Which I am definitely not.

At the moment I pull all users from the database, make another DB call to pull usermeta, convert dates from MySQL to unix timestamps and check them individually ... which is sloooooow.

For context, this is the whole routine :

function dt_membership_credit_cron( $level_name, $credits ) {

  // fetches all users
  // using the query above
  $users = dt_get_pmpro_users_at_level( $level_name );

  // get current time
  $time_now = current_time( 'timestamp' );

  // for each user...
  foreach ( $users as $user ) {

    // each $user is a stdClass with
    //   user_id      Int
    //   startdate    MySQL formated timestamp

    // get last time
    $last_time = get_user_meta( $user->user_id, 'dt_last_awarded_points_date', true);

    // If no last time
    if ( empty( $last_time ) ) {
      $last_time = $user->startdate;
    }

    // get differece between last time and now
    $diff = $time_now - strtotime( $last_time);

    // if difference is smaller than a week in seconds
    // its not time ... yet. Skip
    if ( $diff < 60 * 60 * 24 * 7 ) { continue; }

    // otherwise...

    // grant credits
    mycred_add( 'credit', $user->user_id, $credits, 'Credit' );

    // update new date
    update_user_meta( $user->user_id, 'dt_last_awarded_points_date', date( 'Y-m-d H:i:s', $time_now) );

  }

}

Any help is greatly apreciated. I am very open to suggestions on how to improve this code!

Cheers!

0

Your Answer

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

Browse other questions tagged or ask your own question.