Looping through Column and using values in new query?

[mailblade]

Good day,

I have been struggling with a query issue for some time now.

I need two queries which have to execute; the second one retrieving information based on the first query result.

When I submit this form, you can have up to ten rows of information and 5 columns. So they are numbered as "Status1", "Container1", "ContainerType1", "ExpDate1" and "Commodity1"; this goes all the way up to the number 10. There is a also a "SubmissionId" linked to each submission of the form naturally.

Firstly, I am obtaining data from the database, searching through the "Status" fields where it has a value of "Pending". I will get a result of "Status1" IF the corresponding "FieldValue" for "Status1" IS "Pending". So if I get a result of "Pending" for "Status1", I know that I need to display the other information for this container as well (the fields mentioned in above paragraph).

However, what makes it difficult is that in the database, the column which stores the "SubmissionValues" for each are ALL IN ONE COLUMN in the table "jos_rsform_submission_values". The column is literally named "SubmissionValues".

I will post this code below for retrieving pending statuses, it is quite straightforward:

Code: Select all

<?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 - Container10". 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:

Code: Select all

<?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.

To shorten it a bit:

• 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)

• Instead of "Status1" etc. 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.

I apologise if the question is too long and I thank you for reading through it. I have tried many different ways and I would really appreciate help on this.