Show data on frontend

Hi,

I'm testing CC. I've got the following question. Is it possible to show a group by and a sum on the frontend because i can't get it to work. I've got a table thats looks like:

id,member,amount
1,Peter,10
2,John,15
3,Peter,5
4,Peter,25
5,Chris,10
6,Vince,45

Now i want to show this on the frontend like this:

member,amount
Peter,40
John,15
Chris,10
Vince,45

If possible please some example code. Thanks!

Regards,

Wes

P.S. When i use only a 'GROUP BY member' in the WHERE clause it only shows 2 (Peter and John), the rest isn't there???

Hi,

I've already tried the following, for the first step (GROUP BY)

<?php
$db = JFactory::getDBO();
$query = "SELECT members FROM `jos_chronoforms_Sponsors` GROUP BY member";
$db->setQuery($query);
$rows = $db->loadObjectList();
echo $db->getErrorMsg();
 {
echo "$row->member<br>";
}
?>

It seems the GROUP BY function isn't working because i get the following result:

Peter
John
Peter
Peter
Chris
Vince

What am i doing wrong (when i use the statement in PHPmyAdmin it works fine)?

Regards,

Wes

Hi wes, I really have no idea why this doesn't work for you! as per your 2nd post, the statement doesn't work with normal in the PHP and so its not a CC problem! keep testing and you may find it yourself!

Regards
Max

Hi Max,

Thanks for the reply. The statement that i've showed before had some incorrect fieldnames (trying to do too many things at te same time). I will try to get more info on the php part. I haven't got a clue for now.

Regards,

Wes

Max,

I got it working in PHP, but when i use the code in CC it gives the result for every entrie in the table.

<?php
$my_connection = mysql_connect('localhost', 'test', 'test');

if (!$my_connection){
	die('Could not connect: ' . mysql_error());
}

echo 'Connected successfully' . '<br><br>';

mysql_select_db('test');


$query = "SELECT *, SUM(amount) AS amount1 FROM jos_chronoforms_Sponsors GROUP BY member";
$result = mysql_query($query);
$my_rows;

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
  $my_rows = $my_rows . "member : {$row['member']}<br>" . "amount :{$row['amount1']} <br><br>";
}

mysql_close($my_connection);


?>

In php after a echo the result is:

Peter,40
John,15
Chris,10
Vince,45

In CC after a echo in the body field (code above was placed in the header)the result is 6 times the results of the row above. Does CC expect something to be different?

Regards,

Wes

Hi Wes,

Its not a good idea to make new mysql connection or write all the regular PHP mysql code inside Joomla because Joomla codebase already does alot of this, your code also doesn't get use of any of the CC features and if you converted it to the Joomla code version then you can use it with a Chronoform instead, did you check the CC tutorial from the home page ? give me more idea also about what are you trying to do ?

Regards
Max

Hi Max,

I know it's not the best option and in this case i don't use anything of Joomla or CC. What i'm trying to achieve is to create an overview (i hope this is the right word in english) which collects data from a table and groups this data by a specific column. In this table there are multiple entries therefore i want to group.

Regards,

Wesley

P.S. In the screenshot i already added a SUM to the sql statement. But it's the kind of view i want to create.

Hi Wesley,

you can;t edit the whole SELECT statement in CC, so you can use your SQL above with the Joomla PHP code inside a Chronoform!

Regards
Max

Hi Max,

Thanks for the tip, i'm going to test it.

Regards,

Wes

I am trying to do something very similar, I have a table that is linked to a form that people log in their hours for a club they belong to.
When people are logged in they want to be able to see the date/activity and hours that they have spent so far.

The thing is that I want to be able to have a sum at the bottom of the table for the individual, not the entire group.

I am using this

$db->setQuery("SELECT Atime, SUM(Atime) as 'time1' FROM `jos_chronoforms_ActivityHours` group by 'cf_user_id' ");
$result = $db->loadObject();
$time = $result->time1;

The output is the total hours, not the individual, any idea how to only display the individual's hours? If I use a WHERE statement I get an error.

thanks

Hi Horace,

If I use a WHERE statement I get an error.

I think you put the WHERE in the wrong place, it must be before the "group"!

Regards
Max

You sir...are now my hero!

I knew it was something simple.

$db->setQuery("SELECT Atime, SUM(Atime) as 'time1' FROM `jos_chronoforms_ActivityHours` WHERE cf_user_id='{$user->id}' group by 'cf_user_id' ");