arcadeicons
Member
I'm trying to create a table with dynamic columns for a scoreboard I'm creating. I have all of my main data entered from my forms in 3 tables...
table: #submit_score
gameID = game ID (INT)
player = player ID (INT)
gamescore = score (INT)
tournID = tournament ID (INT) <-- use to filter, {tournID} will be passed to the page in the URL, so only display players and scores matching the passed {tournID} value
table: #_users
name = Player Name (VAR)
table: #_djc2_items
name = Game Name (VAR)
The number of games, their names and the numbers can change for every single Tournament (tournID) so I need a table that will adapt to the data for each different tournament. Also I'm trying to only show the highest score each player got on each game as players may play the same game multiple times in a single tournament.
I don't want to do a pivot on my submit_score table as it will end up being millions of rows over time so that's a very expensive view to create. I'd rather pull the required data into PHP and then create my arrays before building the table.
Here's how far I have got, I am using sourcerer to inject my code into an article.
live page http://www.arcadeicons.com/index.php?option=com_content&view=article&id=7&tournID=12
table: #submit_score
gameID = game ID (INT)
player = player ID (INT)
gamescore = score (INT)
tournID = tournament ID (INT) <-- use to filter, {tournID} will be passed to the page in the URL, so only display players and scores matching the passed {tournID} value
table: #_users
name = Player Name (VAR)
table: #_djc2_items
name = Game Name (VAR)
The number of games, their names and the numbers can change for every single Tournament (tournID) so I need a table that will adapt to the data for each different tournament. Also I'm trying to only show the highest score each player got on each game as players may play the same game multiple times in a single tournament.
I don't want to do a pivot on my submit_score table as it will end up being millions of rows over time so that's a very expensive view to create. I'd rather pull the required data into PHP and then create my arrays before building the table.
Here's how far I have got, I am using sourcerer to inject my code into an article.
live page http://www.arcadeicons.com/index.php?option=com_content&view=article&id=7&tournID=12
Code:
{source}
<?php
// Get a db connection.
$db = JFactory::getDbo();
// Create a new query object.
$query = $db->getQuery(true);
$query->SELECT ($db->quoteName('g.name', 'p.name', MAX('ss.gamescore') . 'AS' . $db->quoteName('player_score')))
$query->FROM ($db->quoteName('submit_score' , 'ss'));
$query->JOIN ('inner', $db->quoteName('nfojm_users') . ' AS ' . 'p' . ' ON (' . $db->quoteName('ss.player') . ' = ' . $db->quoteName('p.id')));
$query->JOIN ('inner', $db->quoteName('nfojm_djc2_items') . ' AS' . 'g' . ' ON (' . $db->quoteName('ss.gameID') . ' = ' . $db->quoteName('g.id')));
$query->WHERE tournID = {$tournId};
$query->GROUP BY g.name, p.name
$query->ORDER BY g.name ASC, player_score DESC;
$games = [];
$scores = [];
$i = 0;
$mysqli = mysqli($db);
$result = $mysqli->query($query);
while ($row = $result->fetch_assoc()) {
if (!in_array($row["name"], $games) {
$i = 0;
$games[] = $row["name"];
}
while (count($scores[]) <= $i) {
$scores[] = array();
}
$scores[$i][$row["name"]] = $row["player_score"];
}
$result->close();
print("<table><thead><tr>");
foreach ($games as $game ) {
print("<th>{$game}</th>");
}
print("</tr></thead><tbody>");
foreach ($scores as $score_row) {
print("<tr>");
foreach ($games as $game) {
$ps = isset($score_row[$game]) ? $score_row[$game] : "n/a";
print("<td>{$ps}</td>");
}
print("</tr>");
}
print("</tbody></table>");
?>
{/source}
Last edited: