MODx从数据库中检索数据并排序到表中

Question

I'm trying to set up a table with scores from the MODX database, this data has been entered in via tv.variables in the MODX admin panel.

My SQL code retrieves my desired data with:

SELECT sc.pagetitle, cv.value, t.name
FROM pphc_site_tmplvar_contentvalues cv, pphc_site_content sc, pphc_site_tmplvars t
WHERE sc.id = cv.contentid
AND cv.tmplvarid = t.id
ORDER BY cv.value * 1 DESC

My SQL retrieves the team name, range of team scores (plays, wins, losses, draws, total points) and if they won/lost/drew.

Below is a screenshot of what I'm after (with the SQL I can get at the moment)

My code at the moment for dealing with the SQL/loop is:

//$output = $x; //get draws, losses, played, points and wins
$id = $x; //id of current page

function sort_by_value($a, $b) {
    return $b["value"] - $a["value"];
}

$sql = "SELECT sc.pagetitle, cv.value, t.name
FROM pphc_site_tmplvar_contentvalues cv, pphc_site_content sc, pphc_site_tmplvars t
WHERE sc.id = cv.contentid
AND cv.tmplvarid = t.id
AND sc.id = $id
ORDER BY cv.value * 1 DESC";

$result = $modx->query($sql)->fetchAll();

usort($result, "sort_by_value");
//print_r($result);die();
$html = '';

foreach ($result as $row) {
    $html .= ''. $row['value'] .'<br>';
}
return $html;

At the moment all this code does is retrieve the results of each team - Great!

I can't seem to figure out how to lay out the data in table form AND include the team names

Answer 1

Go back to your SQL & rewrite the query so that you get your results like this:

pagetitle | wins | losses | played | draws | points 
Bath HC   |  5   |   4    |   2    |   5   |  234  

then you will be able to:

foreach ($result as $row) {

    $html.= $modx->getChunk($rowTpl,$row);
}

return $html;

where $rowTpl is a chunk you have defined in modx ~ something like:

<tr>
     <td>[[+pagetitle]]</td>
     <td>[[+wins]]</td>
     <td>[[+losses]]</td>
     <td>[[+played]]</td>
     <td>[[+draws]]</td>
     <td>[[+points]]</td>
</tr>

It's just a matter of getting the query returning the data formatted in a more useful way.