无法调用超过100个INSERT查询

Question

I have to insert 1000 records to read from file and insert to db but my code is not working more than 100 times. It says internal server 500 but it works fine when I run code below 100 times. Are there any settings that I have to apply to insert more than 100 records?

Here is my code:

<?php 
    $servername = "**";
    $username = "**";
    $password = "**"; 
    $dbname = "**";

    $con=mysqli_connect($servername, $username, $password,$dbname);

    for($i=0; $i<400; $i++){

        $query = mysqli_query($con,"INSERT INTO table_name (column1, column2, column3)
                                    VALUES (value1, value2, value3)");          
    }

    $query->close();
    $con->close();
?>

It's working only 100 times. If I set loop to run more than than that, it does not work any ideas?

Answer 1

I am unsure if this will eliminate your issue, but it is always better to try to reduce total database calls in your code.

Since you are INSERTing on the same table, you can do everything in a single query. Build your single query inside the loop and then use mysqli_query() only once after the loop is finished.

Code:

$values="";
for($i=0; $i<400; $i++){
    $values.=($i==0?"",",")."('value1','value2','value3')";
}
$query=mysqli_query($con,"INSERT INTO table_name (column1, column2, column3) VALUES $values;