1

Possible Duplicate:
insert multiple rows via a php array into mysql

I know about the possibility of making a multiple insert 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's a better way to do this and maybe extending the current DB (active-record) library to make it with even less code?

Thanks

Improve this question
1

3 Answers 3

Reset to default
4

You need to be clear about your reason for wanting to insert multiple rows in a single statement. Is it for performance?

Frameworks are for programming productivity and convenience, but not necessarily performance. I agree with the answer given by @Udi Mosayev -- use the framework API in its simplest usage.

If you are inserting a small number of rows, the difference between inserting one row per statement and multiple rows per statement is insignificant.

If have a large number of rows and you really need them to insert with high-performance, nothing beats LOAD DATA INFILE. Your attempts to optimize usage of INSERT are being penny-wise and pound-foolish. Even dumping your PHP array into a tmpfile and then loading it LOAD DATA is faster than using INSERT.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Hi Bill. Strictly for performance it is. I am aware about what Udi Mosajev linked. I have not been able to get it running with multiple rows though. To do a load data infile seems interesting though and definitely a thing to consider. I will keep looking for a way to do multiple inserts or updates within the frames of the codeigniter active record style though...
The value of an ORM framework is for developer productivity, not runtime performance.
Hi Bill, I am aware of that, so therefore I would love to extend the database library of Codeigniter to do this. Want to thank you for your "SQL antipattern strike back" on Slideshare as well, probably one of the best things I've ever read!
@Bill Karwin, thank you for "LOAD DATA INFILE" I didn't know about it.
@Industrial: Thanks! I'm glad it was helpful. Be sure to check out my SQL Antipatterns book coming out very soon, with even more tips: pragprog.com/titles/bksqla/sql-antipatterns
|
2

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

you can read more about it here

Improve this answer

Comments

1

If you are going to insert really big number of rows you should do it in single query. I don't think that CI is doing it right. PS:Don't forget about mysql maximum query size.

Improve this answer

2 Comments

Hi Kirzilla, Isn't that what I am doing in my example, or have I gotten it wrong? Thanks!
Yes, you're doing it right. But if you'll use CI insert('table', $array) - I'm not sure that CI will it do as you wish. CI could execute multiple insert queries. So, be careful using CI's insert. Good luck!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.