How to display aggregated data in the frontend

svenhaul
Topic Author
Offline

7 years 8 months ago #4079 by svenhaul

How to display aggregated data in the frontend was created by svenhaul

First: great extension, a million thanks!

Our non-profit site offers to sell tickets to our dance performances online, and we use visforms for this. Our volunteers in charge of the tickets wanted an overview (sum) of the sold tickets. I have not found a solution inside visforms, but with a little coding, I have built something myself and wanted to share this solution here.

You need to create a php module using one of the common extensions available for this task. I have used "Blank Module", but anything that allows to use PHP to render output would do. A menu entry points to a single article which includes the PHP module using core Joomla "loadmodule" functionality.

In the PHP module, I have placed the following code:

Code:

// get DB query object
$db=JFactory::getDBO();
$query=$db->getQuery(true);

// prepare query
$query->select($db->quoteName('f7').' as performance')
->select('sum('.$db->quoteName('f2').') as adults')
->select('sum('.$db->quoteName('f3').') as children')
->select('sum('.$db->quoteName('f6').') as handicapped')
->from($db->quoteName('jos_visforms_2'))
->group($db->quoteName('f7'));

// fetch data
$db->setQuery($query);
$result=$db->loadObjectList();

// prepare table output 
echo '<table class="slr-table">';
echo '<thead>';
echo '<tr><th>Performance</th><th>Adults</th><th>Children</th><th>Handicapped</th></tr>';
echo '</thead>';

// iterate over result set and output table rows
echo '<tbody>';
foreach ($result as $row) {
	echo '<tr>';
	echo '<td>'.$row->performance.'</td>';
	echo '<td>'.$row->adults.'</td>';
	echo '<td>'.$row->children.'</td>';
	echo '<td>'.$row->handicapped.'</td>';
	echo '</tr>';
}
echo '</tbody>';
// close table
echo '</table>';

I have used Adminer (PHPMyAdmin would do as well, or any other DB tool) to look into the table jos_visforms_2 to find out which columns hold my data. Since visforms uses generic column names f1, f2, ..., I have renamed the columns in the select statement above ("as xxx").
So the output is the sum of tickets, grouped by performance (date).

Thanks again for the excellent extension,
regards,
Sven

Administrator AV
Offline

7 years 8 months ago #4082 by Administrator AV

Replied by Administrator AV on topic How to display aggregated data in the frontend

Hi,

thanks a lot for sharing!!!

Regards,
Aicha

I recommend you the new and up-to-date documentation for Joomla 4:
docs.joomla-5.visforms.vi-solutions.de/en/docs/
Most of this also applies retrospectively to Joomla 3.
Please only ask 1 question per topic :-)

Ich empfehle Dir die neue und aktuelle Dokumentation für Joomla 4:
docs.joomla-5.visforms.vi-solutions.de/docs/
Das meiste gilt rückwirkend auch für Joomla 3.
Bitte immer nur 1 Frage pro Thema stellen :-)

Moderators: Administrator AV, Administrator IV

Joomla 5 Notice

Forum

How to display aggregated data in the frontend

Visforms Subscription

Visforms Extensions