Inserting multiple rows

Databases
, ,
1

Although I do know how to insert many rows in a table(with a single query), in the situation I am facing things are not so straight forward.Let me explain:

I have an app where the business user enters his services offered in a form and these consequently must enter a database…there would not be a problem if every user offered the exact number of services…the issue is that this varies.(the services go from the client to the server in an array).

Question:
Suppose we have 4 or 3 or 2 services to enter in the database…is it the best way to do it to use a for loop the runs a query that inserts one service at a time in the db?

2

I’m not 100% sure, but one by one sounds like the right way of doing it. Handling one service at a time, you could validate the insertion of each service separately and report if anything failed inserting a particular service. I would go with the for loop and then calling a method within: “insertService(…)”.

5

no

INSERT INTO mytable VALUES ( 23 , 'this is a row' , 'X' ) ,( 42 , 'this is another' , 'Y' ) ,( 66 , 'whaddya know, another' , 'Z' ) ;

6

I do not think this is the solution…as already stated the number of service to enter in the DB varies and your query/code deals only with the situation where are 3 services-in other words the number of services/rows to be filled is fixed.

7

okay, let me be more specific

you can insert one row at a time –

INSERT INTO mytable VALUES ( 23 , 'this is a row' , 'X' )

or, you can insert two rows at a time –

INSERT INTO mytable VALUES ( 23 , 'this is a row' , 'X' ) ,( 42 , 'this is another' , 'Y' )

or, you can insert three rows at a time –

INSERT INTO mytable VALUES ( 23 , 'this is a row' , 'X' ) ,( 42 , 'this is another' , 'Y' ) ,( 66 , 'whaddya know, another' , 'Z' )

or, you can insert fourteen rows at a time –

INSERT INTO mytable VALUES ( 201 , 'row201' , 'A' ) ,( 202 , 'row202' , 'B' ) ,( 203 , 'row203' , 'C' ) ,( 204 , 'row204' , 'D' ) ,( 205 , 'row205' , 'E' ) ,( 206 , 'row206' , 'F' ) ,( 207 , 'row207' , 'G' ) ,( 208 , 'row208' , 'H' ) ,( 209 , 'row209' , 'I' ) ,( 210 , 'row210' , 'J' ) ,( 211 , 'row211' , 'K' ) ,( 23 , 'this is a row' , 'X' ) ,( 42 , 'this is another' , 'Y' ) ,( 66 , 'whaddya know, another' , 'Z' )

are you seeing any kind of pattern here? you can insert as many rows in one INSERT statement as you wish

8

I understand what are you trying to say but I am saying that the number of services is not fixed…it varies from business user to business user.

The code you have written above(whatever we take for example) does not cater for that fact.

Unless you are trying to “say something else”…

I am little bit confused…sorry.
I am going to state it this way…how the code(the query) inside the functions that accepts the services array must be? Obviously it cannot be all of the above…
Code is needed here that will encompass all possible scenarios…as you depict above

9

Surely you can build up the INSERT query in PHP so it can handle an indeterminate number of VALUES?

10

that is what I am trying to find out and that is why I am saying maybe a for loop is needed.

11

@r937 gave you the syntax to do this with a single query. So you may use a loop of some kind to build such a query, as opposed to running multiple queries in a loop as per your OP.
But how you build the query would be a PHP question, not for the DB forum. Assuming PHP is your back-end.

12

got it…but you do agree that a loop is needed here…

13

Yes, a loop to build your single query.

14

thanks…all I have to do now is go to the PHP forum…thanks.

15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.