2

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.

2

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();
    }
  • This method is tagged as deprecated. Is there a replacement or has this been abandoned? Can you point me to a url that shows the source code for this method? – mickmackusa Jun 19 '18 at 4:35
  • 1
    You can find it in here github.com/joomla/joomla-cms/blob/staging/libraries/joomla/… and as far as I can see it is depreced only when you use it stright from JDatabase. So if you use it on JDatabaseDriver (that is the object returned from JFactory::getDbo()) you can still use it. They just inform about from where the method comes from. – Artur Stępień Jun 19 '18 at 11:08
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;
    }
0

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(); 
0

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.