1

I have mysql query:

// Get a db connection.
$db = JFactory::getDbo();

// Create a new query object.
$query = $db->getQuery(true);

$formId = 21;

$query = "select * from #__rsform_submission_values WHERE `FormId` = $formId order by `SubmissionId` desc limit 30";

$db->setQuery($query);
$results = $db->loadObjectList();

It loads specific data from above table (see screenshot)

mysql query

what I want is to load data like:

  0 => 
    object(stdClass)[427]
      public 'SubmissionValueId' => string '20006' (length=5)
      public 'FormId' => string '21' (length=2)
      public 'SubmissionId' => string '1122' (length=4)
      public 'FieldName' => string 'session_date' (length=12)
      public 'session_date' => string '17-03-2022 20:00' (length=16)
  1 => 
    object(stdClass)[426]
      public 'SubmissionValueId' => string '20007' (length=5)
      public 'FormId' => string '21' (length=2)
      public 'SubmissionId' => string '1122' (length=4)
      public 'FieldName' => string 'city' (length=4)
      public 'city' => string 'Hørsholm Kommune' (length=17)

so instead of FielValue = example it should be called session_date = example (replace all FieldValue to WITH FieldNames)

1 Answer 1

1

I really can't see the use in retrieving the SubmissionValueIds or FormIds associated with the rows of data after you pivot into an associative structure. When you fold the original rows together, you are going to lose the relationship to the original values anyhow.

If you already know all of the FieldName values that you are going to processing, you can most simply use a pivot technique -- here are MANY demonstrations that I've posted. From what I see in your resultset screenshot, you want to capture the data for session_date, city, group, and status. I don't see any use for submit or formId -- in fact this redundant storage is unnecessarily bloating a table that is notoriously prone to becoming VERY bloated. I recommend adjusting your RSForm form so that the submit and formId values are not stored in the database table at all. With my current understanding of your requirement, this should do:

$query = $db->getQuery(true)
    ->select([
        "a.SubmissionId",
        "a.DateSubmitted",
        "MAX(CASE WHEN b.FieldName = 'status' THEN b.FieldValue ELSE NULL END) AS status",
        "MAX(CASE WHEN b.FieldName = 'session_date' THEN b.FieldValue ELSE NULL END) AS session_date",
        "MAX(CASE WHEN b.FieldName = 'city' THEN b.FieldValue ELSE NULL END) AS city",
        "MAX(CASE WHEN b.FieldName = 'group' THEN b.FieldValue ELSE NULL END) AS " . $db->qn('group')
    ])
    ->from("#__rsform_submissions a")
    ->innerJoin("#__rsform_submission_values b ON a.SubmissionId = b.SubmissionId")
    ->where("a.FormId = " . (int)$formId)
    ->group("a.SubmissionId DESC");  // yes, you can declare the sort order here

// echo $query->dump();  // uncomment if you want to confirm the rendered query
try {
    $db->setQuery($query);
    $result = $db->loadObjectList();
} catch (Exception $e) {
    JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');  // never show getMessage() to public / in production
}

If you need your script to remain fully dynamic and you don't want to perform an earlier query on the table to collect all of the qualifying FieldName values to set up a dynamic pivot query, then you can just iterate over your result set and do the grouping/pivoting in PHP.

$result = [];
foreach ($db->loadObjectList() as $obj) {
    $result[$obj->SubmissionId][$obj->FieldName] = $obj->FieldValue;
}
return $result;
7
  • Thank you for your answer! it is so helpfull, but syntax is not working I got error. maybe it is mysql server version
    – Gocha
    Mar 15 at 12:18
  • your MySQL server version for the right syntax to use near 'group FROM #__rsform_submissions a INNER JOIN #__rsform_submission_values ' at line 1
    – Gocha
    Mar 15 at 12:23
  • 1
    Ah, group is a reserved keyword in mysql. It would have been better to not use this word as a column name, but you just need to properly quote it. dev.mysql.com/doc/refman/8.0/en/…
    – mickmackusa
    Mar 15 at 12:33
  • Now everything works perfectly, just "a.status", is not in a right place, there is no that field in rsform_submissions. just because I am sure this answer will be usefull for many people.
    – Gocha
    Mar 15 at 12:38
  • 1
    You use HAVING. joomla.stackexchange.com/a/24320/12352 as in ->having("MAX(CASE WHEN b.FieldName = " . $db->q("status") . " THEN b.FieldValue ELSE NULL END) != " . $db->q("booked"));
    – mickmackusa
    Mar 15 at 12:50

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.