SQL WHERE clause with a variable (current article ID)

Question

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!

Answer 1

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.