I have a php function which displays a leader board of all players and stats from my database, which is working as intended. I now however want to amend the function so that i can pass data to it to limit the data being returned.
here is my function display_singles_ladder()
function display_singles_ladder() {
global $wpdb;
$ranking_table = $wpdb->prefix . 'player_rankings';
$players_table = $wpdb->prefix . 'players';
$match_players_table = $wpdb->prefix . 'match_players';
$matches_table = $wpdb->prefix . 'matches';
$results_table = $wpdb->prefix . 'match_results';
$game_scores_table = $wpdb->prefix . 'match_games';
// Get all singles-ranked players
$players = $wpdb->get_results("
SELECT
r.player_id,
r.player_rank,
CONCAT(p.first_name, ' ', p.last_name) AS player_name,
(
SELECT COUNT(*) FROM {$wpdb->prefix}challenges c
WHERE c.challenged_id = p.player_id
AND c.match_type_id = 1
AND c.status = 'pending'
) AS pending_challenges
FROM $ranking_table r
JOIN $players_table p ON r.player_id = p.player_id
WHERE r.match_type_id = 1
ORDER BY r.player_rank ASC
", ARRAY_A);
if (empty($players)) {
return "<p>No ladder data available for singles.</p>";
}
ob_start();
echo "<table class='singles-ladder'>";
echo "<thead>
<tr>
<th>Rank</th>
<th>Player</th>
<th>Matches Played</th>
<th>Match Wins</th>
<th>Match Losses</th>
<th>Game Wins</th>
<th>Game Losses</th>
<th>Win Streak</th>
<th>Challenges</th>
</tr>
</thead><tbody>";
foreach ($players as $player) {
$player_id = (int)$player['player_id'];
// Matches won/lost
$match_stats = $wpdb->get_row($wpdb->prepare("
SELECT
COUNT(*) AS matches_played,
SUM(CASE
WHEN mp.team_number = 1 AND r.team1_score > r.team2_score THEN 1
WHEN mp.team_number = 2 AND r.team2_score > r.team1_score THEN 1
ELSE 0
END) AS wins,
SUM(CASE
WHEN mp.team_number = 1 AND r.team1_score < r.team2_score THEN 1
WHEN mp.team_number = 2 AND r.team2_score < r.team1_score THEN 1
ELSE 0
END) AS losses
FROM $match_players_table mp
JOIN $matches_table m ON mp.match_id = m.match_id
JOIN $results_table r ON r.match_id = m.match_id
WHERE mp.player_id = %d
AND m.match_type_id = 1
", $player_id), ARRAY_A);
$wins = $match_stats['wins'] ?? 0;
$losses = $match_stats['losses'] ?? 0;
$matches_played = $match_stats['matches_played'] ?? 0;
// Game wins/losses
$game_stats = $wpdb->get_row($wpdb->prepare("
SELECT
SUM(CASE
WHEN mp.team_number = 1 AND gs.team1_score > gs.team2_score THEN 1
WHEN mp.team_number = 2 AND gs.team2_score > gs.team1_score THEN 1
ELSE 0
END) AS games_won,
SUM(CASE
WHEN mp.team_number = 1 AND gs.team1_score < gs.team2_score THEN 1
WHEN mp.team_number = 2 AND gs.team2_score < gs.team1_score THEN 1
ELSE 0
END) AS games_lost
FROM $match_players_table mp
JOIN $matches_table m ON mp.match_id = m.match_id
JOIN $game_scores_table gs ON gs.match_id = m.match_id
WHERE mp.player_id = %d
AND m.match_type_id = 1
", $player_id), ARRAY_A);
$games_won = $game_stats['games_won'] ?? 0;
$games_lost = $game_stats['games_lost'] ?? 0;
// Win Streak
$win_streak = get_player_win_streak($player_id);?>
<tr>
<td><?= $player['player_rank']; ?></td>
<td><?= $player['player_name'];?> <?= $player['player_rank'] == 1 ? '👑 ' : ''; ?></td>
<td><?= $matches_played;?></td>
<td><?= $wins;?></td>
<td><?= $losses;?></td>
<td><?= $games_won;?></td>
<td><?= $games_lost;?></td>
<td><?= $win_streak;?></td>
<td>
<?php if ($player['pending_challenges'] > 0): ?>
<span style="color: orange;">⚔️ <?= $player['pending_challenges'] ?> pending</span>
<?php else: ?>
<span style="color: gray;">—</span>
<?php endif; ?>
</td>
</tr><?php
}
echo "</tbody></table>";
return ob_get_clean();
}
and i have a shortcode set up
add_shortcode('singles_ladder', 'display_singles_ladder');
which is called in my wordpress site using the following line
[singles_ladder]
so what i want to be able to do is return either the full table as it currently does if no values are passed, or return a select number of rows and certain columns if values are passed, if possible?
for example could i return the function using something like this
function display_singles_ladder($limit,$columns){
and then pass values through my shortcode snippet? i'm unsure of how to approach this, if anyone can help me it would be much appreciated.
The key is to modify your function to accept an $atts array and use the WordPress shortcode_atts() function to set default values. This ensures your shortcode works as before when no attributes are provided.
function display_singles_ladder($atts) {
global $wpdb;
// ADDED: Define default attributes and merge with user-provided ones.
$args = shortcode_atts([
'limit' => 0, // 0 means no limit.
'columns' => 'all', // 'all' shows every column.
], $atts, 'singles_ladder');
// ADDED: Sanitize and prepare the attributes for use.
$limit = (int) $args['limit'];
// ADDED: Define all possible columns and their headers.
$all_columns = [
'rank' => 'Rank',
'player' => 'Player',
'matches_played' => 'Matches Played',
'match_wins' => 'Match Wins',
'match_losses' => 'Match Losses',
'game_wins' => 'Game Wins',
'game_losses' => 'Game Losses',
'win_streak' => 'Win Streak',
'challenges' => 'Challenges',
];
// ADDED: Determine which columns to display.
if ($args['columns'] === 'all') {
$visible_columns = array_keys($all_columns);
} else {
// Convert the comma-separated string from the shortcode into an array.
$visible_columns = array_map('trim', explode(',', $args['columns']));
}
// Table prefixes
$ranking_table = $wpdb->prefix . 'player_rankings';
$players_table = $wpdb->prefix . 'players';
$match_players_table = $wpdb->prefix . 'match_players';
$matches_table = $wpdb->prefix . 'matches';
$results_table = $wpdb->prefix . 'match_results';
$game_scores_table = $wpdb->prefix . 'match_games';
// MODIFIED: Added a dynamic LIMIT clause to the main query.
$limit_clause = ($limit > 0) ? $wpdb->prepare("LIMIT %d", $limit) : '';
// Get all singles-ranked players
$players = $wpdb->get_results("
SELECT
r.player_id,
r.player_rank,
CONCAT(p.first_name, ' ', p.last_name) AS player_name,
(
SELECT COUNT(*) FROM {$wpdb->prefix}challenges c
WHERE c.challenged_id = p.player_id
AND c.match_type_id = 1
AND c.status = 'pending'
) AS pending_challenges
FROM $ranking_table r
JOIN $players_table p ON r.player_id = p.player_id
WHERE r.match_type_id = 1
ORDER BY r.player_rank ASC
$limit_clause
", ARRAY_A);
if (empty($players)) {
return "<p>No ladder data available for singles.</p>";
}
ob_start();
echo "<table class='singles-ladder'>";
// MODIFIED: Dynamically generate the table header.
echo "<thead><tr>";
foreach ($all_columns as $key => $header) {
if (in_array($key, $visible_columns)) {
echo "<th>{$header}</th>";
}
}
echo "</tr></thead><tbody>";
foreach ($players as $player) {
$player_id = (int) $player['player_id'];
// --- All your database queries for stats remain the same here ---
// Matches won/lost
$match_stats = $wpdb->get_row($wpdb->prepare("SELECT COUNT(*) AS matches_played, SUM(CASE WHEN mp.team_number = 1 AND r.team1_score > r.team2_score THEN 1 WHEN mp.team_number = 2 AND r.team2_score > r.team1_score THEN 1 ELSE 0 END) AS wins, SUM(CASE WHEN mp.team_number = 1 AND r.team1_score < r.team2_score THEN 1 WHEN mp.team_number = 2 AND r.team2_score < r.team1_score THEN 1 ELSE 0 END) AS losses FROM $match_players_table mp JOIN $matches_table m ON mp.match_id = m.match_id JOIN $results_table r ON r.match_id = m.match_id WHERE mp.player_id = %d AND m.match_type_id = 1", $player_id), ARRAY_A);
$wins = $match_stats['wins'] ?? 0;
$losses = $match_stats['losses'] ?? 0;
$matches_played = $match_stats['matches_played'] ?? 0;
// Game wins/losses
$game_stats = $wpdb->get_row($wpdb->prepare("SELECT SUM(CASE WHEN mp.team_number = 1 AND gs.team1_score > gs.team2_score THEN 1 WHEN mp.team_number = 2 AND gs.team2_score > gs.team1_score THEN 1 ELSE 0 END) AS games_won, SUM(CASE WHEN mp.team_number = 1 AND gs.team1_score < gs.team2_score THEN 1 WHEN mp.team_number = 2 AND gs.team2_score < gs.team1_score THEN 1 ELSE 0 END) AS games_lost FROM $match_players_table mp JOIN $matches_table m ON mp.match_id = m.match_id JOIN $game_scores_table gs ON gs.match_id = m.match_id WHERE mp.player_id = %d AND m.match_type_id = 1", $player_id), ARRAY_A);
$games_won = $game_stats['games_won'] ?? 0;
$games_lost = $game_stats['games_lost'] ?? 0;
// Win Streak
$win_streak = get_player_win_streak($player_id);
?>
<tr>
<?php // MODIFIED: Dynamically generate the table cells. ?>
<?php if (in_array('rank', $visible_columns)): ?><td><?= $player['player_rank']; ?></td><?php endif; ?>
<?php if (in_array('player', $visible_columns)): ?><td><?= esc_html($player['player_name']);?> <?= $player['player_rank'] == 1 ? '👑' : ''; ?></td><?php endif; ?>
<?php if (in_array('matches_played', $visible_columns)): ?><td><?= $matches_played;?></td><?php endif; ?>
<?php if (in_array('match_wins', $visible_columns)): ?><td><?= $wins;?></td><?php endif; ?>
<?php if (in_array('match_losses', $visible_columns)): ?><td><?= $losses;?></td><?php endif; ?>
<?php if (in_array('game_wins', $visible_columns)): ?><td><?= $games_won;?></td><?php endif; ?>
<?php if (in_array('game_losses', $visible_columns)): ?><td><?= $games_lost;?></td><?php endif; ?>
<?php if (in_array('win_streak', $visible_columns)): ?><td><?= $win_streak;?></td><?php endif; ?>
<?php if (in_array('challenges', $visible_columns)): ?>
<td>
<?php if ($player['pending_challenges'] > 0): ?>
<span style="color: orange;">⚔️ <?= $player['pending_challenges'] ?> pending</span>
<?php else: ?>
<span style="color: gray;">—</span>
<?php endif; ?>
</td>
<?php endif; ?>
</tr><?php
}
echo "</tbody></table>";
return ob_get_clean();
}
// Your shortcode registration remains the same.
add_shortcode('singles_ladder', 'display_singles_ladder');
Display the rank, name, and win streak for the Top 5 players. [singles_ladder limit="5" columns="rank,player,win_streak"]