Efficient MySQL INSERT
I have a PHP script and I am inserting values into a MySQL table. This worked fine when working with several thousand rows of data, but as I increment the data, only a fraction of the data gets inserted into the MySQL table.
It seems to only stop after 6000 lines of data. To be honest, I want it to work for 40,000 lines and then it needed 160,000 lines. I need to run the script multiple times to add more data to the table.
I'm new to working with SQL statements and I don't think the way I've configured it 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 on how to quickly insert multiple rows into the database would be helpful.
Many thanks
source to share
Try to set time_limit 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
source to share