Is it possible to run multiple update queries in one using Joomla?

Question

I want to run many SQL update queries at one time using JOOMLA 2.5. Below my code:

require_once '../includes/framework.php';  
$query = "UPDATE #__mytable SET myfield='value' where id=1; UPDATE #__mytable SET  
myfield='value' where id=2; UPDATE #__mytable SET myfield='value' where id=3;";  
$db = JFactory::getDbo();  
$db->setQuery($query);  
$db->query();

But it shows me a syntax error. I tried to test directly in MYSQL and it works.

Vivacity

Answer 1

PHP does not allow multiple queries by default. You can force it to do so by adding a parameter to mysql_connect, but I wouldn't recommend it (it opens huge security holes for SQL injections).

I don't know how JFactory handles this, but I would be surprised if it were different.

More infos about it: http://de3.php.net/manual/en/function.mysql-query.php#91669

Incite

Ok, so I think it will be better to use a loop. I also try to mesure the time that will take (0.095135 second) and it seems ok. Thank you for your help. – Vivacity

If you set the same value to every entry you can do it in a single query: UPDATE #__mytable SET myfield='value' where id IN (1,2,3) – Incite

Yes, but I don't have the same values. – Vivacity

what about using mysqli? it suports multiple query and joomla supports mysqli – Hub

Given that all updates are related to a single table, also the CASE costruct may be a viable solution. – Latoshalatouche

While this still is true, the newer answer by Demis is much more helpful. Please upvote it, it should be the accepted answer by now. – Incite

Answer 2

You must use JDatabaseDriver::splitSql() to split a string of multiple queries into an array of individual queries, and run them once at a time.

This is how the internal extensions installer works.

Don't worry about comments, they will be stripped off.

$sql = "UPDATE #__mytable SET myfield='value' where id=1; UPDATE #__mytable SET myfield='value' where id=2; UPDATE #__mytable SET myfield='value' where id=3;";

$db = JFactory::getDbo();
$queries = JDatabaseDriver::splitSql($sql);
foreach ($queries as $query)
{
    try
    {
        $db->setQuery($query)->execute();
    }
    catch (JDatabaseExceptionExecuting $e)
    {
        ...
    }
}

Louque