How to check whether a specific row exists in a JDatabase table?

Question

What would be the best way to check whether a specific row exists already in a JDatabase table?

Instead of INSERT INTO Command, I need to execute a UPDATE command if the row already exists.

Help would be appreciated!

Answer 1

So lets say you would like to update the username Saibbyweb in the #__users table to something like Saibbyweb123.

You're going to need to perform a select query first to see if the row already exists:

$db = JFactory::getDbo();

$query = $db->getQuery(true);
$query->select($db->quoteName('username'))
      ->from($db->quoteName('#__users'))
      ->where($db->quoteName('username') . ' = '. $db->quote('Saibbyweb'));
$db->setQuery($query);

$result = $db->loadResult();

The $result will now either contain the username, or be null.

So you can now run your update query if the result does is not null:

if ($result !== null)
{
    $query  = $db->getQuery(true);
    $query->clear();

    $fields     = array($db->quoteName('username') . ' = ' . $db->quote('Saibbyweb123'));
    $conditions = array($db->quoteName('username') . ' = ' . $db->quote($result));

    $query->update($db->quoteName('#__users'))->set($fields)->where($conditions);
    $db->setQuery($query);
    $db->execute();
}

Hope this helps