I am trying to display my commissions for each month. I want to display all 12 months with a total for each month. I've started with just January but this is as close as I have come to get to what I want. I am using Joomla 3.9, Platform 13.1.0.

I have 2 records with 255 in Jan for each one so I am getting the right sum.

This is the result I am getting with my php code:

FieldName: jan

SUM(FieldValue): 510

I just want it to show:

jan 510

Here is my code:

$db = JFactory::getDBO();

$query = "SELECT FieldName, SUM(FieldValue) FROM jos_rsform_submission_values WHERE FieldName=\"Jan\"';

$db -> setQuery($query);

$results = $db -> loadObjectList();

foreach($results as $value){
    foreach($value as $key => $data){
        echo $key.': '.$data."<br />";
    echo "<br />";

Start by hardcoding a complete array of three-letter months. Then create an array with those months as keys with zero values for all.

Then query your database for existing amounts. You are going to want to have a way of isolating year-specific data as the years go by. At the very least, you should be filtering on FormId so that you are not accidentally including unrelated data from the same table. (I'll add 99 in my demonstration.)

After you query, add the new found amounts to your zero amounts for any found months.

After all of the calculations are done, loop through your prepared data to display the months and their totals.

Without listing all of the months, you will only see the months that are present in the database table. This solution will consistently deliver 12 months.

Note: Never show $query->dump() or $e->getMessage() to the public! This is purely to help you diagnose any coding mistakes while developing from my script. Once your script is working, comment the two JFactory::getApplication()->enqueueMessage() lines out.

Tested Code:

$months = ['jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov', 'dec'];
$monthlySums = array_fill_keys($months, 0);

$db = JFactory::getDBO();
$query = $db->getQuery(true)
    ->select("FieldName, SUM(FieldValue) AS Sum")
        "FormId = 99",
        "FieldName IN (" . implode(',', $db->q($months)) . ")"

JFactory::getApplication()->enqueueMessage($query->dump(), 'info');  // never show dump() to public

try {
    $objects = $db->loadObjectList();
    foreach ($objects as $object) {
        $monthlySums[$object->FieldName] += $object->Sum;
} catch (Exception $e) {
    JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');  // never show getMessage() to public

foreach ($monthlySums as $month => $sum) {
    echo "<div>{$month}: {$sum}</div>";
| improve this answer | |

I'm assuming that you have the month name in FieldName and the commission values in FieldValue. When you do a SQL COUNT or SUM over subsets of records like this you have to have a GROUP BY clause.

Also loadObjectList() will return an array of objects, and for each object the object property matches the columns you're selecting.

So try this:

$db = JFactory::getDBO();

$query = "SELECT FieldName AS `month`, SUM(FieldValue) AS `commission` FROM jos_rsform_submission_values GROUP BY FieldName";

$db -> setQuery($query);

$results = $db -> loadObjectList();
foreach($results as $row)
    echo $row->month . ': ' . $row->commission . '<br>';

See https://docs.joomla.org/Selecting_data_using_JDatabase

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.