1

I am trying to do a simple calculation on two Joomla custom fields in a module. I get the article ID as a variable and want to put this in the WHERE clause to get the right custom field value. Have tried many thing, but can't manage. The field_id is 4 of the field I need and when I fill in the article ID as a number, it is working, but I can't get the variable to work (which I need so the module uses the article ID). Hope somebody can help me out?

$article_id = JFactory::getApplication()->input->get('id');

   $db = JFactory::getDbo();
   $query = $db->getQuery(true);
   $query->select('value', 'item_id', 'field_id')
         ->from ($db->quoteName('#__fields_values'))
         ->where($db->quoteName('#__fields_values.field_id=4'))
         ->where($db->quoteName('#__fields_values.item_id')=$article_id);
   $db->setQuery($query);


     $result = $db->loadResult();
     return $result;

Thanks!

3

Your code contains several errors, including a syntax error. You should use an IDE with proper code completion and signature hints (any modern IDE should be able to do that).

  1. $query->select('value', 'item_id', 'field_id'):

    JDatabaseQuery::select() takes exactly one parameter. If it is only one field, you can provide the name as a string directly; otherwise, it must be an array of field names. In any case, the field names should be escaped. Your code yields in selecting only value.

  2. ->where($db->quoteName('#__fields_values.field_id=4')):

    JDatabaseDriver::quoteName() takes a field name and optionally an alias as parameter, not an expression. Your code produces `#__fields_values`.`field_id=4`, leading to an SQL error. Also, since you are working with a single table, fully qualifying column names is not necessary.

  3. ->where($db->quoteName('#__fields_values.item_id')=$article_id):

    You are trying to assign a value to a function, which results in a syntax error. Also, you are not sanitising user input, which makes your code vulnerable to SQL injections.

  4. $result = $db->loadResult():

    Since you are fetching one field only, why do you try to select three fields?

If you fix the issues, your code looks like this:

$article_id = JFactory::getApplication()->input->get('id');

$db    = JFactory::getDbo();
$query = $db->getQuery(true);

$query
    ->select($db->quoteName('value'))
    ->from($db->quoteName('#__fields_values'))
    ->where($db->quoteName('field_id') . '=4')
    ->where($db->quoteName('item_id') . '=' . (int) $article_id)
;
$db->setQuery($query);

return $db->loadResult();

and should work like expected.

  • Thanks for your excellent explanation. I saw this only now after I had found an answer myself also. However, your explanation really helps to understand! Thanks a lot! – Wil-It Mar 6 '18 at 10:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.