0

I'm running a query on phpmyadmin which has the "IN" function included. It is to extract data from the same column for different fields' (Waybill1, Waybill2 etc.)

I shall post the SQL query below:

SELECT FieldValue, 
       SubmissionID 
FROM jos_rsform_submission_values 
WHERE FieldName IN ('Waybill1', 'Waybill2', 'Waybill3', 'Waybill4', 'Waybill5') 
   AND FieldValue != "" AND FormId = 28 
ORDER BY SubmissionId ASC

Now, after I convert it to PHP I get this code:

$sql = "SELECT FieldValue,\n"

    . "       SubmissionID\n"

    . "FROM   jos_rsform_submission_values\n"

    . "WHERE  FieldName IN (\'Waybill1\', \'Waybill2\', \'Waybill3\', \'Waybill4\', \'Waybill5\')\n"

    . "   AND FieldValue != \"\" AND FormId = 28\n"

    . "ORDER BY SubmissionId ASC";

The thing is now that I do not know how to convert it to Joomla PHP syntax. I will post what I've done but it is clearly wrong as the page isn't even loading:

<?php 

$db = JFactory::getDbo();
$db->getQuery(true);
$db->setQuery("SELECT `SubmissionId`, `FieldValue`, FROM `jos_rsform_submission_values` WHERE `FieldName` IN (\"Waybill1\", \"Waybill2\", \"Waybill3\", \"Waybill4\", \"Waybill5\") AND `FieldValue` != \"\" AND `FormId` = 28");

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

    }
?>
?>

I would greatly appreciate any pointers on this issue. The Joomla syntax is sometimes confusing and I'm not 100% sure how to do advanced functions such as this (I have tried many different approaches and combinations for trying to solve this, but none have worked. I just can't get the syntax right/know what syntax to use)

2

As @FFrewin noted, you've got a syntax issue with the extra comma before the FROM block. However, there's a few other things I'd recommend (this is untested however!):

$query->select($db->quoteName(array('SubmissionId', 'FieldValue')));
$query->from($db->quoteName('#__rsform_submission_values'));
$query->where($db->quoteName('FieldName') . ' IN ("Waybill1","Waybill2","Waybill3","Waybill4")');
$query->where($db->quoteName('FieldValue') . ' != ""');
$query->where($db->quoteName('FieldId') . '=' . $db->quote(28));

You could chain these if you like, I just did this to make it clear like they do in the examples in https://docs.joomla.org/Selecting_data_using_JDatabase

IF the parameters of your IN clause are user-input supplied, I'd recommend using $db->quote on those values as well.

Also-- when you're developing turn on error reporting to Development which will print errors to the page instead of letting it fail silently and showing you a blank page.

  • Thank you so much @user101289!! This is absolutely perfect and does the job as required! I was struggling to figure out how I would chain the query, but now I can clearly see how it's done. I wasn't sure if you could add more than one "where" query, and I also didn't know how to implement it. I really appreciate the terrific help :) – MailBlade Aug 2 '18 at 6:02
  • I was wondering if you could please help me with one more piece of code: I would like to implode results, and I will post the code here: $query->select($db->quoteName(array('FieldValue'))); $query->from($db->quoteName('#__rsform_submission_values')); $query->where($db->quoteName('FieldName') . ' IN ("Container1","Container2","Container3","Container4")'); $query->where($db->quoteName('SubmissionID') . IN . (''.implode("', '", $result).'')); $db->setQuery($query); – MailBlade Aug 2 '18 at 11:01
  • it's hard to read in a comment but I'd recommend imploding before the query, just to keep it clean. Then you can use the normal $db->quote method on the result – user101289 Aug 3 '18 at 17:53
  • For the record, $db->quote() on the imploded string will not single-quote-wrap the individual values inside of IN's parenthetical list. – mickmackusa Aug 3 '18 at 20:39
  • @mickmackusa - that's true. The follow up should have been a separate question / answer – user101289 Aug 3 '18 at 20:44
1

There is not something really special regarding Joomla PHP syntax. Most likely you have a common syntax error somewhere, either on your PHP, or on your Query.

I haven't gone thoroughly through your code, but I already spotted a such error, in your above SELECT query. You use a comma just before the FROM statement. This definitely won't go on in MySQL. You may have more later on (but didn't check it as I said).

Generally, you should enable error reporting and debug, so you have some further info about the errors.

Also, have you read this documentation page: Selecting Data Using JDatabase. It contains many examples on constructing queries.

Finally do note, that you shouldn't use the actual table prefix hardcoded, as the table prefix is something that can change. Replace jos_ with #__ and Joomla will always use the correct one.

  • Thank you for your answer, I really appreciate it :) I will take note of your advice and proceed and see what I can do. Cheers – MailBlade Aug 1 '18 at 12:07
1

Let's not only solve your issue, but teach you how you can solve your own querying issues in the future. After all, if we need to fix every Joomla hiccup that you encounter, then you aren't acting very much like a "developer", your work will come to a grinding halt every time a problem arises, and this community will need to volunteer their time to write your code (which I assume that you are being paid/compensated for).

Most importantly, use a try {} catch {} block to help you to debug your query. If you would have written this:

$db = JFactory::getDBO();
try {
    $db->setQuery("SELECT `SubmissionId`, `FieldValue`, FROM `jos_rsform_submission_values` WHERE `FieldName` IN (\"Waybill1\", \"Waybill2\", \"Waybill3\", \"Waybill4\", \"Waybill5\") AND `FieldValue` != \"\" AND `FormId` = 28");
    $db->execute();
    var_export($db->loadObjectList);
} catch (Exception $e) {
    echo  "Syntax Error: " , $e->getMessage();  // only display getMessage() where not available to the public
}

You would have been presented with:

Syntax Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM `jos_rsform_submission_values` WHERE `FieldName` IN ("Waybill1", "Waybill2"' at line 1

This indicates that there was a syntax error just before the FROM clause. Then you could have compared your query to any other SELECT query on this site or tutorial or manual online to quickly determine that the comma is to blame.

Based on your follow up comment:

I was wondering if you could please help me with one more piece of code: I would like to implode results, and I will post the code here:

$query->select($db->quoteName(array('FieldValue')));
$query->from($db->quoteName('#__rsform_submission_values'));
$query->where($db->quoteName('FieldName') . ' IN ("Container1","Container2","Container3","Container4")');
$query->where($db->quoteName('SubmissionID') . IN . (''.implode("','", $result).''));
$db->setQuery($query);
  1. You are using table and column quoting methods that are not necessary for security/stability/accuracy. Want to know when they are necessary? Read this.
  2. You are feeding the select() an array when a string will do.
  3. You are wrapping your FieldName values in double quotes when single quotes are preferred / more commonplace.
  4. Your second where() call is treating IN as a constant, which is not right, and you are using too many single quotes on both sides of implode().

Here's what I recommend:

$result = [1, 2];  // fake data
$db = JFactory::getDBO();
try {
    $query = $db->getQuery(true)
        ->select('FieldValue')
        ->from('#__rsform_submission_values')
        ->where([
            "FieldName IN ('Container1','Container2','Container3',Container4')",
            "SubmissionID IN (" . implode(',', $result) . ")"
          ]);
    echo $query->dump();                       // never show this to the public
    $resultset = $db->loadObjectList();
    if (!$resultset) {
        echo "No Rows In Result Set";
    } else {
        var_export($resultset);
    }
} catch (Exception $e) {
    echo  "Syntax Error: " , $e->getMessage(); // never show this to the public
}

This will display your rendered query:

SELECT FieldValue
FROM jos_rsform_submission_values
WHERE FieldName IN ('Container1','Container2','Container3',Container4') AND SubmissionID IN (1,2)

and either an error message, an indication of a null resultset, or your multidimensional resultset.

Notice that the imploded SubmissionID values are not quote wrapped. I am doing this because it is fair to assume that these values are integers. If these values are not integers or they are from an untrusted source (like a user-submitted form), then please read the advice at the bottom of this answer.

  • Thank you for your very helpful post. I agree with your first paragraph. I am trying to learn but I was thrust into this position and didn't really do that much coding in my college, it was more on the technical side. I have also solved a lot of issues on my own by searching answers on Stackoverflow and Joomla Forums. These are just new problems I encounter and I have literally never had to deal with this. I'm very thankful for the help and support :) That is also one issue I have. You explained the post and WHY it was not working, as well as what your function would do. Thanks a lot! – MailBlade Aug 4 '18 at 6:33
  • Happy to help, because helping you helps me to learn too. Please do me a favor and improve your "4 queries into 1 via Sourcerer" question. – mickmackusa Aug 4 '18 at 12:10
  • That question has been solved actually by the answers in this thread, since I now know how I would need to go about combining the two different queries :) I was struggling with the correct syntax, but it's all working now! I have combined them using the same structure /u/YellowWebMonkey provided in that thread. (If that is the thread you're referring to )Thanks again for all your help! – MailBlade Aug 5 '18 at 5:49
  • In that case, please take the time to re-craft your question so that it is ultra clear and reproduce-able. Then you can post your own solution. If I can improve your solution, I'll post an answer as well. Remember that helping YOU is just the beginning, we must all aim to help all future Joomla researchers with great questions and great answers. – mickmackusa Aug 5 '18 at 6:12

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.