Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2006
    Posts
    7

    Unanswered: Auto Increment, multiple tables

    I have two tables, table A and table B. Table A has the primary key and is innoDB, so I can force integrity on other tables. However, I want to use Full text search, so I have another MyISAM table (table B) for these keywords. When I insert a row into table A, it is auto incremented. I want to tie that row in with a row of keywords in table B. I know I could insert the row into A, then immediately do a search for the last row added to get the Primary Key, however there is a chance that another row could be added in that split second. So, how can I solve this problem?

    Thanks!!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use the LAST_INSERT_ID function right after the INSERT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2006
    Posts
    7
    I think that statement does the same thing I said would possibly not work. What if two rows are being entered in at the same time. Oviously there is a chance that the that the last row inserted could be that of a different insert, not the one you just entered.

    The only way I can think of to get around this problem is add yet another field in the table where I can place a unique random number. Then I can do a select back on that random number to find the row I just inserted. I wish there was an easier built in way.

    Thanks for the help.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you tried it......


    to see if your theory holds water or not?
    to see if your supposition is anywhere near what MySQL & other db Vendors have implmented
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2006
    Posts
    7
    I cant test it, I cant submit two rows at the exact same time.

    If the site has a thousand users online at the same time, the chances increase that two people will do an insert at the exact same time. If a 2nd row is entered inbetween the first row insert and recall, both will recall the 2 insert as being the last row entered.

    For a low use site, I can see how the chances would be so low it is not worth worrying about. However, for a high traffic site, I have to take this chance into consideration.

  6. #6
    Join Date
    Nov 2006
    Posts
    7
    I have been reading some more about this, and my theory appears to be true.

    http://us3.php.net/mysql_insert_id

    The way someone suggested on this site was to lock the table, insert the row, request the id of the last row, then unlock the table.

    I dont think locking and unlocking the table it the answer. This would just cause the second row insert attempt to fail, causing errors for that second attempt trying to happen at the same time.

    I think the only real solution to this is as I mentioned earlier, create a new field where a random unique number can be inserted, then later called back to find that record just inserted.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    read this --
    http://dev.mysql.com/doc/refman/5.0/...unique-id.html

    notice where it says For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client

    this means your "at the same time" scenario (which is impossible, by the way) does not matter, because they are separate connections

    if you still won't trust it, i will ask you this: how can you be sure that the "random" number you plan to generate is truly random?

    there is a way that you can query back the row just inserted, and that is to query it back using the value of a unique key (not a random number)

    this unique key is called a candidate key in data modelling parlance -- it could be the primary key except for the fact that the auto_increment has to be the primary key (or else it cannot be an auto_increment)

    the neat thing about using a candidate key is that you do not have to lock the table -- there can be many inserts in between the time this one is inserted and the time it is queried back

    the only downside is that you are using 2 queries (the insert, followed by a select), whereas with LAST_INSERT_ID you are using only one (the insert)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    oddly enough I dont read it that way

    mysql_insert_id: Retrieves the ID generated for an AUTO_INCREMENT column by the previous INSERT query.

    the row/table locking is used when people try to find the highest used number.
    if you are using an autoincrement/autogenerated number then mysql_insert_id will give you the value of the row just inserted, it doesn't matter what other INSERTS/ writis are happening in the db, PROVIDED you dont use insert delayed.

    Im happy to convinced otherwise, but Ive yet to see the same problem you suppose in actual use.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2006
    Posts
    7
    So....here is my example.

    Table Photos
    PhotoID (auto increment)
    FileName (varchar)

    Table Keywords
    PhotoID (Integer not auto increment)
    Keywords (Text)

    INSERT INTO Photos (PhotoID,FileName)
    VALUES(NULL,'$FileName');

    INSERT INTO Keywords (PhotoID,Keywords)
    VALUES(LAST_INSERT_ID(),'$Keywords')

    I can see how this will not give a possible second insert between the insert and select, as there is no select to get that auto increment. When I initially read that, I thought you had to do an insert, then go back and do a select for the last insert id. If the above sql is correct, then this will work perfectly.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    after the insert return the value of PhotoID using mysql_insert_id

    so Id expect to see something like:-

    $strSQL2 = "INSERT INTO Photos (PhotoID,FileName)
    VALUES(NULL,'$FileName');";
    $sqlResult1($strSQL1);
    $PhotoID = mysql_insert_id();
    $strSQL2 = "INSERT INTO Keywords (PhotoID,Keywords)
    VALUES($PhotoID,'$Keywords');";
    $sqlResult2($strSQL2);


    how you handle you error processing is up to you (examing the error number is a good approach)
    Id suggest you should check that the insert invoked on strSQL1 is successfull before attempting to invoke $strSQL2
    Last edited by healdem; 11-24-06 at 10:26.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Nov 2006
    Posts
    7
    Thanks for the help. I am writing the scripts now. See how it works.

  12. #12
    Join Date
    Nov 2006
    Posts
    7
    It works, just as advertised. Of course the thing I was concerned about I can not test....and will probably never know...unless that small chance does happen.

    Thanks again for everyone's help!!!!

    ~Matthew

  13. #13
    Join Date
    Mar 2004
    Posts
    480
    Note that if you need to use the id but also do other inserts, you should save the last_insert_id in a variable. That way you won't lose it if you inadvertantly do another insert into a third table say. I've got an article http://guelphdad.wefixtech.co.uk/sql...insertid.shtml if you are interested in reading more.

Posting Permissions

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