-1

I was wondering if anyone could help me JOIN multiple queries into one query. Currently, these 2 blocks of code retrieve information from the DB. I am using "Sourcerer" plugin to add custom PHP code in my custom module.

However, the full query is much too long, and I need to reduce the size considerably. After a certain amount of queries, the page just doesn't load at all.

I will post two queries I need to have joined as an example.

<?php 
    $db = JFactory::getDbo();
    $db->getQuery(true);

    $db->setQuery("SELECT `SubmissionId`, `FieldValue`, `FormId` FROM `jos_rsform_submission_values` WHERE `FieldName` = \"Status1\" AND `FieldValue` = \"Pending\" AND `FormId` = 28");
    $results = $db->loadColumn();


    $db->getQuery(true);
    $db->setQuery("SELECT `FieldValue` FROM `jos_rsform_submission_values` WHERE `FieldName` = \"Container1\" AND `SubmissionId` IN ('".implode("', '", $results)."') ORDER BY `jos_rsform_submission_values`.`SubmissionId`"); 

    $result = $db->loadObjectList();

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



<?php 
    $db = JFactory::getDbo();
    $db->getQuery(true);

    $db->setQuery("SELECT `SubmissionId`, `FieldValue` FROM `jos_rsform_submission_values` WHERE `FieldName` = \"Status1\" AND `FieldValue` = \"Pending\" AND `FormId` = 28");
    $results = $db->loadColumn();


    $db->getQuery(true);
    $db->setQuery("SELECT `FieldValue` FROM `jos_rsform_submission_values` WHERE `FieldName` = \"containertype1\" AND `SubmissionId` IN ('".implode("', '", $results)."') ORDER BY `jos_rsform_submission_values`.`SubmissionId`"); 
    $result = $db->loadObjectList();

    foreach($result as $value) {
            foreach($value as $key => $data) { 
              echo $data."<br />"; 
           }
       }
?>
  • Your question despite receiving an accepted answer is: Unclear what you are asking. You are vague/ambiguous about your JOIN logic. You say that you need your 2 queries to become 1 despite actually providing 4 queries. Your accepted answer is misleading because it provides 1 query, but actually requires 2 queries to generate the (1,2,3). A good question never needs researchers to read the answer to understand the question. In its current state, this entire page is "low-value". – mickmackusa Jun 21 '18 at 22:29
  • Please improve your question by offering some sample data (sql export dump) from your table, a realistic collection of queries that will be ready to use in your project, and your exact desired resultset from your minimal sample data. This will help you to receive the best possible support, help volunteers to develop and test their answers, and help researchers to understand the issue. Q&A is not a 1-to-1 interaction, it is a 1-to-many interaction where the goal is not only to solve problems but to also educate. – mickmackusa Jun 21 '18 at 22:29
  • I know this is two months late, but can you improve your question so that I can help you to find a clean, direct, effective solution? I would much rather remove my downvote and offer an upvote, but your question doesn't merit those acts yet. – mickmackusa Jul 22 '18 at 5:54
  • I'll try and write more details for the question. I literally just need to put those 2 queries into one query. That is all. I'll add some more details though. – MailBlade Jul 25 '18 at 12:59
  • Please leave me a comment after you update your question so that I know to come back and have a look. – mickmackusa Jul 31 '18 at 5:36
2

Here is what I believe the mySQL query to be with (1,2,3) substituted in for your imploding of results because I wanted to check the mySql syntax of it. I don't have time to convert into PHP right now, but wanted to try to help. If it doesn't work, let me know in comments and I will try to adjust:

SELECT a.FieldValue as Container, b.FieldValue as ContainerType FROM ( SELECT FieldValue FROM jos_rsform_submission_values WHERE FieldName = "Container1" AND SubmissionId IN (1,2,3)) as a LEFT JOIN ( SELECT FieldValue FROM jos_rsform_submission_values WHERE FieldName = "containertype1" AND SubmissionId IN (1,2,3)) as b on a.SubmissionId = b.SubmissionId ORDER BY a.SubmissionId;

I had to remove the tick marks to get StackExchange to format correctly, but I would suggest converting your code to use $db->quote and $dbquotename instead anyway: https://docs.joomla.org/Selecting_data_using_JDatabase
https://docs.joomla.org/Secure_coding_guidelines

  • Thank you so much for your reply mate!! I will give it a go and let you know :) – MailBlade May 31 '18 at 10:52
  • I know this is 2 months late, but I was wondering if you could post the PHP code for this? – MailBlade Jul 17 '18 at 12:57
  • I'm pretty slammed this week but will take a look when I get some time. – YellowWebMonkey Jul 18 '18 at 18:19

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.