Unable to insert multiple records in sql server using php

Question

I am facing issues while inserting multiple records in sql server using php.

Here is my query:-

 INSERT INTO [BP].[users](id,username,email,contact,status) VALUES
        ('12','sujata','[email protected]','8588841506','0'),
        ('13','sonali','[email protected]','7894561231','0'),
        ('14','khushboo','[email protected]','7894561230','0')

It shows me this error message always:-

Msg 103010, Level 16, State 1, Line 20

Parse error at line: 2, column: 49: Incorrect syntax near ','.

This is my query from php file:-

 $query = "INSERT INTO users(id,username,email,contact,status,num_update) VALUES  $import_data ;

This is what import data returns

('12','sujata','[email protected]','8588841506','0'),('13','sonali','[email protected]','7894561231','0'),('14','khushboo','[email protected]','7894561230','0')

When I print the query it shows:-

INSERT INTO users(id,username,email,contact,status) VALUES ('12','sujata','[email protected]','8588841506','0'),('13','sonali','[email protected]','7894561231','0'),('14','khushboo','[email protected]','7894561230','0')

When I print Sql Error,it shows:-

SQLSTATE: 42000
code: 103010
message: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Parse error at line: 1, column: 104: Incorrect syntax near ','.
SQLSTATE: 42000 code: 103010 message: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Parse error at line: 1, column: 104: Incorrect syntax near ','.

@Lakshitha:- When I enter insert single entry,it works perfect but facing issue with multiple entries. — shweta_kaushish
– shweta_kaushish, Commented Apr 20, 2018 at 5:21

Lakshitha · Accepted Answer · 2018-04-20 05:24:26Z

1

Try this

 INSERT INTO [BP].[users](id,username,email,contact,status)

 select  '12','sujata','[email protected]','8588841506','0'
  union

select  '13','sonali','[email protected]','7894561231','0'
 union

select '14','khushboo','[email protected]','7894561230','0'

answered Apr 20, 2018 at 5:24

Lakshitha

1281 gold badge1 silver badge14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lakshitha Over a year ago

You will have to split (by comma) each entry to distinguished the separate record and shall replace with a union. It is obvious that you have to alter your import date to generate the desired query. And not to forget you will have to get rid of from the parenthesis as well.

shweta_kaushish Over a year ago

:-This solution worked..I was able to insert multiple records in sql server only using this way...

shweta_kaushish Over a year ago

The only issue I am facing now is that each record is inserting twice in database..Any suggestions over this

Lakshitha Over a year ago

Could you upload the code anywhere so that I can have a look? My suggestion is that you need to make sure you generating dynamic correct and only once.

Tekin Güllü · Accepted Answer · 2018-04-20 05:56:15Z

0

Actually my php skill not wery good. But I suggest an approach for solve a dynamic one. First you can parse your string with regex or if value is an array you can loop at other ways

    ('12','sujata','[email protected]','8588841506','0'),
    ('13','sonali','[email protected]','7894561231','0'),
    ('14','khushboo','[email protected]','7894561230','0')

Then you can insert per row

edited Apr 20, 2018 at 5:56

answered Apr 20, 2018 at 5:50

Tekin Güllü

3631 gold badge4 silver badges19 bronze badges

Comments

Rahul · Accepted Answer · 2018-04-20 06:16:06Z

0

if you have csv file then you need to do run following code to insert data in table

$query = <<<eof
    LOAD DATA INFILE 'your csv file name'
     INTO TABLE BP_users
     FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
     LINES TERMINATED BY '\n'
    (id,username,email,contact,status)
eof;

$db->query($query);

answered Apr 20, 2018 at 6:16

Rahul

1,6151 gold badge12 silver badges18 bronze badges

5 Comments

shweta_kaushish Over a year ago

How to create chat room here,will discuss it there

shweta_kaushish Over a year ago

It seems I dont have previliges to chat..It shows me mssage hereYou must have 20 reputation on Stack Overflow to talk here.

Rahul Over a year ago

you can upload your code with csv file any where and give me url .

Rahul Over a year ago

now try chat.stackoverflow.com/rooms/169402/…

shweta_kaushish Over a year ago

Rahul,Thank you for all your help..You were really a support in all this troubleshotting!

Collectives™ on Stack Overflow

Unable to insert multiple records in sql server using php

3 Answers 3

4 Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related