1

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?

6
  • You like clause value is incorrect. Check the documentation: docs.joomla.org/Selecting_data_using_JDatabase
    – Lodder
    Apr 30, 2018 at 10:43
  • Hi Charlie @Lodder strange how the same structure works fine on numerous other queries in the same component that I'm writing. the $releaseartistid variable is delated and populated previously in the script Apr 30, 2018 at 11:58
  • Note the % symbol
    – Lodder
    Apr 30, 2018 at 12:00
  • % there is no % in that code snippet Charlie @Lodder Apr 30, 2018 at 14:04
  • Also, you're using ->lookup(). Where in the API section did you see this? I've never heard of it.
    – Lodder
    Apr 30, 2018 at 14:12

1 Answer 1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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