auto increment problem

[Phrazer]

Hi,

i have the following problem:
i have 2 tables,
the first one has an ID as primary key (auto increment).
The second one has this ID as foreign key.
No if i want to add data, i write the data in the first table
(with the ID as primary key), then the db will add a new entry
with ID+1 as IDs value. (auto_increment)
Now the next step is to save the rest into the other table
with this new ID as key.
So i have to read the new ID first, before writing it in the second table.
BUT WHAT HAPPENS IF ANOTHER CLIENT WRITES INTO THE FIRST TABLE BETWEEN MY INSERT AND SELECT ???
Then i would read (the new) WRONG ID ???

I know i can lock the table, but this isn't whery fine, so is there a better solution ?

Please help,
phrazer

 

[KamranFeroz]

let me explain first..

what ur trying to do? I mean how u r placing the data in the db? either by query or by the help of ne front end?

Thats make easier to answer you.

 

[netcomander]

hi,

your solution was written into the mysql-manual,
so I copied and paste it for you here. This should
answer your question

here the original text out of the manual...

You can check if an AUTO_INCREMENT index is used by executing the following code. This also checks if the
query was an INSERT with an AUTO_INCREMENT index:

if (mysql_error(&mysql)[0] == 0 &&
mysql_num_fields(result) == 0 &&
mysql_insert_id(&mysql) != 0)
{
used_id = mysql_insert_id(&mysql);
}

The most recently generated ID is maintained in the server on a per-connection basis.

It will not be changed by another client.

It will not even be changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0).

If you want to use the ID that was generated for one table and insert it into a second table, you can use SQL statements like this:

INSERT INTO foo (auto,text)
VALUES(NULL,'text'); # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
VALUES(LAST_INSERT_ID(),'text'); # use ID in second table


netcomander