How to query certain rows from a table based on other associated rows in the same table?

Question

I'm trying to query my jos_rsform_submission_values table for FieldValue data in rows with a FieldName of Container# which shares a SubmissionId with another row where the FieldValue is "Pending" and the FieldName is "Status#" and the # after "Status" = the # after "Container".

Here is some sample table data, so that I can better express what I'm after:

CREATE TABLE `jos_rsform_submission_values` (
  `SubmissionValueId` int(11) NOT NULL,
  `FormId` int(11) NOT NULL,
  `SubmissionId` int(11) NOT NULL DEFAULT '0',
  `FieldName` text NOT NULL,
  `FieldValue` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `jos_rsform_submission_values` (`SubmissionValueId`, `FormId`, `SubmissionId`, `FieldName`, `FieldValue`) VALUES
(129862, 28, 548, 'creationdate', '27-08-2018 10:30 AM'),
(129863, 28, 548, 'CustomerAccount', 'TEST'),
(129864, 28, 548, 'otheraccount', ''),
(129865, 28, 548, 'salesorderno', 'SO-00006296'),
(129866, 28, 548, 'DocumentUpload', '-'),
(129867, 28, 548, 'numberofbayan', '1'),
(129868, 28, 548, 'BayanNo', '189426'),
(129869, 28, 548, 'portofloading', 'Seaport'),
(129870, 28, 548, 'containers', '3'),
(129871, 28, 548, 'Container1', 'TTNU8517512'),
(129872, 28, 548, 'CargoDescription1', 'Chocolate'),
(129873, 28, 548, 'containertype1', 'Reefer'),
(129874, 28, 548, 'purchaseorder1', ''),
(129875, 28, 548, 'Location1', 'Jeddah'),
(129876, 28, 548, 'Status1', 'Return Empty'),
(129877, 28, 548, 'requesteddate1', '18-08-2018 07:00 AM'),
(129878, 28, 548, 'Container2', 'TCLU1256192'),
(129879, 28, 548, 'CargoDescription2', 'Chocolate'),
(129880, 28, 548, 'containertype2', 'Reefer'),
(129881, 28, 548, 'purchaseorder2', ''),
(129882, 28, 548, 'Location2', 'Jeddah'),
(129883, 28, 548, 'Status2', 'Return Empty'),
(129884, 28, 548, 'requesteddate2', '18-08-2018 07:00 AM'),
(129885, 28, 548, 'Container3', 'KKFU6780793'),
(129886, 28, 548, 'CargoDescription3', 'Chocolate'),
(129887, 28, 548, 'containertype3', 'Reefer'),
(129888, 28, 548, 'purchaseorder3', ''),
(129889, 28, 548, 'Location3', 'Jeddah'),
(129890, 28, 548, 'Status3', 'Pending'),
(129891, 28, 548, 'requesteddate3', '18-08-2018 07:00 AM'),
(129892, 28, 548, 'Container4', ''),
(129893, 28, 548, 'CargoDescription4', ''),
(129894, 28, 548, 'containertype4', ' '),
(129895, 28, 548, 'purchaseorder4', ''),
(129896, 28, 548, 'Location4', '-'),
(129897, 28, 548, 'Status4', ' '),
(129898, 28, 548, 'requesteddate4', ''),
(129899, 28, 548, 'Container5', ''),
(129900, 28, 548, 'CargoDescription5', ''),
(129901, 28, 548, 'containertype5', ' '),
(129902, 28, 548, 'purchaseorder5', ''),
(129903, 28, 548, 'Location5', '-'),
(129904, 28, 548, 'Status5', ' '),
(129905, 28, 548, 'requesteddate5', '')

ALTER TABLE `jos_rsform_submission_values`
  ADD PRIMARY KEY (`SubmissionValueId`),
  ADD KEY `FormId` (`FormId`),
  ADD KEY `SubmissionId` (`SubmissionId`),
  ADD KEY `SubmissionId_2` (`SubmissionId`),
  ADD KEY `SubmissionId_3` (`SubmissionId`);

ALTER TABLE `jos_rsform_submission_values`
  MODIFY `SubmissionValueId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=133092;

From the above, only SubmissionId 548 has a Status3 value of Pending.

## SubmissionId    FieldName    FieldValue
## -----------------------------------------
##     548         Status1      Return Empty
##     548         Status2      Return Empty
##     548         Status3      Pending
##     548         Status4      
##     548         Status5

I need to find the corresponding Container# value:

## SubmissionId    FieldName    FieldValue
## ----------------------------------------
##     548         Container3   KKFU6780793

Additional details:

Regarding my submitted data, the FieldName values will begin with one of five strings: "Status", "Container", "ContainerType", "ExpDate" and "Commodity" followed by a number starting from 1 but may be as high as 10.

My coding attempt:

<?php 
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('FieldName')));
$query->from($db->quoteName('#__rsform_submission_values'));
$query->where($db->quoteName('FieldName') . ' IN ("Status1","Status2","Status3","Status4" ,"Status5")');
$query->where($db->quoteName('FieldValue') . ' = "Pending"');

$db->setQuery($query);
$result = $db->loadColumn();

foreach($result as $value) {
        foreach($value as $key => $data) {
         $cut = substr($data, 6);
         $cut = "Container".$cut;
     }
}
?>

You will notice I used substr to remove the first 6 letters (Status) from each result. This is to only get the remaining number (either 1, 2, 3, 4 etc. depending on which status is "Pending"). I also then added "Container" to the front of each of the remaining numbers, since the "FieldValue" which I am looking for is "Container1 up to Container10" (depending on which field it is). Remember it is only showing where the status is "Pending", so it could be one result or 10 results which is retrieved.

So I thought the easiest way was to simply add the required prefix to each array element; then I select the "FieldValue" from the "SubmissionValue" column where the "FieldValue" is equal to the "FieldName" of one of the results obtained in the previous query.

Here is the code for this:

<?php
$query = $db->getQuery(true);
$query->select($db->quoteName(array('FieldValue')));
$query->from($db->quoteName('#__rsform_submission_values'));
$query->where($db->quoteName('FieldName') . ' IN ' . '(' . implode(',', $cut) . ')');

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

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

I have tried using "loadRow , loadColumn, loadResult" etc. and none of them work. It's saying I have a syntax error (1064) when I load the page.

I am not sure if it is a "SubmissionId" problem. I don't know how to incorporate the SubmissionId into this query though, since the substr will simply remove it in the first query, and then add the prefix in front of it, which it should not do.

In other words:

• Query 1 retrieves all "Status" fields where the value is "Pending".
• I remove the "Status" from these fields to only have the number remaining.
• I then add a prefix to these numbers, depending on the field (which are listed above in a paragraph)
• I now have "Container1" as a result (depending on which number it is; 1 - 10).
• I then need to find the "FieldValue" for each of the results.
• I try and use the $cut value in an implode array, but it is not working.

Answer 1

It is best practice to minimize the number of calls to the database.

Your logic in your two queries can be merged into one like this:

try {
    $db = JFactory::getDbo();
    $query = $db->getQuery(true)
                ->select("a.FieldName, a.FieldValue")
                ->from("#__rsform_submission_values a")
                ->innerJoin("#__rsform_submission_values b ON a.SubmissionId = b.SubmissionId AND a.FieldName = REPLACE(b.FieldName, 'Status', 'Container')")
                ->where(["b.FieldValue = 'Pending'", "b.FieldName LIKE 'Status%'"]);
    $db->setQuery($query);
    echo $query->dump();    // never show to public
    if (!$result = $db->loadAssocList()) {
        echo "No rows found";
    } else {
        echo "<pre>";
            var_export($result);
        echo "</pre>";
    }
} catch (Exception $e) {
    JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');    // never show to public
}

Output:

SELECT a.FieldName, a.FieldValue
FROM jos_rsform_submission_values a
INNER JOIN jos_rsform_submission_values b ON a.SubmissionId = b.SubmissionId AND a.FieldName = REPLACE(b.FieldName, 'Status', 'Container')
WHERE b.FieldValue = 'Pending' AND b.FieldName LIKE 'Status%'

array (
  0 => 
  array (
    'FieldName' => 'Container3',
    'FieldValue' => 'KKFU6780793',
  ),
)

By JOINing the table to itself and stipulating that you are only interested in SubmissionId data that is associated with "Status# = 'Pending'", you can avoid two trips to the database.

---

As for your original snippets... (which I don't recommend that you try to salvage)

You were overwriting your $cut variable in the loop (not pushing elements into a $cut array. It should have been something like:

foreach($result as $value) {
     $cut[] = "Container" . substr($value, 6);
}

Then in the next query, you need to single quote wrap your items in IN.

"... IN ('" . implode("','", $cut) . "')"

---

About performance, there are several different ways to write the query that will provide the desired result, but I don't know just how many rows you have in your project to start an accurate benchmarking test. (and I don't have time to offer this service right now anyhow)

Here is another query that should perform well:

SELECT a.SubmissionId, a.FieldName AS ContainerName, a.FieldValue AS ContainerValue
FROM jos_rsform_submission_values a
INNER JOIN (
    SELECT SubmissionId, SUBSTRING(FieldName, 7) AS StatusNum
    FROM jos_rsform_submission_values
    WHERE FieldValue = 'Pending'
      AND FieldName LIKE 'Status%'
) b ON a.SubmissionId = b.SubmissionId AND a.FieldName = CONCAT('Container', b.StatusNum)

Answer 2

Assuming this is the complete code and the second code snippet goes right after the first one:

$db->loadColumn() returns a simple array but you're running nested foreach loops which, if you had strict error reporting enabled, would result in a warning:

Warning: Invalid argument supplied for foreach()

Because of this, the $cut variable is NULL. You also need to escape strings with $db->quote().

In your first snippet change the foreach loops to this:

foreach($result as $key => $value) {
    $result[$key] = $db->quote('Container' . substr($value, 6));
}

And then pass $result instead of $cut to implode() in the second snippet.