I have a PHP script and I'm inserting values into a MySQL table. This was working fine when dealing with a few thousand lines of data but as I increase the data only a part of the data is inserted into the MySQL table.
It seems to stop after only about 6000 rows of data. REally I want it to work for 40,000 lines and later it needs 160,000 line. I have to run the script several times to get more data added into the table.
I am new to working with SQL statements and I don't think the way I have set it up is efficient.
Some of my code:
for($x=0;$x<count($array_athlete); $x++){
$checkuser=mysqli_query($link,"SELECT * FROM `Events_testing2` WHERE `location`='$location'
AND `barcode`='$array_barcode[$x]' AND `date`='$date'");
$rowcount=mysqli_num_rows($checkuser); //checks if barcode exists for that user in that location on that date. Inserts data if doesn't already exist.
if($rowcount>0){
}
else{
$queryInsertUser=mysqli_query($link, "INSERT INTO `Events_testing2` (`eventID`,`location`,`date`,`barcode`,`athlete`,`time`,`Run Points`,`Volunteer Points`,`Gender`,`Gender pos`)
VALUES (' ','$location','$date','$array_barcode[$x]','$array_athlete[$x]','$array_time[$x]','$array_score[$x]',' ','$array_gender[$x]','$array_gender_pos[$x]') ");
}
}
Any advice of how to insert more rows quickly into the database would be appreciated.
Many thanks
Insert by chunk, like insert first 1000 (1 - 1000) then next 1000 (1001 - 2000). In this way, you will not encounter errors.
Try to set the time_limit of php beforehand
<?php
ini_set('max_execution_time', '0'); // infinite
set_time_limit(0); // infinite
/// do your stuff
See:
http://php.net/manual/en/info.configuration.php#ini.max-execution-time
http://php.net/manual/en/function.set-time-limit.php