PHP Insert large amount of values issue

Question

Im currently writing a php loader to my mySQL database . The user enter user|password and write the database table he want to insert to , and select a region from

The form action goes to database.php to do the insert using POST

my code in database.php is:

$region = $_POST["region"];
$tablename = $_POST["tableName"]; 

$accounts = explode(PHP_EOL, $_POST["accountlist"]);

$quantity = sizeof($accounts);
// Batch size, calc iterations
$batchSize = 500;

for($idx=0;$idx*$batchSize < $quantity; $idx++){
    $accountsPartial = array_slice($accounts, $idx*$batchSize, $batchSize);

    // Prepare the Query
    $query = "INSERT INTO $tablename (username, password, region) VALUES (?, ?, ?)";
    // Create the multiple value placeholder
    $db = $db->prepare($query);


    foreach($accountsPartial as $item){
        $i = 1;
        list($user, $pass) = explode("|", $item);
        $db->bindValue($i++, trim($user));
        $db->bindValue($i++, trim($pass));
        $db->bindValue($i++, trim(strtoupper($region)));
        $db->execute();
    }
}

My problem is when I sent more then 500 Values , only 500 accounts getting Insereted. I would love to get some help and suggestions how to solve this issue Thanks in advance!

You should be preparing your statement outside the loops. Doing it inside the loop for defeats the purpose of preparing statements in the first place. But at least you're doing it inside the foreach, so there's that, at least. Plus. why batchsize at all? If you want to insert all of the records, then just split your array and loop on the entire thing. — Marc B
– Marc B, Commented Feb 2, 2015 at 15:08
When you step through this in a debugger, where does it fail? The observed result is that it stops at 500, and the code pretty strongly implies a "batch size" of 500. I doubt that's a coincidence. Perhaps the logic for moving on to the next "batch" is either not working as intended or you misinterpreted the intent? — David
– David, Commented Feb 2, 2015 at 15:08
Why care about a "batch size" at all if you are inserting one row at a time? It's not like you are inserting multiple rows at a time and parallelizing execution of large batch queries or anything of that sort to where batching is even a complexity you should add. — Mike Brant
– Mike Brant, Commented Feb 2, 2015 at 15:17

Marc B · Accepted Answer · 2015-02-02 15:21:54Z

1

Your looping code is basically useless. Since you want to insert ALL of the data from the POST, you should be doing this:

$query = "INSERT INTO $tablename (username, password, region) VALUES (?, ?, ?)";
// Create the multiple value placeholder
$stmt = $db->prepare($query);
$stmt->bindValue(1, $user);
$stmt->bindValue(2, $pass);
$stmt->bindValue(3, $region)

$region = trim(strtoupper($region));

foreach($accountsPartial as $item){
    list($user, $pass) = explode("|", $item);
    $user = trim($user);
    $pass = trim($pass);

    $stmt->execute();
}

Note the binding of the variables OUTSIDE the loop, and binding to the actual variables, not the results of the trim() calls.

As well, as written, your code is deleting your database connection, by using the same variable to hold your prepared statement as the db connection itself:

$db = $db->prepare(...);

will nuke your DB connection, making the rest of the code moot.

answered Feb 2, 2015 at 15:21

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

1 Comment

user3283390 Over a year ago

Hi , thanks for your answer , I tried using your code but now its not even loading 1 account , im probarly doing something wrong(im new to php so sorry for that) , Im not sure what my $accountsPartial should contain when im using your code , can you write me the whole code for your solution. Also , inside the foreach I dont need to add $region = trim($region) ? Thanks

Alexandru Olaru · Accepted Answer · 2015-02-02 15:14:56Z

0

You should use a do {} while () loop cause when you want to insert 600 instead of 500 the $idx = 2 and $idx * $batchSize = 1000 < 600 and is exiting the loop.

answered Feb 2, 2015 at 15:14

Alexandru Olaru

7,1426 gold badges31 silver badges55 bronze badges

1 Comment

user3283390 Over a year ago

I changed it to do {} while{} but it still not loading .. can you edit my code to do{} while{} and post it maybe i made a mistake? thanks in advance!

Collectives™ on Stack Overflow

PHP Insert large amount of values issue

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related