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!!

use the LAST_INSERT_ID function right after the INSERT

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.

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 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.

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.

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)

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.

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.

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

Thanks for the help. I am writing the scripts now. See how it works.

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

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.