SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict agentforte's Avatar
    Join Date
    May 2007
    Location
    Toronto, ON, Canada
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question mysql_insert_id() question

    I want to get the last ID from one table and insert that value into another table. Please let me know if this is the right way.
    For some reason I think this would get the last id of the second table rather than the first table

    PHP Code:

    $sql 
    "INSERT INTO table1
              SET
                name = 'name',
                email = 'email'"
    ;

     
    // inserts values into first table. Note: first table has auto increment value ID
    $result=$db->query($sql);


    $sql "INSERT INTO table_2
              SET
                userID = '"
    .mysql_insert_id()."',
                entry = 'entry'"
    ;

    // inserts values into second table
    $result=$db->query($sql); 
    Does mysql_insert_id() return the last inserted id from the first or the second table?

  2. #2
    SitePoint Addict agentforte's Avatar
    Join Date
    May 2007
    Location
    Toronto, ON, Canada
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now that I wrote it out, I can see that mysql_insert_id() would only get info from the first query (since the second one was not executed yet)

    but if I made a third query into a third table, and the second table does not have an auto increment value, would it return an error, or the id from the first table?
    for example:

    PHP Code:

    $sql 
    "INSERT INTO table1
              SET
                name = 'name',
                email = 'email'"
    ;

     
    // inserts values into first table. Note: first table has auto increment value ID
    $result=$db->query($sql);


    $sql "INSERT INTO table_2
              SET
                userID = '"
    .mysql_insert_id()."',
                entry = 'entry'"
    ;

    // inserts values into second table
    $result=$db->query($sql); 

    // would the following give an error,
    // or would it return the id from the first query?
    // Note:  userID is not an auto increment value
    echo mysql_insert_id(); 

  3. #3
    play of mind Ernie1's Avatar
    Join Date
    Sep 2005
    Posts
    1,252
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql_insert_id

    Returns the value generated for an AUTO_INCREMENT column by the previous INSERT statement. Use this function after you have performed an INSERT statement into a table that contains an AUTO_INCREMENT field.
    mysql-insert-id
    my mobile portal
    ghiris.ro

  4. #4
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    would the following give an error, or would it return the id from the first query?
    If table_2 has any AUTOINCREMENT field then mysql_insert_id will return it. Otherwise, from the first query.

    Keep in mind that the argument the function expects is your connection resource, NOT a query, or query result!
    The arg is optional, it'll use the current connection if none is specified.

  5. #5
    SitePoint Addict agentforte's Avatar
    Join Date
    May 2007
    Location
    Toronto, ON, Canada
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cranial-bore View Post
    Keep in mind that the argument the function expects is your connection resource, NOT a query, or query result!
    The arg is optional, it'll use the current connection if none is specified.
    I understand what you mean about the resource (and not the query result)

    Does this mean if I am dealing with two datbases (and I have two connections) I can get the last id of the first database regardless of operations on the second database?

    Would the following code be correct assuming tables:

    Columns for table in database 1
    auto_inc_id_1, username, name, email

    Columns for table in database 2
    auto_inc_id_2, items, userID

    PHP Code:

    $result 
    $db1->insertRow();

    // returns auto_inc_id_1, in FIRST database
    $id mysql_insert_id($db1);

    $result $db2->insertRow($id);

    // returns auto_inc_id_2, in SECOND database
    mysql_insert_id($db2)

    // Still returns auto_inc_id_1, in FIRST database
    mysql_insert_id($db1


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
  •