Trying to display mysql values properly in php

Question

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 />";
}

mickmackusa · Accepted Answer · 2019-12-22 20:37:46Z

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")
    ->from("#__rsform_submission_values")
    ->where([
        "FormId = 99",
        "FieldName IN (" . implode(',', $db->q($months)) . ")"
    ])
    ->group("FieldName");

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

try {
    $db->setQuery($query);
    $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>";
}

Robbie Jackson · Accepted Answer · 2019-12-22 20:35:59Z

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

Stack Exchange Network

Trying to display mysql values properly in php

2 Answers 2

Your Answer

Hot Network Questions

Trying to display mysql values properly in php

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions