Mysql insert multiple data issue

[laxi]

Hi everyone,

I am trying to insert multiple data into my mysql database table with the below syntax.

however it only inserts the first row of data and returns a syntax error. Not sure what i am doing wrong...Please advice
-------------------------------------------
INSERT INTO three_drops ( id, tier_one,tier_two, tier_three)
VALUES



(4, 'CLOTHING', 'MEN', 'Jeans');
(5, 'CLOTHING', 'MEN', 'Cargos');
(6, 'CLOTHING', 'MEN', 'Shorts & 3/4ths');
(7, 'CLOTHING', 'MEN', 'Trousers');

-------------------------------------------------

it inserts only 4th item and returns an error for the remaing items

Error

SQL query:

( 5, 'CLOTHING', 'MEN', 'Cargos' ) ;


MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5, 'CLOTHING', 'MEN', 'Cargos')' at line 1



Viewing: Dev Shed Forums > Databases >

[dungpt29]

SQL syntax:  INSERT INTO table_name (column1,column2,column3,...)VALUES (value1,value2,value3,...); only allows inserting one record per time. 

[Barand]

Use commas, not semicolons, to separate the individual records.

INSERT INTO table (a,b,) VALUES
(1, x),
(2, y),
(3, z);

Semicolon terminates query

[vinny42]

As a side note: be carefull when inserting multiple values at once in MySQL, some versions don't rollback so when the third set of values fails to insert, the first two may have made it into the table already and are not removed (even though when an insert query fails you'd expect none of the data to be saved).

 

Test this error on your database before using this method in production. (and ofcourse always use InnoDB and transactions!)

[Yohanne]

Hi laxi.

 

use array implode to insert multiple of records and from your every text box, create an id to identify array.