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!

  • 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 Jun 12 '18 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? – mickmackusa Jun 12 '18 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 – mickmackusa Jun 12 '18 at 9:50
  • 1
    I needed them to be isolated. – saibbyweb Jun 15 '18 at 15:20
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

  • 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 Apr 28 '16 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 Apr 28 '16 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 Apr 28 '16 at 8:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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