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.
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
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)
{
...
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With