SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    JavaScript Guru (Big Ego) Arielladog's Avatar
    Join Date
    Jul 1999
    Location
    SC, USA
    Posts
    390
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is there a way to have multiple MySQL Insert commands? Here's the code I am trying to use from PHP, but it won't work:

    INSERT INTO Teachers (UserName,PassWord) VALUES ('pilky','pilky'); INSERT INTO Teachers (UserName,PassWord) VALUES ('moore','moore')

    How can I have it so that I can insert more than one new row?

    aDog

  2. #2
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Assign your values to an Array and use a while loop to insert them into the database.

    1 Insert for 1 row.
    Wayne Luke
    ------------


  3. #3
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But really to answer Ariella's question no you cannot make to inserts with one query to the database.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  4. #4
    JavaScript Guru (Big Ego) Arielladog's Avatar
    Join Date
    Jul 1999
    Location
    SC, USA
    Posts
    390
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey guys,

    Actually, I am creating the insert the thing from a while() loop, so it won't be hard to mysql_query() each one.

    Ok, I can do that. Is there a way to detect if all of the new rows have been inserted ok? Would I have to check each mysql_query() seperately?

    aDog

  5. #5
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could do under the insert statement
    $i = 1;
    while() {
    //your insert stuff
    if (mysql_affected_rows($result) == 0) {
    print "Error Printing Row".$i;
    }
    $i++;
    }
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  6. #6
    JavaScript Guru (Big Ego) Arielladog's Avatar
    Join Date
    Jul 1999
    Location
    SC, USA
    Posts
    390
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanx, I'll do something similar to that

    aDog

    P.S.-You sure got a smart cat

  7. #7
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freddydoesphp
    But really to answer Ariella's question no you cannot make to inserts with one query to the database.
    I beg to differ:

    INSERT INTO tblName (ColName1,ColName2,...) VALUES (Col1Value1,Col2Value1,...), (Col1Value2,Col2Value2,...), ...

    Or to use ADog's example:

    INSERT INTO Teachers (UserName,PassWord) VALUES ('pilky','pilky'), ('moore','moore')
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  8. #8
    Idea Developer
    Join Date
    Sep 2000
    Location
    Bethlehem, PA
    Posts
    521
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so it can be done, I always used loops.

  9. #9
    JavaScript Guru (Big Ego) Arielladog's Avatar
    Join Date
    Jul 1999
    Location
    SC, USA
    Posts
    390
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And I had just finished scripting my thing the other way I'll change it arround and use that way as it's probably better to query the server only once.

    Thanx kev,
    aDog

  10. #10
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Loops aren't bad if you're just inserting a couple of entries, but beyond a handful of entries, the loop becomes pretty unneccessarily intensive. More than 50 and you're shooting yourself in the foot.

    If you do need a loop (say, if you're inserting an indeterminate number of entries), then you're better off looping through the construction of the multi-INSERT query, then issuing the one query when you're done.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  11. #11
    JavaScript Guru (Big Ego) Arielladog's Avatar
    Join Date
    Jul 1999
    Location
    SC, USA
    Posts
    390
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Make up your mind

    I'm already doing the loop, but, one person will probably add all the teachers at once (in a big school, could be 100+), so I'll try the one query

    Thanx again

    aDog
    <Edited by Arielladog on 12-30-2000 at 12:34 AM>

  12. #12
    SitePoint Evangelist
    Join Date
    May 2000
    Location
    Canada
    Posts
    533
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    now only if you could do multiple SEPARATE inserts in the same line ! .. separate meaning different queries altogether .. i've had that issue in the past, dont know if the new mysql's can handle it
    cogito, ergo sum

  13. #13
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    By 'separate' do you mean 'into multiple tables'?

    If so, you've crossed into the realm of stored procedures, which MySQL does not yet support. You'll need a beefier database solution, like MS-SQL server, that supports such advanced features.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference


Bookmarks

Posting Permissions

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