How to Insert Record in multiple rows PHP MYSQL

Question

enter image description here

I have a problem whenever I wanted to insert record in rows, it only insert the last value of the rows

Here is my insert server record behavior code:

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
 $insertSQL = sprintf("INSERT INTO ordering (productCode, Name, paymentMethod, Quantity, TotalPrice) VALUES (%s, %s, %s, %s, %s)",
                   GetSQLValueString($_POST['productcode'], "int"),
                   GetSQLValueString($_POST['Name'], "text"),
                   GetSQLValueString($_POST['PaymentMethod'], "text"),
                   GetSQLValueString($_POST['quantity'], "int"),
                   GetSQLValueString($_POST['totalprice'], "double"));

  mysql_select_db($database_perfume_connection, $perfume_connection);
  $Result1 = mysql_query($insertSQL, $perfume_connection) or die(mysql_error());

  $insertGoTo = "member_perfume_homepage.php";
  if (isset($_SERVER['QUERY_STRING'])) {
  $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
  $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

You need to [stop using mysql_ functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) as they are going to be removed from PHP — Machavity, Oct 09 '15 at 16:17
Possible duplicate of [Inserting multiple rows in mysql](http://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql) — GottZ, Oct 09 '15 at 16:18
i tried gottz solution but i cant get it done to query @Machavity its ok though im just using this as for my school projects.... but still i want it to be done though — KingJ, Oct 09 '15 at 16:24

score 0 · Answer 1 · answered Oct 10 '15 at 01:39

The SQL You are executing is:

INSERT INTO ordering (productCode, Name, paymentMethod, Quantity, TotalPrice) VALUES (%s, %s, %s, %s, %s)

which inserts only one row. Thats the syntax of SQL. Also, You should notice by Your common sense, that You are parsing $_POST only once, so it will not retrieve You multiple values to be used in rows used by sprintf.

Correct syntax of inserting multiple rows in one SQL query is:

INSERT INTO ordering (productCode, Name, paymentMethod, Quantity, TotalPrice) VALUES (%s, %s, %s, %s, %s), (%s, %s, %s, %s, %s), (%s, %s, %s, %s, %s);

If You do would like to prepare SQL query using sprintf function, You will need 3x more arguments passed to that method.

$insertSQL = sprintf("INSERT INTO ordering (productCode, Name, paymentMethod, Quantity, TotalPrice) VALUES (%s, %s, %s, %s, %s)",
                   GetSQLValueString($_POST['productcode'], "int"),
                   GetSQLValueString($_POST['Name'], "text"),
                   GetSQLValueString($_POST['PaymentMethod'], "text"),
                   GetSQLValueString($_POST['quantity'], "int"),
                   GetSQLValueString($_POST['totalprice'], "double"),
                   GetSQLValueString($_POST['productcode'], "int"),
                   GetSQLValueString($_POST['Name'], "text"),
                   GetSQLValueString($_POST['PaymentMethod'], "text"),
                   GetSQLValueString($_POST['quantity'], "int"),
                   GetSQLValueString($_POST['totalprice'], "double"),
                   GetSQLValueString($_POST['productcode'], "int"),
                   GetSQLValueString($_POST['Name'], "text"),
                   GetSQLValueString($_POST['PaymentMethod'], "text"),
                   GetSQLValueString($_POST['quantity'], "int"),
                   GetSQLValueString($_POST['totalprice'], "double"));

This should work, although, I don't suggest using this way, but think of a better solution.

Also, mysql_* functions are deprecated. Use PDO or MySQLi instead. Read about them in PHP manual: http://php.net/manual/en/book.pdo.php or http://php.net/manual/en/class.mysqli.php

i'll update you as soon as i finish updating my code! wait me!! — KingJ, Oct 10 '15 at 01:46
okay i've done coding it inserts the row fine but if i insert 1 item, the databse shows that i insert the item 3 of them..(UPDATE) if i enter 2 different items from the row only the last row will be inserted in the database and it will insert 3 rows of the insert record — KingJ, Oct 10 '15 at 01:52
um im using ur suggested code but, its not working.... it only shows the last value of the row.. see the picture from my post above... u see the tables with 2 rows? for example it only shows the last row and it will insert 3 duplicate data from the last row... i want it to show many data from the rows in the database.. — KingJ, Oct 10 '15 at 02:12
My code was just an example and it will insert 3 identical rows. So thats correct. — dpitkevics, Oct 10 '15 at 02:16
its weird because why is only the value of the last row is inserted? why not from top to down? — KingJ, Oct 10 '15 at 02:20
this must be some kind of misunderstandings,,, i did say that i want to insert record FROM rows NOT IN ROWS... but someone just edit my title and my content... that is why you dont understand me.. sorrry =((((( — KingJ, Oct 10 '15 at 02:47

How to Insert Record in multiple rows PHP MYSQL

1 Answers1