Calculate days since last log entry

Well you can query the db for the last comment and return the time it was added then calculate from this time how many days has passed this this occurred.

global $wpdb, $mycred;

$user_id = 1;
$last_time = $wpdb->get_var( $wpdb->prepare( "
	SELECT time
	FROM {$mycred->log_table}
	WHERE ref = %s
	AND user_id = %s
	ORDER BY time DESC
	LIMIT 0,1;", 'approved_comment', $user_id ) );

if ( $time === NULL ) {
	// User has not received points for a comment at all
}
else {
	// Now you have the timestamp for the last time the user
	// got points for a comment. Convert this unix timestamp
	// to number of days
	$now = time();
	$days_since = round( ( ( $now - $last_time ) / 86400 ) );
}

Thank you for the response.

I tried your query but $days_since is empty. Is there maybe a typo in your query (or code)? I tried with with a couple of user IDs and all of them have an “approved_comment” entry in the DB, but nothing was returned.

As far as I can see, it should be fine but I think we are missing something…?

You are right, the user ID is passed on to the query as a string instead of an integer.

Change:

AND user_id = %s

to:

AND user_id = %d

I tried it but still did not return anything.

Here is the full code I tried:

global $wpdb, $mycred;

$user_id = 1;
$last_time = $wpdb->get_var( $wpdb->prepare( "
	SELECT time
	FROM {$mycred->log_table}
	WHERE ref = %s
	AND user_id = %d
	ORDER BY time DESC
	LIMIT 0,1;", 'approved_comment', $user_id ) );

if ( $time === NULL ) {
	// User has not received points for a comment at all
}
else {
	// Now you have the timestamp for the last time the user
	// got points for a comment. Convert this unix timestamp
	// to number of days
	$now = time();
	$days_since = round( ( ( $now - $last_time ) / 86400 ) );
}

echo "$days_since days since last comment";

Do you know why it is not working?

Strange.

I used the following code to test this and got it to be working:

add_shortcode( 'days_since_last_comment', 'mycred_pro_days_since_last_comment' );
function mycred_pro_days_since_last_comment() {

	if ( ! is_user_logged_in() ) return;

	global $wpdb, $mycred;

	$user_id = get_current_user_id();
	$last_time = $wpdb->get_var( $wpdb->prepare( "
	SELECT time
	FROM {$mycred->log_table}
	WHERE ref = %s
	AND user_id = %d
	ORDER BY time DESC
	LIMIT 0,1;", 'approved_comment', $user_id ) );

	if ( $last_time === NULL ) {
		// User has not received points for a comment at all
		$return = 'no comments made yet';
	}
	else {
		// Now you have the timestamp for the last time the user
		// got points for a comment. Convert this unix timestamp
		// to number of days
		$days_since = human_time_diff( $last_time );
		$return = "$days_since since last comment";
	}

	return $return;

}

It creates a custom shortcode which show the current users last comment. human_time_diff will return how long ago it occurred in minutes or days.

Can you please tell me where exactly you are running this code? Using your code as a template, I added $user_id as an argument and simply called the function but yet again, nothing was returned. Here is the code I used:

function mycred_pro_days_since_last_comment($user_id) {

	if ( ! is_user_logged_in() ) return;

	global $wpdb, $mycred;

	$last_time = $wpdb->get_var( $wpdb->prepare( "
	SELECT time
	FROM {$mycred->log_table}
	WHERE ref = %s
	AND user_id = %d
	ORDER BY time DESC
	LIMIT 0,1;", 'approved_comment', $user_id ) );

	if ( $last_time === NULL ) {
		// User has not received points for a comment at all
		$return = 'no comments made yet';
	}
	else {
		// Now you have the timestamp for the last time the user
		// got points for a comment. Convert this unix timestamp
		// to number of days
		$days_since = human_time_diff( $last_time );
		$return = "$days_since since last comment";
	}

	return $return;

}

mycred_pro_days_since_last_comment(1);

I am calling this on the front-end, in a page. Did you test this with WP4.0? (sorry about the stupid question, but I can’t see how it is working for you and not me, I’m on a fresh install of WP and mycred install)

EDIT: Please ignore, it works fine. Simply didn’t echo it =) It’s been a long day. Thank you very much!

EDIT 2: Sorry I have one last question. Is it possible to just GET the number in days since last comment? Currently it returns “1 month”, I would want only “30”.

That is fine, I solved it with:

$return = $days_since ? round( abs( time() - $days_since ) / DAY_IN_SECONDS ) : -1;

Thanks again!

No worries. Let me know if you have any further issues or questions and enjoy.