Php – How to insert/update a large amount of data into thesql using php

big dataexcelMySQLPHP

I have an excel sheet which has a large amount of data. I am using php to insert the data into mysql server.

I have two problems

1) I have to update a row if the id already exists, else insert the data.

2) BIG PROBLEM : I have more than 40,000 rows and the time out on the sql server which is set by the admin is 60 seconds. When i run the update/insert query it will take more than 60 seconds, and because of this there will be a timeout. So the whole process will fail.

Is there a way I can do this ?

Currently I am checking the student id if it exists, then update otherwise insert. This I feel is taking a lot of time and causing the server to time out.

Also I have this field in the mysql stating the last time the data was updated(last_update). I was thinking of using this date, and if it is past a particular date(ie last time i ran the program) then only those rows should be updated.

Will this help in anyway ?

And what is the query i can run so as to check this date in the mysql database, that if it is past a particular date only those rows need to be updated and not everything else.
(Please help me with an example query for the above!!!!!!!!!!!!!!!!!)

Best Answer

Use MySQL's INSERT... ON DUPLICATE KEY UPDATE syntax to automatically handle the insert/update logic. 40,000 is not that many rows - I'd be surprised if that command took more than a few seconds.

Note that you can insert many rows at once:

INSERT INTO table (id, name) VALUES (id1, name1), (id2, name2), ..., (idN, nameN) ON DUPLICATE KEY UPDATE id=VALUES(id), name=VALUES(name)

If you're worried about hitting a memory limit (possible) then try loading the rows in batches of a few thousand at a time and looping through.

Related Topic