Out of memory error with multiple inserts

Question

I have a script that takes a variable number of inputs from a user and inserts them into a database table, with one row for each item. For small data sets, this works fine, but for larger data sets (more than 10 items) the script always fails with an out of memory error. Here's the code I'm using- is there a more memory efficient way of doing this?

$data = $_POST['dataArray'];
for ($i = 0; i < count($data); $i++)
{
    $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
    $stmt = $mysqli -> prepare('INSERT INTO items(name, serial, cost) VALUES (?, ?, ?)');
    $stmt -> bind_param('sii', $data[$i]['name'], $data[$i]['serial'], $data[$i]['cost']);
    $stmt -> execute();
    $stmt -> close();
    $mysqli -> close();
}

FWIW, the database table in question is just 4 columns:

id [int(11), autoincrement]
name [varchar(256]
serial[int(11)]
cost[int(11)];

Move your SQL connection and prepare out of the for loop, same for your close statements and leave only the bind_param and the execute inside. You can also resume your bind_param into: `$stmt->bind_param('sii', $data[$i]);` but before doing so make sure the array order is the same for the types. Also your cost field should be `double(7,2)` will hold millions instead if you plan on having decimal values such as `100.23` — Prix, Mar 02 '14 at 23:23
is there a reason why you're opening and closing a new connection on each and EVERY row you're inserting? — Tularis, Mar 02 '14 at 23:23
If you want _real_ efficiency, instead of inserting each row separately, you should insert them all at once. "INSERT INTO items(name, serial, cost) VALUES (a,b,c), (d,e,f) ... ;" http://stackoverflow.com/questions/14860606/mysqli-inserting-multiple-rows-with-one-prepared-statement is an example of a dynamic prepared statement. — erik258, Mar 02 '14 at 23:42
Thanks Dan Farrell, your answer ended up working for me. I had previously tried moving the connection opening and closing outside of the loop, but I still get the same out of memory error. I had moved them inside hoping that creating / tearing down a connection and prepared statement immediately would reclaim memory faster, although i now realize that's not the case. — Alex Blyton, Mar 03 '14 at 00:35

score 1 · Accepted Answer · answered Mar 02 '14 at 23:29

Your problem is you are creating a LOT of connections by including them in your loop; to fix use this:

$data = $_POST['dataArray'];
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
$stmt = $mysqli -> prepare('INSERT INTO items(name, serial, cost) VALUES (?, ?, ?)');
for ($i = 0; i < count($data); $i++) {
    $stmt -> bind_param('sii', $data[$i]['name'], $data[$i]['serial'], $data[$i]['cost']);
    $stmt -> execute();
}
$stmt -> close();
$mysqli -> close();

Out of memory error with multiple inserts

1 Answers1