Page 1 of 1

Inserting records into normalized db

Posted: Sat Dec 19, 2009 5:13 pm
by LDusan
I posted question several months earlier, I researched a bit and decided to normalize my db.

So, I have, one table - books with all book relevant data, one authors table with author data, and one join table with book_id and author_id.

I was surprised I could not find the answer to a simple question - how do I add record into such, typical database. When a user fills the form with one book and authors, there should be one entry in books table, one or more entry in authors table and one or more entries in join table. How do I get IDs for the join table?

Re: Inserting records into normalized db

Posted: Sun Dec 20, 2009 5:17 am
by VladSun

Re: Inserting records into normalized db

Posted: Sun Dec 20, 2009 6:23 am
by LDusan
Exactly what I needed. Thanks a lot. I thought of:

Code: Select all

 
SELECT ID FROM BOOKS ORDER BY ID DESC LIMIT 1;
 
But why waste another query. Thanks.

Re: Inserting records into normalized db

Posted: Sun Dec 20, 2009 6:56 am
by VladSun
LDusan wrote:Exactly what I needed. Thanks a lot. I thought of:

Code: Select all

 
SELECT ID FROM BOOKS ORDER BY ID DESC LIMIT 1;
 
But why waste another query. Thanks.
It's also not session safe - if someone inserts a record between the execution of this query and your insert query you'll get a wrong ID

http://dev.mysql.com/doc/refman/5.0/en/ ... ue-id.html
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. It is not even changed if you update another AUTO_INCREMENT column with a nonmagic value (that is, a value that is not NULL and not 0). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed.

Re: Inserting records into normalized db

Posted: Sun Dec 20, 2009 9:41 am
by LDusan
Great! Thanks a lot!