Jump to content


Mysql insert multiple data issue

  • Please log in to reply
4 replies to this topic

#1 laxi

  • Members
  • PipPipPip
  • Advanced Member
  • 53 posts

Posted 19 September 2013 - 03:52 AM

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)

(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


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 >

#2 dungpt29

  • Members
  • PipPip
  • Member
  • 29 posts

Posted 19 September 2013 - 06:21 AM

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

Edited by dungpt29, 19 September 2013 - 06:22 AM.

#3 Barand

  • Moderators
  • Sen . ( ile || sei )
  • 17,741 posts

Posted 19 September 2013 - 08:21 AM

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

(1, x),
(2, y),
(3, z);

Semicolon terminates query

Edited by Barand, 19 September 2013 - 08:22 AM.

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#4 vinny42

  • Members
  • PipPipPip
  • Advanced Member
  • 414 posts

Posted 19 September 2013 - 08:26 AM

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!)

#5 Yohanne

  • Members
  • PipPipPip
  • Advanced Member
  • 213 posts
  • LocationPhilippines

Posted 20 September 2013 - 01:08 AM

Hi laxi.


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

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users