Query gives a single value when two columns in SELECT clause

Question

I do have a database connection earlier in the script.

$db = JFactory::getDbo();

Here's the query code:

$albumartistidquery = $db->getQuery(true);

$albumartistidquery->select($db->quoteName(array('artist_id', 'artist_name')));
$albumartistidquery->from($db->quoteName('#__vinylvault_artists'));
$albumartistidquery->lookup($db->quoteName('artist_name'));
$albumartistidquery->where($db->quoteName('artist_id') . ' LIKE '. $db->quote($releaseartistid));

$db->setQuery($albumartistidquery);

// Load the results
$dbalbumartist = $db->loadResult();

I'm expecting that $dbalbumartist should contain the contents of the relevant 'artist_name' field, but when I echo $dbalbumartist it displays the artist_id column value.

Can anyone advise where I've gone wrong and how to fix?

Answer 1

For the most part, your code is pretty right. As commented, you are using ->lookup() -- a method that is currently unheard of.

Generally, you would only use the LIKE operator if you were going to also use _ or % wildcard symbols. Because your LIKE condition is matching the whole $releaseartistid string, it seems more logical to replace LIKE with =.

The loadResult() method is to be used when you are only expecting a single value in the resultset. Your query is returning two columns of data so if you are expecting a single row of data, then you might prefer loadRow(), loadAssoc(), or loadObject(). If your resultset may contain more than one row of data, then refer to the Mult-Row Methods and determine which method is best suited for your project.

Assuming your $releaseartistid value is an integer, you can avoid quote-wrapping the value and maintain security by casting the value as (int).

As a matter of personal preference, I use qn() versus quoteName() purely for code brevity.

Suggested Code:

$db = JFactory::getDBO();
try {
    $albumartistidquery = $db->getQuery(true)
                             ->select($db->qn(array('artist_id', 'artist_name')))
                             ->from($db->qn('#__vinylvault_artists'))
                             ->where($db->qn('artist_id') . ' = ' . (int)$releaseartistid);
    echo $albumartistidquery->dump();    // never expose this on your live/public site
    $db->setQuery($albumartistidquery);
    echo "<pre>";
        var_export($db->loadAssoc());  // display the resultset
    echo "</pre>";
} catch (Exception $e) {
    echo "<div>", $e->getMessage(), "</div>";  // never expose this on your live/public site
}

Finally, if you DO want to use LIKE with wildcards, I'll refer you to this post where I explain how to use LIKE with proper Joomla escaping syntax to maintain query security.