Inserting an array into MySQL database directly

Question

I have a text file to read which has around 10000 points separated by, x1,y1 x2,y2 x3,y3 . . 10000 times

I read them using a loop in PHP and then store in an array and then I run a loop and insert one line at a time in my database. It takes really long time. Is there any way I can insert the whole array

for ($i=0; $i<10000; $i++)

{
    $sql = '
        INSERT INTO `firefly`.`FreeFormPoly` 
            (`markedObjectID`, `order`, `x`, `y`) 
        VALUES 
            ('.$markedObjectsID.', '.$order.', '.$valuesx[i].','.$valuesy[i].')';

    $db->query($sql, $markedObjectsID, $order, $values[1], $values[0]);
}

Duplicated question: stackoverflow.com/questions/779986/…

Alexander Jardim
– Alexander Jardim

2013-06-28 18:50:42 +00:00
Commented Jun 28, 2013 at 18:50 — Alexander Jardim
– Alexander Jardim, Commented Jun 28, 2013 at 18:50

beiller · Accepted Answer · 2013-06-28 17:43:03Z

2

Try using multiple insert statement. Generate one insert and submit the entire statement

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

SO:

$sql = 'INSERT INTO `firefly`.`FreeFormPoly` (`markedObjectID`, `order`, `x`, `y`) VALUES';
for($i=0;$i<10000;$i++) {
    if($i != 0) $sql .= ',';
    $sql .= '('.$markedObjectsID.', '.$order.', '.$valuesx[i].','.$valuesy[i].')';                         
}
$db->query($sql);

answered Jun 28, 2013 at 17:43

beiller

3,1351 gold badge14 silver badges20 bronze badges

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

2 Comments

TravisO Over a year ago

There is a limit to how big of an INSERT you can sent. You might need an extra loop around this one. Depending on the size of the SQL, I recall having to limit it to 3,000 grouped inserts, but everybody's will vary.

beiller Over a year ago

Perhaps there is a limit. In that case "batch" them up into as many as you can. No doubt the slowdown is from the SQL parsing so many statements.

NewUser · Accepted Answer · 2013-06-28 18:52:03Z

I would do something like this:

$sql = 'INSERT INTO `firefly`.`FreeFormPoly` (`markedObjectID`, `order`, `x`, `y`) VALUES';
for($i=0;$i<length;$i++) {
    $sql .= '('.$markedObjectsID.', '.$order.',  .$valuesx[i].','.$valuesy[i].'),';
}
$sql = substr($sql,0,-1);
$db->query($sql);

Explanation:

The syntax to enter multiple records is

INSERT INTO TABLE_NAME VALUES(VAL1, VAL2, ....), (...), (...);

In the SQL you are concatenating (val1,val2,val3), every time you execute the loop hece you will get an extra , in the last position and substr() trims it off.

More preferably I would do

$sql = 'INSERT INTO `firefly`.`FreeFormPoly` (`markedObjectID`, `order`, `x`, `y`) VALUES ';
for($i=0;$i<length;$i++) {
    $sql .= '('.$markedObjectsID.', '.$order.',  .$valuesx[i].','.$valuesy[i].'),';
}
$sql = substr($sql,0,-1);
$result = mysqli_query($db,$sql)
    or die('Error in querying the database');

Philip Kearns · Accepted Answer · 2013-06-29 14:42:52Z

0

You should be able to speed it up considerably by sending BEGIN TRANSACTION before the loop and COMMIT after the loop. One time I had to insert 14,000 data points on SQLite, and it took 20 minutes, but when I put the data in as a transaction it completed in 0.3 seconds.

answered Jun 29, 2013 at 14:42

Philip Kearns

4555 silver badges17 bronze badges

Comments

Ja͢ck · Accepted Answer · 2013-06-29 14:51:02Z

0

First off, you can use prepared statements to reduce the network overhead. Assuming PDO:

$stmt = $db->prepare('INSERT INTO mytable (foo, bar, baz) VALUES (:foo, :bar, :baz)');

for ($i = 0; $i < $length; ++$i) {
    $stmt->execute(array(
        ':foo' => $data[$i]['foo'],
        ':bar' => $data[$i]['bar'],
        ':baz' => $data[$i]['baz'],
    ));
}

Second, you could wrap the whole code inside $db->beginTransaction() and $db->commit().

answered Jun 29, 2013 at 14:51

Ja͢ck

174k39 gold badges269 silver badges316 bronze badges

Collectives™ on Stack Overflow

Inserting an array into MySQL database directly

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related