Multiple INSERTS in one query Joomla

Question

How can I do multiple inserts like:

$query = "INSERT INTO `table` (`fied1`, `field2`, `field3`) VALUES ('one', 'two', 'three'),('four', 'five', 'six');
INSERT INTO `table` (`fied1`, `field2`, `field3`) VALUES ('seven', 'eight', 'nine'),('ten', 'eleven', 'twelve');"
$db->setQuery($query);
$db->query();

I can't split this query because I'm getting it from large text file.

Answer 1

Use JDatabaseDriver::splitSql($query) so your function will look like this:

   $query = "INSERT INTO `table` (`fied1`, `field2`, `field3`) VALUES ('one', 'two', 'three'),('four', 'five', 'six');
    INSERT INTO `table` (`fied1`, `field2`, `field3`) VALUES ('seven', 'eight', 'nine'),('ten', 'eleven', 'twelve');";
    $db = JFactory::getDbo();
    $queries = $db->splitSql($query);
    foreach( $queries AS $sql ) {
        $db->setQuery($sql);
        $db->execute();
    }

Answer 2

You can split the sql and run the statements using below code

    function insertSql($filename){
        $db = JFactory::getDbo();
        $status = true;

        //parsing of SQL file 
        $sql_file_path = JPATH_ADMINISTRATOR.'/components/xxx/sql/install/mysql/'.$filename.'.sql';
        $queries = JDatabaseDriver::splitSql(file_get_contents($sql_file_path));
        foreach ($queries as $query)
        {
            $query = trim($query);
            if ($query != '' && $query{0} != '#')
            {
                $db->setQuery($query);
                if (!$db->execute())
                {
                    $application->enqueueMessage(JText::sprintf('JLIB_INSTALLER_ERROR_SQL_ERROR', $db->stderr(true)), 'error');
                    $status  = false;
                }
            }
        }
        return $status;
    }

Answer 3

Try this,

$db = $this->getDBO();  
$query = "INSERT INTO `table` (`field1`,`field2`,`field3`)  
VALUES   
('one', 'two', 'three'),  
('four', 'five', 'six'),  
('seven', 'eight', 'nine'),
('ten', 'eleven', 'twelve')
";  
$db->setQuery($query);  
$db->query(); 

Answer 4

I'm using a workaround:

$sqlstr = "INSERT INTO `table` (`f1`, `f2`, `f3`) VALUES ('1', 'x', 'p');";
$sqlstr .= "INSERT INTO `table` (`f1`, `f2`, `f3`) VALUES ('1', 'x', 'p');";

[etc.]

$db = JFactory::getDbo();
$m = $db->getConnection();
$m->multi_query($sqlstr);