Use added-prefix array values in new query to obtain database values?

by MailBlade   Last Updated October 11, 2018 10:10 AM

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:

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

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)


   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.

Tags : php database sql


Related Questions




How to edit the text string "Contact Form"

Updated November 22, 2017 01:10 AM

Why int(11) on database integer columns?

Updated August 05, 2015 13:04 PM

Joomla SQL drivers and prepared statements

Updated August 11, 2015 17:04 PM