Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 867
toto Avatar asked Oct 03 '12 09:10

toto


2 Answers

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

like image 162
Gerald Schneider Avatar answered Oct 27 '22 16:10

Gerald Schneider


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)
    {
        ...
    }
}
like image 1
Demis Palma ツ Avatar answered Oct 27 '22 15:10

Demis Palma ツ