2

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!

4
  • I have already mentioned in the question details, "Instead of INSERT INTO Command, I need to execute a UPDATE command if the row already exists" ?
    – saibbyweb
    Commented Jun 12, 2018 at 8:01
  • Truth is, you don't need to check first. Just send the UPDATE query. If there isn't a qualifying row(s), there will be no affected rows. Is that satisfactory? Commented Jun 12, 2018 at 8:18
  • What I am asking is: do you really need to isolate each possible outcome or only if an update was successful?joomla.stackexchange.com/a/22985/12352 Commented Jun 12, 2018 at 9:50
  • 1
    I needed them to be isolated.
    – saibbyweb
    Commented Jun 15, 2018 at 15:20

1 Answer 1

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

3
  • 1
    You should propagate JTable for this case ; ) if(JTable->load([$key => $value])) return true if record exists and false if record not exists then you can use ->store to insert / update record.
    – Lanah
    Commented Apr 28, 2016 at 7:03
  • 1
    @Lanah - That is under the assumption that this is for a component ;) Seeing as nothing was mentioned, I just used a simple database query
    – Lodder
    Commented Apr 28, 2016 at 8:10
  • 1
    true, but he might be using some kind of core tables : ) and if not he should create JTable in module / plugin :)
    – Lanah
    Commented Apr 28, 2016 at 8:14

Your Answer

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

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