Hi guys, im new to php and mysql. I'm writing a script that writes to a MySQL database, it stores the data in table1, now I have a field that needs to be inserted in another table (table2) but it needs to have a column with the ID of the row that I just wrote to table1. The ID in table1 is auto generated by the MySQL database, its a auto increment column.

This is being used for a notes section, where each individual would have multiple notes and the notes would be linked to the individual by their ID found in table1.

The first time you add an individual to the database you have the option of entering the first note in the form, I would need that note to be inserted to table2 with the ID that tabel1 just assigned the individual.

Could someone please help me on how I can get the ID after it writes it and write the notes field to the 2nd column with the ID.

    This will return the id of the last mysql query you performed.

    $rowid  = mysql_insert_id();

      Note that at times of high load on your server, mysql_insert_id might return the ID of an insert that happened between when you did your insert and your call to mysql_insert_id. The best way to get the ID is to actually select the id from the table you just inserted to. Then you know you're getting the correct ID 100% of the time.

      It's not likely to happen, but it can happen if there is a lag on the server, or two requests were sent off milliseconds apart. So if userA inserts, then userB inserts, the mysql_insert_id for userA will return userB's insert, and userB will get the correct ID.

      Just a word of caution.

        bpat1434 wrote:

        Note that at times of high load on your server, mysql_insert_id might return the ID of an insert that happened between when you did your insert and your call to mysql_insert_id. The best way to get the ID is to actually select the id from the table you just inserted to. Then you know you're getting the correct ID 100% of the time.

        It's not likely to happen, but it can happen if there is a lag on the server, or two requests were sent off milliseconds apart. So if userA inserts, then userB inserts, the mysql_insert_id for userA will return userB's insert, and userB will get the correct ID.

        Just a word of caution.

        My understanding is that it's the last auto-increment value created under the current connection, so that should only be an issue if you are using persistent connections (and thus is another reason to avoid using persistent connections unless you have a good reason to do so). However, I do not see this explicitly stated in the manual, other than a user's comment about persistent connections possibly affecting which ID you get.

          I don't see it either; however, I do see this in the caution (emphasis is mine):

          PHP Manual wrote:

          mysql_insert_id() converts the return type of the native MySQL C API function mysql_insert_id() to a type of long (named int in PHP). If your AUTO_INCREMENT column has a column type of BIGINT, the value returned by mysql_insert_id() will be incorrect. Instead, use the internal MySQL SQL function LAST_INSERT_ID() in an SQL query.

          So I think it might be connection independent; however, I could be wrong. But still, a select query that small is so minuscule, it really doesn't matter if you run it.

            Got me curious, so I found in the MySQL documentation for the LAST_INSERT_ID information function:
            [indent]The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions. [/indent]
            So you should be OK with mysql_insert_id() as long as you are not sharing the same persistent connection (i.e. using [man]mysql_pconnect[/man]).

              Lol... way to go above and beyond!! 😛 Good information to know though.

                bpat1434 wrote:

                Note that at times of high load on your server, mysql_insert_id might return the ID of an insert that happened between when you did your insert and your call to mysql_insert_id. The best way to get the ID is to actually select the id from the table you just inserted to. Then you know you're getting the correct ID 100% of the time.

                It's not likely to happen, but it can happen if there is a lag on the server, or two requests were sent off milliseconds apart. So if userA inserts, then userB inserts, the mysql_insert_id for userA will return userB's insert, and userB will get the correct ID.

                Just a word of caution.

                What code would i use to select the row i just inserted?

                  Given that you have to know some, most or all of the columns in the table, then you can just do a SELECT id FROM table WHERE ____ and specify all the same data over again.

                  But as NogDog said, you really should be okay using mysql_insert_id() as long as you're not using persistent connections.

                    bpat1434 wrote:

                    Given that you have to know some, most or all of the columns in the table, then you can just do a SELECT id FROM table WHERE ____ and specify all the same data over again.

                    This causes a problem as well. If you may have the exact same data in two rows (other than the auto_incremented id) this query could return more than one row, and then you don't know what row to use.

                      Thanks for the help guys. I have another question.

                      What I'm trying to do is print out all the customers that match the username of the admin currently logged in, but it would also need to print out the first note in table2 that matches the ID of the user that i just printed out.

                      I'm stuck on trying to pass on the ID of the customer I just printed to the notes query.

                      I'm thinking about putting the code for the notes query in the fetch_assoc code that's printing out the customer data. Would this be the logical way to go?

                        Write a Reply...