I am trying to modify this function:

// automatically delete users after 7 days in wordpress
function wcs_auto_delete_users() {
global $wpdb;
$query = $wpdb->prepare( "SELECT ID FROM $wpdb->users WHERE datediff( now(), user_registered ) > 7" );
if ( $oldUsers = $wpdb->get_results( $query, ARRAY_N ) ) {
    foreach ( $oldUsers as $user_id ) {
        wp_delete_user( $user_id[0] );
    }
}
}
add_action( 'wcs_daily_clean_database', 'wcs_auto_delete_users' );

wp_schedule_event( time(), 'daily', 'wcs_daily_clean_database' );

to work differently instead — I want it automatically delete users who haven't been active in, say, 2 months. I have a plugin that tracks user activity and stores the data in wp_usermeta. Example:

user_id = 2; meta_key = wp_wp_kc_last_active_time; meta_value = 1422796627

This is the query that I've come up with:

SELECT user_id FROM wp_usermeta WHERE meta_key = 'wp_wp_kc_last_active_time' AND TIMESTAMPDIFF( second, now(), TIMESTAMP(SELECT meta_value) ) > 5184000

But it's not selecting the right IDs. What should I change to make it work?

up vote 2 down vote accepted

Your query is wrong because your third argument to TIMESTAMPDIFF is incorrect.

You should be using meta_value instead of SELECT meta_value.

SELECT user_id FROM wp_usermeta WHERE meta_key = 'wp_wp_kc_last_active_time' AND TIMESTAMPDIFF( second, now(), TIMESTAMP(meta_value) ) > 5184000;

Try that and see if the results start looking correct.

I just checked over the mySQL Date Function Docs and you appear to be doing this wrong.

Try the following instead:

SELECT user_id FROM wp_usermeta WHERE meta_key = 'wp_wp_kc_last_active_time' AND TIMESTAMPDIFF( MONTH, NOW(), FROM_UNIXTIME(meta_value) ) > 2;

Or Maybe...

global $wpdb;

$query = <<<SQL
 SELECT user_id 
 FROM {$wpdb->usermeta} 
 WHERE 
    meta_key = 'wp_wp_kc_last_active_time'
    AND DATEDIFF( NOW(), FROM_UNIXTIME( meta_value ) ) > 60
SQL;

$query = $wpdb->prepare( $query );

Which should gather the right users. If not, try it without the datediff statement and see if anything is returned. If not, then something is amiss with the meta_key (e.g. is it really wp_wp_... or just wp_kc_...)

  • Nope, no syntax error this time, but: "MySQL returned an empty result set (i.e. zero rows)", and there're definitely users who haven't visited the site in over 2 months. – pereyra Feb 1 '15 at 17:17
  • Try the new query I added. I believe your query doesn't do what you think it does. – Privateer Feb 1 '15 at 18:41
  • Sadly, still getting "MySQL returned an empty result set (i.e. zero rows)". – pereyra Feb 1 '15 at 21:25
  • Added another method with a note to check your meta key value as well. It seems odd to have wp_wp_kc_* as a meta key. – Privateer Feb 1 '15 at 21:44
  • The meta key value is correct. This seems to work, thank you! SELECT user_id FROM wp_usermeta WHERE meta_key = 'wp_wp_kc_last_active_time' AND DATEDIFF( NOW(), FROM_UNIXTIME(meta_value) ) > 60; – pereyra Feb 1 '15 at 22:00

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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