• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Feel like I'm close, but it's not working - Dynamic Table Columns

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

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:
Yeah, quite a few things wrong with that. The SELECT line is broken, you can't quoteName() a MAX, plus you are feeding the result of the entire quoteName() to AS ... without spaces ... then you last three lines of setting up the query are just horribly broken, they need to be actual function calls, not just words. And you can't use {placeholders} in code you run through Sourcerer, placeholders are a Fabrik thing, so you'll need to get tournId from the query string with JFactory::getApplication()->input->getInt('tournId'). So basically, I think there's about 9 errors within 7 lines of setting up that query. :(

Then you are trying to use built in PHP mysqli functions on a J! database object, instead of the J! database API calls.

https://docs.joomla.org/Selecting_data_using_JDatabase

And that's as far as I got, haven't even started looking at the logic for actually processing your data set.

This is really not within the scope of subscription support. If you'd like me to write the code for you, I'd be happy to do it, but it'd have to be something I charged for my time on.

-- hugh
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top