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.
  • None of your queries actually neex the quoteName() calls. The replacement of Status with Container can easily be done in the query itself. I'm going to have to read your question a few more times to figure out what you need. Don't you need to be including an id in your WHERE clause while querying #__rsform_submission_values? Can you post an sql export for me to see the schema and sample data? Your query with implode doesn't work because you are not imploding with ','. It should be like "('" . implode("','", $cut) . "')" -- but I'm not posting that an answer -- it's not good. – mickmackusa Oct 11 at 9:56
  • HI Mick. Unfortunately that site is blocked for me. Could you provide it in "jsfiddle" perhaps? I don't really know how to simplify the answer any more, it would then be too long. Basically I have one column which stores all the values. It is basically a form I submit on the front end with values. All these values are stored in one column called "SubmissionValues" when a form is submitted. Each form has a "SubmissionId". So I need to find the VALUE of the FIELD named "Container", "ContainerType" etc. in the column WHERE the name and submissionID is equal to the "$cut" variable I created. – MailBlade Oct 11 at 10:50
  • I don't know why db-fiddle is blocked but it will be unblocked soon so you can please keep that one up – MailBlade Oct 11 at 10:56
  • My above query doesn't have SubmissionId in the WHERE clause, but I reckon it should for your actual project. – mickmackusa Oct 11 at 10:59
  • Ok thanks Mick I will try and include it in mine. The only error I get is a syntax query error. It's difficult to explain over text for something like this. – MailBlade Oct 11 at 11:03
up vote 2 down vote accepted

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)
  • Thank you so much mick! I will be testing it and will let you know. I added the code I think you want in my original post. Thank you so much for all your help and advice! I really appreciate it a lot :) – MailBlade Oct 11 at 12:17
  • Wow thanks a lot for the terrific answer Mick! I am pretty sure this will work, going to test it out now. (I know my original code is bad compared to this lol). I can't express my gratitude enough mick. As for the older questions, I've mostly solved those on my own through time. But thank you anyway! – MailBlade Oct 11 at 12:41
  • Hi Mick! Okay the query is working, however it is quite slow and loads quite a long time. Also, it is displaying in SQL format. Is there any way I can get it to display normally? So that it only shows the Container field value ? – MailBlade Oct 11 at 12:58
  • Nevermind, I figured out how to display in normal text. It is still loading extremely long though, I think it's because there is so many values. I'll need to remove some empty values from the table and see if it helps. – MailBlade Oct 11 at 14:15
  • 1
    Hi Mick! Happy to report the query is working and I figured out why it's so slow. I forgot to include the FormId, which is 28. How can I implement this into the->where(["b.FieldValue = 'Pending'", "b.FieldName LIKE 'Status%'"]); part of your code? I tried this: ->where(["b.FieldValue = 'Pending'", "b.FieldName LIKE 'Status%', FormId = '28'"]); It's not working though. – MailBlade Oct 13 at 11:42

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.

  • Thanks a lot for your answer Sharky, sorry for only replying now! I will also try this one – MailBlade Oct 13 at 6:01

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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