CC5, Syntax, Sums, and Listing

Allright, so I have a tale of two connections.

I have this table that has entries from a form that tracks our wellness. Ultimately, I want to be able to sum up the amount of points any particular user has.

In CC4, I've gotten the connection to sum using a custom connection, and code that I picked up from tutorials here on the board.

The code is as follows:

Header:

<?php
global $col_total;
$col_total = 0;
?>
<table border="0" width="300" cellspacing="0" cellpadding="0" id="table1">
 <tr>
  <td>Category</td>
  <td>Points</td>
  <td>User ID</td>
 </tr>

Body

<tr>
  <td>{formCategory}</td>
  <td>{points_1}</td>
  <td>{cf_user_id}<td>
</tr>

<?php
global $col_total;
$col_total += $row['points_1'];
?>

Footer

<tr>
  <td>{formCategory}</td>
  <td>{points_1}</td>
  <td>{cf_user_id}<td>
</tr>

<?php
global $col_total;
$col_total += $row['points_1'];
?> 

In CC4, I noticed that I'm not required to put in a model ID

In CC5, I am required to have a model ID, which I chose as 'record'

Hence, I had to change my body code as follows:

<tr>
  <td>{record.formCategory}</td>
  <td>{record.points_1}</td>
</tr>

<?php
global $col_total;
$col_total += $row['record.points_1'];
?>

However, my total at the end is always equal 0, which tells me that there's something wrong with my $row['record.points_1']; statement.

So, two questions:

1.) What am I doing wrong here?
2.) Is there a way to format this so that I can use the table (my formatting skills are horrid) and still sum my points? I tried putting the following in my table footer and got nowhere with it...

<? php
$db =& JFactory::getDBO();
$query = "
    SELECT 'formCategory' SUM(`points_1`) AS total
        FROM '#__intra_chronoforms_data_wellnesstracker'
        GROUP BY 'formCategory' ;
";
$db->setQuery($query);
$totals = $db->loadObjectList();
foreach ( $totals as $t ) {
  echo "<p>{$t->formCategory} has $($t->total}</p>";
}
?>

Hey Max...

I wound up running this as a SQL query in the header, that looks like so:

<?php
$user = JFactory::getUser();
$db =& JFactory::getDBO();
global $col_total;
$query = "
SELECT `intra_chronoforms_data_wellnesstracker`.`formCategory`,
               `intra_chronoforms_data_wellnesstracker`.`cf_created_by`,
sum(points_1) AS total
FROM `intra_chronoforms_data_wellnesstracker`
Where cf_created_by=". $user->get("id")."
GROUP By formCategory;
               ";

$db->setQuery($query);
$totals = $db->loadObjectList();
foreach ( $totals as $t ) {
  echo "<p>{$t->formCategory} Total Points: {$t->total}</p>";
  $col_total += $t->total;
}
 echo "<p>Total Points: {$col_total}</p>";
?>

However, your code change worked in the custom tables too.

formCategory is the driving menu of a Dynamic Dropdown. The form is designed to track points for our Wellness program, and activities are divided up into categories.

Er, I meant to say, your code change worked in the custom display. >_<