Insert a lot of records into database

Question

I have a list with a lot of (500+) codes and I want to insert it into database.

For example: User given codes to textarea and codes are seperated by new line (\n). In PHP it is $_POST['codes']. Of course I can just use explode() function and insert all codes in loop, but I'm thinking it's not a good idea with 500 repeatitions.

So, how can I do this the most optimal?

500-1000 repeats is nothing for both PHP and MySQL. So just explode and insert... — enenen
– enenen, Commented May 30, 2013 at 10:27
Probably best to use a prepared statement as well to speed it up that little bit. — Phil Cross
– Phil Cross, Commented May 30, 2013 at 10:27
Time isn't an issue and $_POST['codes'] means: data is already in the memory anyway. — Axel Amthor
– Axel Amthor, Commented May 30, 2013 at 10:28

Community · Accepted Answer · 2017-05-23 11:57:23Z

2

Depending on the size of your data, you could make it all into a single query if you are worried about number of separate inserts:

mysql_query("INSERT INTO mytable(`People`, `Places`, `Ideas`, `things`) VALUES ('40', '15', '0', '0'), ('0', '5', '10', '0'), ('10', '0', '11', '12')");

(example from: https://stackoverflow.com/a/10286687/486780)

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered May 30, 2013 at 10:29

ZorleQ

1,4371 gold badge13 silver badges23 bronze badges

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

4 Comments

Marcel Korpel Over a year ago

Don't recommend mysql_* functions, they are deprecated (see the red box). It's advised to use prepared statements.

ZorleQ Over a year ago

Of course, you are 100% right about prepared statements. But as a proof of concept, this would be one way of doing it.

Marcel Korpel Over a year ago

It's crucial for this question: if you run prepared statements, the SQL interpreter has to interpret the query only once and you can run the same query over and over again at high speed. That's a totally different syntax from yours.

ZorleQ Over a year ago

You are totally right. Missed the point of your initial comment a bit. All I can do is agree now.

Collectives™ on Stack Overflow

Insert a lot of records into database

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related