Convert PHP code to Jooma PHP syntax?

Question

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)

Answer 1

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.

Answer 2

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.

Answer 3

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.