Mysqli multi query in foreach loop

Question

I have to do a lot of insert in my DB, importing data from xml. Now, if I open and close the connection in the loop, the script works but crashes for max connection number, and if I use the following code, it execute just one time the mysqli_multi_query.

I just need to know, how to maintain the connection to execute a new multi query in the loop.

$xml = simplexml_load_file('demo.xml');
$mysqli =new mysqli($servername, $username, $password, $dbname);
foreach($xml->datas as $data) {
    $sql="INSERT IGNORE INTO table1 (hobby) values ('".$data->child74."');";
    $sql.="INSERT IGNORE INTO table2 (pizza, spaghetti) values ('".$data->child55."', '".$data->child52."');";
    // a lot more insert...
    mysqli_multi_query($mysqli,$sql);
}
mysqli_close($mysqli);

In any case, your script will crash randomly as soon as data contains quotes or some other special chars. I suggest you drop your current PHP tutorial and learn the joy of prepared statements. — Álvaro González
– Álvaro González, Commented Mar 2, 2017 at 13:10
Anyway, after vote down, I need some explanation to understand what I'm doing wrong. — Vixed
– Vixed, Commented Mar 2, 2017 at 13:20
Pardon? I didn't mean to answer your question. That's why I left comments instead of populating the answer box. — Álvaro González
– Álvaro González, Commented Mar 2, 2017 at 13:27

Your Common Sense · Accepted Answer · 2017-03-02 13:55:38Z

2

For the multiple inserts you should be using prepared statements. This approach will solve all the problems you have at once. The only possible issue (related to possible non-optimal database settings) is solved by using a transaction.

The code below is using only single connection and as fast as it can be

$xml = simplexml_load_file('demo.xml');

$stmt1 = $mysqli->prepare("INSERT IGNORE INTO table1 (hobby) values (?)");
$stmt1->bind_param("s",$hobby);

$stmt2 = $mysqli->prepare("INSERT IGNORE INTO table2 (pizza, spaghetti) values (?,?)");
$stmt2->bind_param("ss", $pizza, $spaghetti);

$mysqli->autocommit(FALSE);
foreach($xml->order as $data) {
    $hobby = $data->child74;
    $pizza = $data->child55;
    $spaghetti = $data->child52;
    $stmt1->execute();
    $stmt2->execute();
}
$mysqli->commit();
$mysqli->close();

Prepared statements with a transaction make it civilized, secure and efficient solution.

edited Mar 2, 2017 at 13:55

answered Mar 2, 2017 at 13:50

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

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

11 Comments

Vixed Over a year ago

I'm in love with your answer, but my problem is that there are around 60 insert query already done, with a lot of string conversion for each var... for some insert it will be ok, but imagine 50/60 insert with 10 columns each... I don't know

Your Common Sense Over a year ago

Don't know what? How to add 10 question marks to a query?

Vixed Over a year ago

Yes, I'm on it ;) Thanks!

Vixed Over a year ago

Any method to print the prepared query? A way to check it on screen without execution.

Your Common Sense Over a year ago

No, if you mean with values inserted. Values are going to server separately and never interfere with the query

|

Collectives™ on Stack Overflow

Mysqli multi query in foreach loop

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related