Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    3

    Unanswered: Retrieving an 'id' from a recent table entry?

    If I add a row to a MySQL table that has an auto-incrementing 'id' field, how do I get the 'id' that was created for this entry and be sure that it's correct?

    I could find the max 'id' before entering the row and then just add one, but it's possible that someone else could enter something between my retrieving the max id and entering the row (entries are done via a public web form). I could get the max after I enter, but the same problem arises.

    I couldn't find the row that has all the same values as what I just entered, because the 'id' is the sole attribute of the primary key so it's possible for two rows to be exactly the same except for their id.

    So, to recap, after executing the following:

    Code:
    mysql_query("INSERT INTO table (col2,col3,etc) VALUES (val2,val3,etc)",$conn)
    I want to get the id that was generated for that insertion.

    How would I do this?

  2. #2
    Join Date
    Nov 2003
    Posts
    91
    As long as the database connection hasn't been closed
    since the last insert operation, this is how I do it in VB.NET:

    Public Shared Function lastInsertID_openCon(ByRef myConnection As MySqlConnection) As Long
    Dim id As Long = -1
    Dim myDataSet As New DataSet

    Dim sql As String = "SELECT LAST_INSERT_ID()"

    Dim myCommand As New MySqlDataAdapter(sql, myConnection)

    Dim objCmd As MySqlCommand
    Dim objDR As MySqlDataReader

    objCmd = New MySqlCommand(sql, myConnection)
    objDR = objCmd.ExecuteReader(CommandBehavior.CloseConnecti on)

    If Not (objDR Is Nothing) And objDR.Read = True Then
    id = objDR.GetInt64(0)
    Else
    id = -1
    End If
    objDR.Close()

    'Remember to close the connection!
    Return id
    End Function

    If the database connection was closed after the insert
    operation prior to calling this function then LAST_INSERT_ID returns garbage.

    You're playing with fire if you do something like
    SELECT max(ID) from sometable;

    -lv

  3. #3
    Join Date
    Nov 2003
    Posts
    3
    thanks for the info, but i figure out how to do it.

    I see now that I forgot to mention that I'm using PHP for this.

    my apologies.

Posting Permissions

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