Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Unanswered: Is it possible to write a loop to insert?

    I figured it's time to ask another, possibly dumb question. I have a transactional procedure that is supposed to take 7 total columns and insert them into the database. I wanted to use this procedure so I could encapsulate the query in the transaction as well as cut down on some application code.

    The parent is table1. Table1 will have one row per "aop_id", the PK. Table2 can have many rows associated with the corresponding row in table 1.

    Here is an example of the data that needs to be stored.

    table1
    name
    address
    city
    aop_id


    table2
    aop_id
    phone
    phone_type

    Is there a way to loop the data in table2 using just SQL? Maybe a WHILE or FOREACH type of loop?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you mean a loop in MySQL to insert multiple rows? a loop isn't really necessary

    all you need is
    Code:
    INSERT INTO table2
     ( aop_id, phone, phone_type )
    VALUES
     ( 42, '416 555-0937', 'home' )
    ,( 42, '647 555-1234', 'cell' )
    ,( 42, '905 555-6789', 'bar' )
    ,( 42, '212 555-1234', 'fax' )
    the only looping you need to do is looping over your form fields in the input form, to construct the SQL statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Well, that is great news, Rudy. I thought I would need to write a complex statement to handle this. I'm still a bit unclear on how to implement this because my procedure call looks like this:

    For table1
    CALL insertSubscription('Tom', '1234 Main St', 'Los Angeles', 41);

    Then for table2 we still have the following 3 pieces of data that still need to stored. Without a loop of some sort, how will the database know how many times to iterate and when it should stop?

    My brain is thinking like a programmer. Before I can enter anything into the database, I'd have to loop over the data and for each row, issue an insert statement. I think this is why it is a bit confusing for me because I don't see that in your sample code.

    Will it matter that I have combined both of these insert queries into a single proc or should they be separated?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Frunkie View Post
    Without a loop of some sort, how will the database know how many times to iterate and when it should stop?
    okay, number one, the database does not iterate, and number two, all it needs to process is an INSERT statement

    so i'm not sure i understand what it is that you don't understand

    did you see my INSERT statement for table2? did you see how it inserts 4 rows of data? without a loop?

    Quote Originally Posted by Frunkie View Post
    My brain is thinking like a programmer. Before I can enter anything into the database, I'd have to loop over the data and for each row, issue an insert statement.
    yes, you do have to loop over the data, for as many times as you want rows inserted (in my example, 4 rows)

    but there is only one INSERT statement, not four

    you ~could~ do it with four separate INSERT statements, but that would be less efficient


    Quote Originally Posted by Frunkie View Post
    Will it matter that I have combined both of these insert queries into a single proc or should they be separated?
    no, a single proc is better


    helps?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    In your example, you provided 4 rows of data so I'm making the assumption that these rows were predetermined. If I were doing this in a programming language, I would loop over the user supplied data and and at the same time, feed those results into an insert statement. I have no way of knowing how many selections the user may have made and is why I use a loop. The loop stops when the data stops and quits issuing an insert statement to the database.

    In this example, the user may choose 4 phone types or may only choose 1 but not less. Your SQL doesn't appear to loop so I was just confused as to how the database would know the total number of rows to insert.

    The only other way I could see this working is by looping in my programming language and instead of issuing an insert statement like I normally would, I would just loop the procedure call instead. Yes?

    Yes, overall it does help.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Frunkie View Post
    In your example, you provided 4 rows of data so I'm making the assumption that these rows were predetermined.
    slightly wrong assumption -- they were constructed from four fields on the input form

    as i said, yes, you have to "loop over" the form fields

    Quote Originally Posted by Frunkie View Post
    In this example, the user may choose 4 phone types or may only choose 1 but not less. Your SQL doesn't appear to loop so I was just confused as to how the database would know the total number of rows to insert.
    becasue the INSERT statement is constructed by your procedure, one row at a time, as your procedure loops over the form fields

    if the looping over the form fields discovers that there is only one filled in, then the INSERT statement, when it is finished being constructed, has only one row's worth of data in the VALUES clause

    at the end of figuring out how many rows of dta there are, the INSERT statement -- remember, there's ony one of these -- is submitted

    maybe what's confusing you is that you wrote your procedure to accept only one field, so you have to call it four times?

    let me suggest that you look at my original INSERT statement, and imagine how you would construct it if there were four sets of data to be inserted

    imagine that you were only going to submit the INSERT statement once, but that it could carry a variable number of rows of data, and you had to figure out how many of these rows to submit before you actually submitted anything

    think like a programmer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937 View Post
    maybe what's confusing you is that you wrote your procedure to accept only one field, so you have to call it four times?
    Actually, I wrote the procedure to accept 7 fields. I was under the impression though that when writing a procedure, that the database was doing the looping. I say this because when I was at that horrific state job, there were procedures written that looped and inserted data at the same time.

    Quote Originally Posted by The Spaghetti Monster
    let me suggest that you look at my original INSERT statement, and imagine how you would construct it if there were four sets of data to be inserted
    Here is how I would handle that.

    loop the data with my application code. and while the application code is looping over the data, the procedure would be inserting the data, one row for every row found, once for every loop where data was present.

    [loop begin]
    CALL myProc(var1, var2,var3,var4,var5,var6,var7);
    [loop end]

    In the proc, I would put the first 5 variables into the parent table INSERT statement and the remaining 3 in the child table INSERT. As the application code loop iterated over the data, the database would insert the current row.

    Quote Originally Posted by r937
    imagine that you were only going to submit the INSERT statement once, but that it could carry a variable number of rows of data, and you had to figure out how many of these rows to submit before you actually submitted anything
    Like an array?? If this is possible, this would be the way to go. Is it??

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Frunkie View Post
    ... and while the application code is looping over the data, the procedure would be inserting the data, one row for every row found, once for every loop where data was present.
    no

    you're right to loop over the data, but not about inserting the data for evey row

    during the loop, you just "collect" that data and use it to build the INSERT statement

    you said there has to be at least one set of data to insert

    so after the first loop, the INSERT statement looks like this --

    INSERT INTO table2
    ( aop_id, phone, phone_type )
    VALUES
    ( 42, '416 555-0937', 'home' )

    then you loop again, and after the second loop, the INSERT statement looks like this --

    INSERT INTO table2
    ( aop_id, phone, phone_type )
    VALUES
    ( 42, '416 555-0937', 'home' )
    ,( 42, '647 555-1234', 'cell' )

    notice you still haven't submitted it to the database yet

    after the third loop, it looks like this --

    INSERT INTO table2
    ( aop_id, phone, phone_type )
    VALUES
    ( 42, '416 555-0937', 'home' )
    ,( 42, '647 555-1234', 'cell' )
    ,( 42, '905 555-6789', 'bar' )

    and after the fourth loop, it looks like this --

    INSERT INTO table2
    ( aop_id, phone, phone_type )
    VALUES
    ( 42, '416 555-0937', 'home' )
    ,( 42, '647 555-1234', 'cell' )
    ,( 42, '905 555-6789', 'bar' )
    ,( 42, '212 555-1234', 'fax' )

    then you realize there is no more data, so the looping ends

    and now you finally submit the INSERT statement to the databas

    there is only one INSERT statement, not four


    Quote Originally Posted by Frunkie View Post
    Like an array?? If this is possible, this would be the way to go. Is it??
    take a look at this --

    INSERT INTO table2
    ( aop_id, phone, phone_type )
    VALUES
    ( 42, '416 555-0937', 'home' )
    ,( 42, '647 555-1234', 'cell' )
    ,( 42, '905 555-6789', 'bar' )
    ,( 42, '212 555-1234', 'fax' )

    if you call the stuff in the VALUES clause an array, then the answer is yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Thanks Rudy. That is a much better way of doing it.

    I just tried to insert 4 rows and 3 of them failed because of the unique constraint. I am going to have to take your advice and prepare the data and INSERT statement before it goes over to the database. Sheesh.. nothing is easy anymore. Well, besides my ex wife, of course.

    EDIT:

    Rudy, let me ask you another question if I may.. Say I wanted to count these rows of data before sending them over to the database and then pass in that number to the database. Is there a way that I could compare the total number of executed inserts against the number that I calculated outside the database? ...and if not, throw an exception or something??
    Last edited by Frunkie; 01-09-10 at 02:15.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Frunkie View Post
    Thanks Rudy. That is a much better way of doing it.
    thanks

    and since i ended up putting so much time and sweat into my explanation, perhaps you can tell me where i was deficient, why it took me so long to explain the concept, and, most importantly, how you would explain it to another developer so that it would be easier to understand than my effort

    i mean, go back and look at post #2, now that you understand it, and explain why this didn't work for you right off the bat
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Rudy, there is still something wrong and admittedly, I still don't understand. I thought I did last night but this still isn't working and seems impossible the way you are suggesting that I do this. Please let me state the question again because I know that I am not good at explaining exactly what I need sometimes.

    I have a stored procedure that encapsulates two insert statements for two related tables. The first table is the parent to the second table. The child table PK references the parent table PK. The parent table can have 1 and only 1 row per customer. The child table however can have 1 or many associated rows to each row in the parent table.

    In order to successfully enter data into both tables, keeping in mind the constraint, I must enter data into table1 and then into table 2. I fail to see how, when these two insert statements are encapsulated by a single SP that I am going to enter more than 1 row into table2. Because of the encapsulation of both insert statements, I am forced to provide the same information for table1 when inserting more then one row into table2. This fails.

    Am I wrong??

    Code:
    CALL myproc('t1c1','t1c2','t1c3','t1c4','t2c1','t2c2');

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Frunkie View Post
    Am I wrong??

    Code:
    CALL myproc('t1c1','t1c2','t1c3','t1c4','t2c1','t2c2');
    sadly, yes

    the problem lies entirely and completely within the scope of your programming logic inside the stored procedure

    which of course you have not shared (yet), and which i will be unlikely to help you with as i have limited experience with stored procedure commands

    i do not understand what you mean by "encapsulation" -- maybe the trouble lies here

    there is absolutely no reason why a stored procedure couldn't issue the multi-valued INSERT statement (example as above, for 4 rows at once)

    simply said, from a database perspective, your application logic inside the stored procedure is faulty
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    I understand. Maybe the logic is flawed after all. If you see anything that is not right, please let me know. The only possible way that I can see to successfully enter 4 rows of data into the phone table is to take out the phone insert statement from this SP and put it into its own SP and then call it for every row that needs to be entered.

    What I meant by encapsulation was just that both of these INSERTs are contained within the same SP.

    Just for reference, the PK for both of these tables is the phone column. This service is tied to their phone so phone seemed like the best way to go.

    Code:
    CREATE PROCEDURE insertuser(
    
    
        IN iName		char   (50)			,
        IN iAddress		char   (50)			,
        IN iCity		char   (50)			,
        IN iAopID		int    (11)			,
        IN iPhone		bigint (11)			,
        IN iPhoneType	char   (12)			,
    
    )
    
    BEGIN
    
      INSERT INTO user(
        name		,
        address		,
        city		,
        aop_id		,
        phone		,
      )
    
      VALUES(
        iName		,
        iAddress		,
        iCity		,
        iAopID		,
        iPhone		,
      );
    
      INSERT INTO phone(
        phone		,
        phone_type
      )
    
      VALUES(
        iPhone		,
        iPhoneType
      );
    
    END

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Frunkie View Post
    If you see anything that is not right, please let me know.
    this stored proc does not allow you to pass in all phone numbers

    you are hamstrung from the get-go

    Quote Originally Posted by Frunkie View Post
    and put it into its own SP and then call it for every row that needs to be entered.
    NOOOOOOOOooooo............!!!!!!


    could you please show the context for the CALL

    in other words, i want to see how you enter multiple phone numbers for a single user
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937 View Post
    this stored proc does not allow you to pass in all phone numbers

    you are hamstrung from the get-go
    Yes, I was leaning in that direction my own self.

    I'm really glad that you think it is best not to remove the phone INSERT from the SP and put it into its own SP. It feels right to leave them together and I just need to figure out how to enter multiple rows of phone numbers without having the transaction fail. This was why I asked initially asked about a loop in mysql. I thought I could loop the phone and phone type for each user and be done with it.

    This is the context of the call that I would use:

    Code:
    PHP Loop begin
    CALL insertuser('$name','$address','$city','$aopID','$phone','$phone_type');
    PHP Loop end
    As for entering multiple phone numbers, in this configuration, it is not possible because it violates the key constraint for the user table. I cannot see any other way to handle this without removing the phone and phone type from this SP and putting it into its own SP. Again though, I don't think that is the "correct" way of doing it.

    Thanks for helping me, Rudy.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •