How-To: Create a Leaderboard

  • Resolved mikebronner

    (@mikebronner)


    11 years, 10 months ago

    Creating a leader-board is actually fairly easy. Granted, it’s no short code, but using a custom post template, you can do it. I have created a leaderboard using the following code:

    <?php
/*
Template Name: Roster
*/
	get_header();
?>

<table id="roster">
	<tr>
		<th>Avatar</th>
		<th>Name</th>
		<th>Rank</th>
<?php
	if (is_user_logged_in())
	{
?>
		<th>Advancement</th>
		<th>Email</th>
<?php
	}
?>
		<th>Member Since</th>
	</tr>
<?php
	$listUsersID = $wpdb->get_col(
		$wpdb->prepare(
			"SELECT $wpdb->users.ID FROM $wpdb->users ORDER BY %s ASC",
			"konb_user_level, nickname"
		)
	);
foreach ( $listUsersID as $userid )
{
	$user = get_userdata( $userid );
?>
	<tr>
		<td><?php echo get_avatar($userid, $size = '33'); ?></td>
		<td><? echo $user->nickname; ?></td>
		<td><?
if ( !empty( $user->roles ) && is_array( $user->roles ) )
{
	foreach ( $user->roles as $role )
	{
		if (strpos($role, "bbp_") === false)
		{
			echo ucfirst(strtolower($role));
		}
	}
}
		?></td>
		<td><?php echo dpa_get_user_points( $user->ID ); ?></td>
<?php
	if (is_user_logged_in())
	{
?>
		<td><? echo $user->user_email; ?></td>
<?php
	}
?>
		<td><? echo $user->user_registered; ?></td>
	</tr>
<?php
}
?>
</table>
<?php
	comments_template( '', true );
	get_sidebar();
	get_footer();

    This code is saved in the root of my child theme as roster.php. You will be able to use it by creating a normal page in WordPress, then selecting the Roster template. Feel free to modify and customize as needed. I’d show a working example, except our roster page requires the user to be logged in.

    Using a custom page template you can fully customize the roster to your liking, where as with a short code, you’d be very dependent on how the layout was done in the plugin.

    ~Mike

    https://www.ads-software.com/extend/plugins/achievements/

Viewing 15 replies - 31 through 45 (of 76 total)
  • Thread Starter mikebronner

    (@mikebronner)

    Aha! Thanks for your help on this. Found some errors in the function, here is the updated function code. Everyone, please use this:

    /* Generate Leaderboard Rankings **********************************************/
/**
 * Returns either the ranking for the current user (single row result) or for all users (limited to the current page, if not overriden).
 *
 *
 * @param bool $show_current_user Optional. If true will get the ranking for the current user only. Default is false.
 * @param int $offset Optional. If set, will start the query from a given offset record. Default is to use normal pagination offset.
 * @param int $posts_per_page Optional. If set, will change the number of records returned per page. Default is WordPress default value.
 *
 * @return array. Two-dimensional array is returned, array["restults"] holds search results, while array["total_number_of_pages"] holds the max number of pages which can be used for pagination links.
 * @since Achievements (3.2.2)
 * @author Mike Bronner <[email protected]>
 */
function dpa_get_leaderboard_rankings($show_current_user = false, $offset = null, $posts_per_page = null)
{
	global $wpdb;

	if ($show_current_user)
	{
		get_currentuserinfo();
	}
	if (null === $posts_per_page)
	{
		$posts_per_page = intval(get_query_var('posts_per_page'));
	}
	$db_prefix = $wpdb->base_prefix;
    $paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
    $posts_per_page = intval(get_query_var('posts_per_page'));
    if (null === $offset)
    {
	    $offset = ($paged - 1) * $posts_per_page;
	}
	$leaderboard_query = "SELECT SQL_CALC_FOUND_ROWS
				person.*
				,nick.meta_value AS nickname
				,SUM(karma.meta_value) AS total_karma
				,FIND_IN_SET(karma.meta_value, (SELECT  GROUP_CONCAT(DISTINCT ranking.meta_value ORDER BY ranking.meta_value  DESC) FROM " . $db_prefix . "usermeta AS ranking WHERE ranking.meta_key = '" . $db_prefix . "_dpa_points')) as rank
			FROM " . $db_prefix . "users AS person
			LEFT JOIN " . $db_prefix . "usermeta as nick
				ON person.id = nick.user_id
				AND nick.meta_key = 'nickname'
			LEFT JOIN " . $db_prefix . "usermeta as karma
				ON person.id = karma.user_id
				AND karma.meta_key = '" . $db_prefix . "_dpa_points'
		WHERE 1 = 1";
	if ($show_current_user)
	{
		$leaderboard_query .= "
			AND ID = " . $current_user->ID;
	}
	$leaderboard_query .= "
		GROUP BY nick.meta_value
			,person.ID
		ORDER BY total_karma DESC
			,person.user_registered ASC";
	if ($show_current_user)
	{
		$leaderboard_query .= "
		LIMIT 0, 1;";
	}
	else
	{
		$leaderboard_query .= "
		LIMIT " . $offset . ", " . $posts_per_page . ";";
	}
	$leaderboard["results"] = $wpdb->get_results($wpdb->prepare($leaderboard_query, null));
	$sql_posts_total = $wpdb->get_var( "SELECT FOUND_ROWS();" );
    $leaderboard["total_number_of_pages"] = ceil($sql_posts_total / $posts_per_page);

    return $leaderboard;
}

    I had missed two hard-coded database prefixes of mine. Sorry about that.

    Works like a charm! That is wonderful! Thinking of creating a plugin for this and maybe a widget for the sidebar?

    Thread Starter mikebronner

    (@mikebronner)

    Also updated the roster.php template file to work correctly with the headers… was hiding two columns in the header if the user wasn’t logged in.

    <?php
/*
Template Name: Roster
*/
	get_header();

	$listCurrentUser = dpa_get_leaderboard_rankings($current_user->ID);
	$listAllUsers = dpa_get_leaderboard_rankings();
	$user_is_logged_in = is_user_logged_in();
    $html = '
<table id="roster">
	<tr>
		<th>Rank</th>
		<th>Avatar</th>
		<th>Name</th>
		<th>Rank</th>
		<th>Advancement</th>';
	if ($user_is_logged_in)
	{
		$html .= '
		<th>Email</th>';
	}
	$html .= '
		<th>Member Since</th>
	</tr>';
	//put current user at top of each page
	foreach ($listCurrentUser["results"] as $row)
	{
		if (($row->Rank > 10) || ($offset > 0))
		{
			$this_user = get_userdata($row->ID);
			$html .= '
		<tr class="current_user_top">
			<td>' . $row->rank . '</td>
			<td>' . get_avatar($row->ID, 39) . '</td>
			<td>' . $this_user->nickname . '</td>
			<td>';
			if ( !empty( $this_user->roles ) && is_array( $this_user->roles ) )
			{
				foreach ( $this_user->roles as $role )
				{
					if (strpos($role, "bbp_") === false)
					{
						$html .= ucfirst(strtolower($role));
					}
				}
			}
			$html .= '</td>
			<td>' . dpa_get_user_points( $row->ID ) . '</td>';
			if (is_user_logged_in())
			{
				$html .= '
			<td>' . $this_user->user_email . '</td>';
			}
			$html .= '
			<td>' . $this_user->user_registered . '</td>
		</tr>';
		}
	}
	//get the current page of the leaderboard
	foreach ( $listAllUsers["results"] as $row )
	{
		$this_user = get_userdata( $row->ID );
		if ($row->ID == $current_user->ID)
		{
			$html .= '
	<tr class="current_user">';
		}
		else
		{
			$html .= '
	<tr>';
		}
		$html .= '
		<td>' . $row->rank . '</td>
		<td>' . get_avatar($row->ID, 39) . '</td>
		<td>' . $this_user->nickname . '</td>
		<td>';
		if ( !empty( $this_user->roles ) && is_array( $this_user->roles ) )
		{
			foreach ( $this_user->roles as $role )
			{
				if (strpos($role, "bbp_") === false)
				{
					$html .= ucfirst(strtolower($role));
				}
			}
		}
		$html .= '</td>
		<td>' . dpa_get_user_points( $this_user->ID ) . '</td>';
		if ($user_is_logged_in)
		{
			$html .= '
		<td>' . $this_user->user_email . '</td>';
		}
		$html .= '
		<td>' . $this_user->user_registered . '</td>
	</tr>';
	}
	$html .= '
</table>
<div class="navigation">
    <div class="previous panel">' . previous_posts_link('&laquo; Previous Page', $listAllUsers["total_number_of_pages"]) . '</div>
    <div class="next panel">' . next_posts_link('Next Page &raquo;', $listAllUsers["total_number_of_pages"]) . '</div>
</div>';
	echo $html;

	get_footer();
    Thread Starter mikebronner

    (@mikebronner)

    @strozzapr: thanks for working with me on this! Glad to have found that bug. ??

    I have submitted the function for Paul’s consideration to include in the plugin. That way people would be free to create their own leader boards. If Paul decides it’s not something he wants to do, I might consider making it a plugin, depending on the security and scalability risks.

    ??

    Thread Starter mikebronner

    (@mikebronner)

    I have performed some scalability tests on the leaderboard function above, here are the results. Please post feedback if you have ideas, comments, concerns.

    The entire function call above is tested, not just the SQL query. So, if you have around 20,000 users, it will take about 2.8 seconds to load each page.

    1 User
    ======
    Time to fetch one ranked row for current user with 100 attempts:
    Average: 0.000323865413666
    Min: 0.000309944152832
    Max: 0.000519990921021
    Total: 0.0323865413666
    Time to fetch one page of all users with 100 attempts:
    Average: 0.000194818973541
    Min: 0.000191926956177
    Max: 0.00019907951355
    Total: 0.0194818973541

    100 Users
    =========
    Time to fetch one ranked row for current user with 100 attempts:
    Average: 0.000301289558411
    Min: 0.000284910202026
    Max: 0.00036883354187
    Total: 0.0301289558411
    Time to fetch one page of all users with 100 attempts:
    Average: 0.000399761199951
    Min: 0.000257968902588
    Max: 0.000548124313354
    Total: 0.0399761199951

    500 Users
    =========
    Time to fetch one ranked row for current user with 100 attempts:
    Average: 0.000312306880951
    Min: 0.000298023223877
    Max: 0.000331878662109
    Total: 0.0312306880951
    Time to fetch one page of all users with 100 attempts:
    Average: 0.0575305533409
    Min: 0.0567970275879
    Max: 0.0626060962677
    Total: 5.75305533409

    1500 Users
    ==========
    Time to fetch one ranked row for current user with 100 attempts:
    Average: 0.000312027931213
    Min: 0.00029993057251
    Max: 0.00035285949707
    Total: 0.0312027931213
    Time to fetch one page of all users with 100 attempts:
    Average: 0.167186529636
    Min: 0.165693044662
    Max: 0.174437999725
    Total: 16.7186529636

    5000 Users
    ==========
    Time to fetch one ranked row for current user with 100 attempts:
    Average: 0.000325660705566
    Min: 0.000312089920044
    Max: 0.000381946563721
    Total: 0.0325660705566
    Time to fetch one page of all users with 100 attempts:
    Average: 0.560528736115
    Min: 0.555218219757
    Max: 0.661763906479
    Total: 56.0528736115

    10102 Users
    ===========
    Time to fetch one ranked row for current user with 100 attempts:
    Average: 0.000309851169586
    Min: 0.000298023223877
    Max: 0.00036096572876
    Total: 0.0309851169586
    Time to fetch one page of all users with 100 attempts:
    Average: 1.1599576211
    Min: 1.14222192764
    Max: 1.35492897034
    Total: 115.99576211

    20057 Users
    ===========
    Time to fetch one ranked row for current user with 100 attempts:
    Average: 0.000322690010071
    Min: 0.000284910202026
    Max: 0.000576972961426
    Total: 0.0322690010071
    Time to fetch one page of all users with 100 attempts:
    Average: 2.89920308352
    Min: 2.79761099815
    Max: 3.93713092804
    Total: 289.920308352

    Trying to get this to work on my site. I have been missing the leader board widget since it has been discontinued. Unfortunately nothing here is working for me (my fault not yours I am a total rookie).

    Using Text edit I created a roster.php file with your latest code above: the stuff that starts with:

    <?php
    /*
    Template Name: Roster
    */

    Then I uploaded that roster.php file to my theme directory. This did alow me to create a page with Roster as the template so I was feeling like I was on the correct track but when I look at that page I get this message: Fatal error: Call to undefined function dpa_get_leaderboard_rankings() in /home/agentbarnes/barneshdintranet.com/wp-content/themes/buddy/roster.php on line 7

    Any help would be great, Thanks for your work on creating a leader board.

    Thread Starter mikebronner

    (@mikebronner)

    Ah yes, it sounds like you’re missing the function referenced in the error message. Two posts above the code you just copied, you see some more code. Be sure to copy/paste that into the functions.php file of your child-theme. Give that another shot and let me know.

    Thread Starter mikebronner

    (@mikebronner)

    @strozzapr: how would you envision the widget looking? What fields should it have, rank, points, and name? Should it show just the top 10? Hmm, maybe two widgets: one that shows the users rank and score, and another that shows the top 10?

    Let me know what you would like to see and I’ll see what I can do. ??

    @mikebronner

    Thank you very much for your help! I was finally able to integrate the leaderboard into my site using the most recent roster.php and functions.php files that you provided.

    However, I am having an issue with my rankings. The leaderboard is properly sorted by amount of Karma points, but the rankings are completely out of order. For instance, I have 1000 points, which is the most at the moment, so I am at the top of the leaderboard, but my rank reads “2”. Under me, the next 20 users are rank 1 (they all have the same number of points), and maybe 21 ranks down, a test account with even less points shows up as rank 3. All users without points are below that, and they do not even have a rank. I don’t know how to fix this, so if you could help I would be eternally grateful.

    Thread Starter mikebronner

    (@mikebronner)

    Hi jrman, I will try to replicate the issue and get back to you on this. Can you post a link for me to look at? I will try to create 10 users or so modeled after yours and see what’s happening.

    Mike Bronner thanks for taking the time to help me on this. I added the code to my function.php file and it works great!

    Thread Starter mikebronner

    (@mikebronner)

    @agentbarnes: good to hear ?? When you get the page finalized, go ahead and attach a screenshot here. ?? Always good to see how others implement it.

    @mikebronner

    Do you have an e-mail address through which I can send you a screen-shot?

    Edit* Here’s a link to a screenshot: https://i36.tinypic.com/10eftq1.jpg

    Thread Starter mikebronner

    (@mikebronner)

    I see what the problem is now … the column where the points are stored is a string, therefor the query is ranking incorrectly. It thinks that 99 ranks higher than 101 because it looks at it as a string. I’m investigating how to do this right without bringing the query to its knees. ??

    Thread Starter mikebronner

    (@mikebronner)

    The leaderboard function is updated. Please remove the existing dpa_get_leaderboard_rankings() function from your functions.php file and replace it with this one. This should fix the incorrect sorting issue.

    Sorry for the inconvenience.

    /* Generate Leaderboard Rankings **********************************************/
/**
 * Returns either the ranking for the current user (single row result) or for all users (limited to the current page, if not overriden).
 *
 *
 * @param bool $show_current_user Optional. If true will get the ranking for the current user only. Default is false.
 * @param int $offset Optional. If set, will start the query from a given offset record. Default is to use normal pagination offset.
 * @param int $posts_per_page Optional. If set, will change the number of records returned per page. Default is WordPress default value.
 *
 * @return array. Two-dimensional array is returned, array["restults"] holds search results, while array["total_number_of_pages"] holds the max number of pages which can be used for pagination links.
 * @since Achievements (3.2.2)
 * @author Mike Bronner <[email protected]>
 */
function dpa_get_leaderboard_rankings($show_current_user = false, $offset = null, $posts_per_page = null)
{
	global $wpdb;

	if ($show_current_user)
	{
		get_currentuserinfo();
	}
	if (null === $posts_per_page)
	{
		$posts_per_page = intval(get_query_var('posts_per_page'));
	}
	$db_prefix = $wpdb->base_prefix;
    $paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
    $posts_per_page = intval(get_query_var('posts_per_page'));
    if (null === $offset)
    {
	    $offset = ($paged - 1) * $posts_per_page;
	}
	$leaderboard_query = "SELECT SQL_CALC_FOUND_ROWS
				person.*
				,nick.meta_value AS nickname
				,SUM(karma.meta_value) AS total_karma
				,FIND_IN_SET(karma.meta_value, (SELECT  GROUP_CONCAT(DISTINCT ranking.meta_value ORDER BY CONVERT(ranking.meta_value, SIGNED)  DESC) FROM " . $db_prefix . "usermeta AS ranking WHERE ranking.meta_key = '" . $db_prefix . "_dpa_points')) as rank
			FROM " . $db_prefix . "users AS person
			LEFT JOIN " . $db_prefix . "usermeta as nick
				ON person.id = nick.user_id
				AND nick.meta_key = 'nickname'
			LEFT JOIN " . $db_prefix . "usermeta as karma
				ON person.id = karma.user_id
				AND karma.meta_key = '" . $db_prefix . "_dpa_points'
		WHERE 1 = 1";
	if ($show_current_user)
	{
		$leaderboard_query .= "
			AND ID = " . $current_user->ID;
	}
	$leaderboard_query .= "
		GROUP BY nick.meta_value
			,person.ID
		ORDER BY total_karma DESC
			,person.user_registered ASC";
	if ($show_current_user)
	{
		$leaderboard_query .= "
		LIMIT 0, 1;";
	}
	else
	{
		$leaderboard_query .= "
		LIMIT " . $offset . ", " . $posts_per_page . ";";
	}
	$leaderboard["results"] = $wpdb->get_results($wpdb->prepare($leaderboard_query, null));
	$sql_posts_total = $wpdb->get_var( "SELECT FOUND_ROWS();" );
    $leaderboard["total_number_of_pages"] = ceil($sql_posts_total / $posts_per_page);

    return $leaderboard;
}
Viewing 15 replies - 31 through 45 (of 76 total)
  • The topic ‘How-To: Create a Leaderboard’ is closed to new replies.