我们如何在同一个查询中插入多个值[关闭]

I have many records in my table I want to know that how can i use loop to insert all record in a single query. e.g

DATABASE TABLE:

invoice_id___ Product_1____Price_1___Product_2____Price_2_____Product_3____Price_3

similarly there are 10 products i want to insert them against same invoice id(primary key.) how can I do this please help. thanks

The problem is the database design. It's broken because it's not normalized. This makes it hard (to the point of being practically unfeasible) to write inserts, query, or establish DRI.

Instead the tables should look something like the following. Note that the InvoicedProducts table establishes a Many-to-Many relationship.

Invoices
--
invoiceNumber
invoiceDate

Products
--
productName
productPrice          -- current product price

InvoicedProducts
--
Invoice (FK)          -- the Invoice and Product FKs create a compound PK
Product (FK)
invoicedProductPrice  -- product purchased at price (in case the price changes)

Then you simply insert the into the InvoicedProducts like ..

INSERT INTO InvoiceProducts (invoiceId, productId, invoidedProductPrice)
VALUES(.., .., ..)

.. the appropriate number of times (or, once per Invoice/Product pair). Use separate INSERT statements, but a single transaction.

From the mySQL Manual

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

Here is the documentation link http://dev.mysql.com/doc/refman/5.6/en/insert.html

try this

$con=mysqli_connect(hostname,username,password,databasename);

    // Check connection
    if (mysqli_connect_errno())
      {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }
    mysqli_query($con,"INSERT INTO table_name (a,b,c)
    VALUES ('Peter', 'Griffin'),('absss','avdss','advas'),(7,8,9)");

Syntax is similar as below

-- INSERT INTO TABLE_NAME(COLUMN1, COLUMN2) VALUES(VALUE1,VALUE2),(VALUE3,VALUE4)....

But if you have multiple places to insert/update/delete, check the database design. You may need to normalize it.