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?

2 Answers 2

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.

2
  • LIMIT works in MySQL, Joomla supports other databases as well like Oracle which don't know this.
    – Nagarjun
    May 1, 2015 at 4:28
  • Of course, how I forgot that. Thanks @Nagarjun. Updated my answer.
    – Rene Korss
    May 1, 2015 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);
2
  • 1
    setLimit is only available for Joomla 3.x, not 2.5
    – Lodder
    Apr 30, 2015 at 20:03
  • Also, it should be $db->setQuery(0, 1). First is offset, second is the limit.
    – Rene Korss
    Apr 30, 2015 at 21:42

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.