0

I am using the latest RS Form Pro with Joomla 3.9, Platform 13.1.0.

My code is working properly (thank you guys!) But the data should be limited to each user. The user ID is is not in the table specified in the code so I assume that's why you get to see everyone's commissions instead of just yours. The table with the user info is jos_users. When you enable an RSform module you specify "login" as to show only the users submissions. Does that matter? How can I adjust this? Many thanks!

Here is the 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 = 46", 
        "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>";
}

SQL Query: 
10
  • We will need to better understand how you are relating/storing the user_id in your database.
    – mickmackusa
    Dec 30 '19 at 21:13
  • I'm not sure what you mean. The user id is assigned to each Joomla user when they register on the site. So when I publish a form, RSForm assigns the the Joomla user (user id) to the submission. The user id's are stored in another table called "jos_users." The script adds up all the commissions for all users. The form itself has no fields and displays information gathered from another form, "Form 46" so it's bypassing the logged in user I guess... I'm not sure how to proceed. I hope this makes sense.
    – kholt6
    Dec 30 '19 at 21:35
  • I am away on holidays, so I cannot access my project with RSForm data in it. With all due respect, it looks like you have simply dumped my last answer and added a new requirement. We want you to go on the development journey before posting a new question. Please research how to retrieve the current user's id, investigate how the user_id relates to the rsforms data in the database, and research how to join tables and relate data using a "pivot" (here's a hint: joomla.stackexchange.com/q/23860/12352). We want to help, but we want to see your effort first.
    – mickmackusa
    Dec 31 '19 at 0:27
  • I would like you to read this: meta.stackoverflow.com/a/333953/2943403 Without seeing the specifics, I have a blind guess that looks like this: 3v4l.org/OHRfV However this is not tested and I am not confident about the result. If you can offer some sample data or feedback from my suggestion, I may be able to post an answer.
    – mickmackusa
    Dec 31 '19 at 1:52
  • I will read it! Because the user id is not in the same table I created a new field in my form to capture the user id and enter it in the table. It is called "Member". It worked. Now I have the user ID in the same table. I have been trying to figure out how to query it from Phpmy admin but because I am not the logged in user it doesnt make sense to me. That's how I got stuck.
    – kholt6
    Dec 31 '19 at 17:34

Your Answer

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

Browse other questions tagged or ask your own question.