Insert multiple rows using a single query

Question

Can a Joomla DB object add multiple rows at once? MySQL can do this as follows:

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

But can Joomla's own functions perform the same thing in a single request? I'm currently doing a loop to insert each row (the same table) into a separate query. Not a good idea when you are dealing with many lines at once.

Answer 1

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 of your model, you need to get the DB object as follows:

 $db = JFactory::getDBO(); 

Answer 2

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 an autoinc identifier if you have one.

Answer 3

Try it if you have values ​​in the 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(); 

Answer 4

You do not 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(); 

Answer 5

Try the following:

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

Description of the value method

Adds a tuple or array of tuples to be used as values ​​for the INSERT INTO statement.
Using:
$ Query-> values ​​('1,2,3') → values ​​('4,5,6');
$ query-> values ​​(array ('1,2,3', '4,5,6'));

Answer 6

In the latest version of joomla, you can use your own DB class as follows. Remember to use the "quoteName" and "quote" functions as necessary.

 $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 you some time. Thank you Happy coding! :)