Custom Fields inside module override

Question

I am using this code inside a module override that render articles, to show the custom fields:

<?php
    $id=$item->id;
    $db = JFactory::getDbo();
    $query = 'select * from #__fields_values where item_id = "'.$id.'"';
    $db->setQuery($query);
    $fields = $db->loadObjectList();

    foreach ($fields as $field) {
        if ($field->field_id == 4) { // 4 - id of your custom field
        echo $field->value;
        }
    }
    ?>

It works fine and renders the value of the field, but I want to display also the field label. Any help?

Thank you

Answer 1

The label is in the 'fields' table so you could pull them with the values in a single query by using a join, eg.

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('a.value,b.label,b.id');
$query->from($db->qn('#__fields_values','a'));
$query->join('INNER', $db->qn('#__fields', 'b') . ' ON ' . $db->qn('a.field_id') . ' = ' . $db->qn('b.id'));
$query->where($db->qn('a.item_id') . ' = ' . $db->q($item->id));
$query->where($db->qn('b.context') . ' = ' . $db->q('com_content.article'));
$db->setQuery($query);
$fields = $db->loadObjectList('b.id'); // add the field id as the key

// For testing to see the whole array
echo '<pre>' . print_r($fields,1) . '</pre>';

// To show only specific fields, eg for field id 4
echo '<p>' . $fields[4]->label . ': ' . $fields[4]->value . '</p>';

// to show all the fields - warning they wont be in the order set in the fields interface
foreach ($fields as $field_id => $field) {
    echo '<p class="fid-' . $field_id . '">' . $field->label . ': ' . $field->value . '</p>';
}