0

I am executing a php script which insert more then 500 rows in multiple relational tables in a loop using pdo->exec()

Code Example:

pdo->beginTransaction();

while (until condition) {

    $sqlqry="Insert Into Table 1";
    pdo->exec($sqlqry);

    $sqlqry="Insert Into Table 2";
    pdo->exec($sqlqry);

    $sqlqry="Insert Into Table 3";
    pdo->exec($sqlqry);

}

pdo->commitTransaction();

I just wanted to know is this a good practice or i should do perform all insert quires at a same time or what is the best way to perform such multiple insert quires.

I am focusing on :

1) Best way to execute bulk insert multiple queries.

2) Friendly for db server.

Kevin
  • 41,694
  • 12
  • 53
  • 70
Salman Khalid
  • 7
  • 3
  • 1
    I cannot tell from your example if multiple inserts can be combined, but at the very least you should use prepared statements and prepare these before the loop so that you only have to execute them inside the loop. – jeroen Jan 24 '18 at 09:01
  • Your loop should be outside the pdo – jking Jan 24 '18 at 09:02
  • Possible duplicate of [PDO Prepared Inserts multiple rows in single query](https://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query) – JohannesAndersson Jan 24 '18 at 09:06
  • @Ziinloader It is not that simple, if the insert id's from the first queries (table 1, table 2) are needed in the later queries (table 2, table 3), you cannot combine the inserts like that. – jeroen Jan 24 '18 at 09:09

1 Answers1

-1

How is your conditions checked? Are the data in a file or array? Is it the same table you want to insert these data?

If these data are in a file and in sql format, i will suggest you dump them directly your database using phpmyadmin.

If these data are unformatted in sql, then you can format them using your code, then if they are intended for different tables then use this option https://stackoverflow.com/a/11237717/1230116

jking
  • 194
  • 2
  • 9