SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2006
    Posts
    105
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Get ID of multiple rows insert

    PHP Code:
    $query "INSERT INTO choice(choice_name, choice_type) VALUES ('choice 1', 'r'), ('choice 2', 'c'), ('choice 2', 'r')"
    If I'm inserting multiple records at once, I want to insert in one query for better performance and is there a way to get the generated auto_increment ID of EACH inserted row ? Is this possible ? if not then I will do multiple query.

    Thanks
    QwerQ - Natural Language is the Command
    QwerQ.com

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You can get the LAST id added by various methods, and if you know how many rows you inserted, you now know all the earlier ids as well, don't you.

    (you can get the last id via php, or asking for max(id))

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2006
    Posts
    105
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Aha....thanks that gives a clue.
    QwerQ - Natural Language is the Command
    QwerQ.com

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Dr John View Post
    You can get the LAST id added by various methods, and if you know how many rows you inserted, you now know all the earlier ids as well, don't you.

    (you can get the last id via php, or asking for max(id))
    You can't get the last id through max(id), because someone else might have inserted a new row in the mean time.

    And you might be right about getting the n ids before the last one inserted by your insert statement, it depends on if mysql does all the inserts of one insert statement without executing any other insert statement. I'm sure one of the mysql experts here can tell us that.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by eagerlearner View Post
    ... is there a way to get the generated auto_increment ID of EACH inserted row ?
    only if the table has some other unique key besides the surrogate auto_increment key -- which it should have!!

    in this case, let's assume that choice_name and choice_type together form a unique key

    then you would return the ids like this --
    Code:
    SELECT id
         , choice_name
         , choice_type
      FROM choice
     WHERE choice_name = 'choice 1' AND choice_type = 'r'
        OR choice_name = 'choice 2' AND choice_type = 'c'
        OR choice_name = 'choice 2' AND choice_type = 'r'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Dec 2006
    Posts
    105
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for the reply, that auto_increment ID is primary key so it's unique. I already got it using the clue given by Dr John.
    QwerQ - Natural Language is the Command
    QwerQ.com


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
  •