How do I know the auto-generated key in a newly-inserted row?

This seems like it would have a very common answer, I just haven't
stumbled upon it. Apologies in advance for my ignorance, and thanks
in advance for your help.

Let's say I have two tables:

Authors
=-=-=-=
id (PK)
fName
lName

Books
=-=-=
id (PK)
Authors.id (FK)
title

The id fields are auto-numbered. Never mind the fact that something
like ISBN might be a better primary key for Books, etc. Let's say I
want to populate them with a Web app that will allow someone to give
an author's name and books they've written. In the form, the user
tells me that Kurt Vonnegut wrote "Slapstick".

So I guess I need to do these:

INSERT INTO Authors (fName, lName) VALUES ("Kurt", "Vonnegut");
INSERT INTO Books (Authors.id, title) VALUES (???, "Slapstick");

The ??? in the second insert is where I need help. Do I have to re-
query between these inserts in order to get the author's id in order
to insert it into Books? How do I know in the second insert what the
author's id is? What are best practices surrounding this?

Clearly, I'm a bit of a noob at this so, again, I hope my ignorance is
tolerated.

Thank you for any help!

bk:

> INSERT INTO Authors (fName, lName) VALUES ("Kurt", "Vonnegut");
> INSERT INTO Books (Authors.id, title) VALUES (???, "Slapstick");
>
> The ??? in the second insert is where I need help.

In case you're using PHP, have a look at mysql_insert_id()

--
Erick

On Thu, 28 Feb 2008 21:01:17 +0100, bk <benjamin.kann@gmail.com> wrote:

> This seems like it would have a very common answer, I just haven't
> stumbled upon it. Apologies in advance for my ignorance, and thanks
> in advance for your help.
>
> Let's say I have two tables:
>
> Authors
> =-=-=-=
> id (PK)
> fName
> lName
>
> Books
> =-=-=
> id (PK)
> Authors.id (FK)
> title
>
> The id fields are auto-numbered. Never mind the fact that something
> like ISBN might be a better primary key for Books, etc. Let's say I
> want to populate them with a Web app that will allow someone to give
> an author's name and books they've written. In the form, the user
> tells me that Kurt Vonnegut wrote "Slapstick".
>
> So I guess I need to do these:
>
> INSERT INTO Authors (fName, lName) VALUES ("Kurt", "Vonnegut");
> INSERT INTO Books (Authors.id, title) VALUES (???, "Slapstick");
>
> The ??? in the second insert is where I need help. Do I have to re-
> query between these inserts in order to get the author's id in order
> to insert it into Books? How do I know in the second insert what the
> author's id is? What are best practices surrounding this?

SELECT LAST_INSERT_ID();

--
Rik Wasmus