Insert multiple rows using a single query

Question

Can Joomla's DB object add multiple rows at once? MySQL can do this like so:

INSERT INTO x (a,b)
VALUES 
 ('1', 'one'),
 ('2', 'two'),
 ('3', 'three')

But can Joomla's own functions achieve the same thing in a single query? Currently I am doing a loop to insert each row (same table) in separate query. Not a good idea when dealing with tons of rows at once.

`$this->store($data,'tablename')` - using the Jtable instance — exentric, Apr 08 '11 at 07:25

score 7 · Accepted Answer · answered Apr 08 '11 at 09:04

7

In your model you can do this:

$db = $this->getDBO();
$query = "
  INSERT INTO x (a,b)
  VALUES 
  ('1', 'one'),
  ('2', 'two'),
  ('3', 'three')
";
$db->setQuery($query);
$db->query();

If you are outside your model you need to get the DB object like so:

$db = JFactory::getDBO();

answered Apr 08 '11 at 09:04

Martin

10,294
11
63
83

Yeah this is what I did. The problem is, I have to do two separate queries for INSERT and UPDATE. With JTable instance, it will choose automatically depending on presence of PRIMARY KEY. – exentric Apr 08 '11 at 13:07
I thought you said you have to do 2 separate queries INSERT and UPDATE. Then why would JTable have to choose? Surely it would also need to do both INSERT and UPDATE as well, unless there is something you have failed to mention about what you want to do. – Martin Apr 12 '11 at 08:07

Petar · Answer 2 · 2012-04-10T01:27:39.130

6

You can use:

$db = JFactory::getDbo();
$query = $db->getQuery(true); // !important, true for every new query

$query->insert('#__table_name'); // #__table_name = databse prefix + table name
$query->set('`1`="one"');
$query->set('`2`="two"');
$query->set('`3`="three"');
/* or something like this:
$query->columns('`1`,`2`,`3`');
$query->values('"one","two","three"');
*/

$db->setQuery($query);
$db->query();

and $db->insertId() can return you autoinc id if you have one.

edited Apr 10 '12 at 01:27

answered Apr 02 '12 at 23:13

Petar

61
1
2

I've corrected the answer tested with $query->columns, $query->values as it was initially wrong. – Valentin Despa Sep 26 '12 at 08:28
This works very well, except if you try it on 1000's of rows, then I would rather suggest Martin's solution up top for efficiency. – Anriëtte Myburgh Jul 17 '15 at 12:20

score 5 · Answer 3 · answered Mar 16 '13 at 16:48

Try this, if you have values in an array :

$query = $this->db->getQuery(true);
$query->insert($this->db->quoteName('#__table_name'));
$query->columns($this->db->quoteName(array('col_1','col_2','col_3','col_4')));

for($i=0; $i < lengthOfArray; $i++)
{
    $values= $arr_1[$i].','.$this->db->quote($arr_2[$i]).','.$this->db->quote($arr_3[$i]).','. $arr_4[$i];
    $query->values($values);
}
$this->db->setQuery($query);
$result = $this->db->query();

score 2 · Answer 4 · answered Mar 11 '13 at 00:12

Try this:

$db = JFactory::getDbo();
$query = $db->getQuery(true);

$query->insert('x');
$query->columns('a,b');
$query->values('1', 'one');
$query->values('2', 'two');
$query->values('3', 'three');

$db->setQuery($query);
$db->query();

A description of "values" method

Adds a tuple, or array of tuples that would be used as values for an INSERT INTO statement.
Usage:
$query->values('1,2,3')->values('4,5,6');
$query->values(array('1,2,3', '4,5,6'));

score 2 · Answer 5 · edited Jul 24 '18 at 15:16

In latest version of Joomla!, you can use it's own DB class as follows. Remember to use 'quoteName()' and 'quote()' functions as you needed.

$dbo = JFactory::getDbo();
$query = $dbo->getQuery(true);

$columns = array('col_one','col_two', 'col_three');
$values = array();

//if you need, here you can use forloop/foreach loop to populate the array
$values[] = 'val_1, val_2, val_3'; // first row values
$values[] = 'val_4, val_5, val_6'; // second row values
...

$query->insert($dbo->quoteName('#__table_name'));
$query->columns($columns);

$query->values($values);

$dbo->setQuery($query);
$dbo->query();

Hope this saves your time. Thanks. Happy coding! :)

score 2 · Answer 6 · edited May 23 '12 at 10:45

2

You don't need $db = $this->getDBO();

just use this:-

$query = "
  INSERT INTO x (a,b)
  VALUES 
  ('1', 'one'),
  ('2', 'two'),
  ('3', 'three')
";

$this->_db->setQuery($query);
$this->_db->query();

edited May 23 '12 at 10:45

EdChum

376,765
198
813
562

answered Mar 19 '12 at 07:29

Raj Kumar Das

21
1

score 0 · Answer 7 · answered Jul 24 '18 at 15:14

...
$columns = array('user_id', 'type', 'object', 'lvl', 'date');
$values  = array();
foreach ($batch as $row) {
    $array    = array(
        $row->user_id,
        $db->quote($row->type),
        $db->quote($row->object),
        $db->quote($row->lvl),
        $db->quote($row->date),
    );
    $values[] = implode(',', $array);
}
$query->insert($db->quoteName('#activity_log'));
$query->columns($db->quoteName($columns));
$query->values($values);

$db->setQuery($query);
$result = $db->execute();

Insert multiple rows using a single query

7 Answers7

Linked