0

The table #__virtuemart_order_histories stores the orders with their virtuemart_order_ids and every order has the records with their own virtuemart_order_history_ids. I need to update the latest record in the range of certain order. Now I have the code:

$order_id = 1028;
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$fields = array($db->quoteName('order_status_code') . " = " . $db->quote('U'));
$conditions = array($db->quoteName('virtuemart_order_id') . " = " . $order_id);
$query->update($db->quoteName('#__virtuemart_order_histories'))->set($fields)->where($conditions)->order($db->quoteName('virtuemart_order_history_id') . ' DESC');
$db->setQuery($query);
$db->execute($query);

But it doesn't work how I need — it updates ALL the records of certain order and not only the latest one. How should I specify the condition of getting only the latest record?

1

Use limit to run on first row found. Since you order it by virtuemart_order_history_id, it will be latest record.

Instead of

$db->setQuery($query);

use

$db->setQuery($query, 0, 1);

Second argument is $offset, third is $limit.

  • LIMIT works in MySQL, Joomla supports other databases as well like Oracle which don't know this. – Nagarjun May 1 '15 at 4:28
  • Of course, how I forgot that. Thanks @Nagarjun. Updated my answer. – Rene Korss May 1 '15 at 6:23
2

There are two ways which you can do database independent way (Joomla recommended).

A. Set limit on query (works with Joomla 3.x only)

$query->setLimit($limit, $offset);
e.g. $query->setLimit(1, 0);

B. Set limit while assigning query to db object (Works with Joomla 2.5 & 3.x)

$db->setQuery($query, $offset, $limit);
e.g. $db->setQuery($query, 0, 1);
  • 1
    setLimit is only available for Joomla 3.x, not 2.5 – Lodder Apr 30 '15 at 20:03
  • Also, it should be $db->setQuery(0, 1). First is offset, second is the limit. – Rene Korss Apr 30 '15 at 21:42

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.