Correct way of using INSERT INTO with my array

Question

I am currently using the following INSERT INTO :

$sql=  "INSERT INTO orders (Orderdatum, Naam, Woonplaats, Straatnaam, Huisnummer, Telefoon, Aantal, Prijs) 
        VALUES (now(), '$_POST[naam]','$_POST[woonplaats]','$_POST[straatnaam]','$_POST[huisnummer]','$_POST[telefoonnummer]', '$total_number', '$total_price')
        ";

mysql_query($sql);

And:

foreach ($products as $key => $product){
  $number = isset($_POST[$key])?$_POST[$key]:'';
  if ($number > 0){
    $sql2 = "INSERT INTO ordered_products (Ordernr, Product, Aantal) VALUES (last_insert_id(), '$product', '$number')";
    mysql_query($sql2);
    }
}

To place the customers order info in 1 table, and the products that are ordered in another. With the correct Ordernr.

This is working just as it should. But i was told the second piece of code the foreach shouldn't be done like i have it right now.

Since it will give problems in the long run if I add more values and make bigger arrays.

So i'm wondering what is the correct way of using this foreach?

Dont get me wrong, my code is working as it should, no problems. But i was told this isn't the right way

This would be a good starting point http://stackoverflow.com/questions/1307618/multiple-mysql-insert-statements-in-one-query-php — Nouphal.M, Feb 18 '14 at 11:03

score 0 · Accepted Answer · answered Feb 18 '14 at 11:17

0

Try with this:

$InsertData = array();
$Last_Insert_Id = mysql_insert_id();

foreach ($products as $key => $product){
   $number = isset($_POST[$key])?$_POST[$key]:'';

   if ($number > 0){
        $InsertData[] = "('{$Last_Insert_Id}', '{$product}', '{$number}')";
   }
}


$sql2 = "INSERT INTO ordered_products (Ordernr, Product, Aantal) 
          VALUES " . implode(", ", $InsertData) . " ";
mysql_query($sql2);

answered Feb 18 '14 at 11:17

jogesh_pi

9,762
4
37
65

Can you maybe tell me why this is the better approach, because according to my knowledge this doesn't seem THAT different from my original foreach – Déjà vu Feb 18 '14 at 11:21
@Farewyth Because inserting in loop cause multiple round trips to the database, and significantly slower scripts. – jogesh_pi Feb 18 '14 at 11:24
Ok, this makes sense. But does it completely outdo my script? Or should i just use this kind of script from now on even if its a little array like my example? maximum 11 products. – Déjà vu Feb 18 '14 at 11:27
@Farewyth, It could be any amount of data array, and a single insert query is better. – jogesh_pi Feb 18 '14 at 11:33

Correct way of using INSERT INTO with my array

1 Answers1