Geek Answers Handbook

MYSQL multiple insertion in codeigniter

Possible duplicate:
insert multiple rows through php array in mysql

I know about the possibility of creating multiple inserts in mySQL by doing something like this:

foreach ($array as $manuf) { $sql[] = '("'.mysql_real_escape_string($manuf['name']).'", "'.$manuf['lang'].'", "'.$mId.'")'; } $this->db->query('INSERT INTO manufacturers (name, lang ,mid) VALUES ' . implode(',', $sql) );

I wonder if there is a better way to do this and possibly expand the current DB library (active-record) to make it even less code?

thanks

+1
source share
3 answers

You need to clearly state why you want to insert multiple lines in a single statement. Is it for performance?

The framework is designed for performance and programming convenience, but not necessarily for performance. I agree with @Udi Mosayev's answer - use the infrastructure API in its simplest use.

If you are inserting a small number of rows, the difference between inserting one row from one operator and several rows per operator is not significant.

If you have a large number of rows, and you really need to insert them with high performance, nothing will be LOAD DATA INFILE . Your attempts to optimize the use of INSERT are cheap and pound-stupid. Even dropping your PHP array into tmpfile and then loading it with LOAD DATA faster than using INSERT .

+4
source

Sure. Just use $this->db->insert('dbTableName', $arrayOfData) . The data array is the field-> value, and the field is the name of the column in your table inside the database.

you can read about it here

+2
source

If you are going to insert a really large number of rows, you should do this in a single query. I do not think CI is doing everything right. PS: Do not forget about the maximum size of the mysql request.

+1
source

More articles:


All Articles