Access the primary key while inserting a record

[Nick]

Is there a way to reference the 'primary key' field of a record you
are actually inserting at the moment.

Im using an 'int auto increment' field as the primary key.

I could reference it with a select statement and adding a 1 to the
last record's primary key. However, there must be a better way to do
this - what if someone adds a record right after I reference the last
one. I hope my question makes sense. Thanks! -Nick

[Mark]

Re: Access the primary key while inserting a record

Nick wrote:[color=blue]
> Is there a way to reference the 'primary key' field of a record you
> are actually inserting at the moment.
>
> Im using an 'int auto increment' field as the primary key.
>
> I could reference it with a select statement and adding a 1 to the
> last record's primary key. However, there must be a better way to do
> this - what if someone adds a record right after I reference the last
> one. I hope my question makes sense. Thanks! -Nick[/color]


You will have to study transaction and locking concepts, which are not
all that easy to understand and probably cannot be explained concisely.
I suggest you look at the on-line MySQL manual at
http://www.mysql.com/doc/en/index.html and do searches on "locking" and
"transactio ns".

You will probably need to lock the entire table, get a primary key,
increment the value, insert a new row with the new value, and then
release the lock, all within a transaction.

[Nikolai Chuvakhin]

Re: Access the primary key while inserting a record

nboutelier@hotm ail.com (Nick) wrote in message
news:<ce0d844c. 0307191156.4c3d 3dea@posting.go ogle.com>...[color=blue]
>
> Is there a way to reference the 'primary key' field of a record you
> are actually inserting at the moment.[/color]

No, but you can fetch it immediately AFTER the insert by calling
mysql_insert_id (). For mroe information, see The Manual:

http://www.php.net/manual/en/function.mysql-insert-id.php

Cheers,
NC

[Drazen Gemic]

Re: Access the primary key while inserting a record

On Sat, 19 Jul 2003 21:56:12 +0200, Nick wrote:
[color=blue]
> Is there a way to reference the 'primary key' field of a record you are
> actually inserting at the moment.
>[/color]

There is no portable way to do it, neither in SQL nor in PHP.
Some database engines alow developer to acquire id by calling
some specific API function (MySQL), other provide a service
that returns unique ID (Postgres). Some provide both.
[color=blue]
> Im using an 'int auto increment' field as the primary key.
>
> I could reference it with a select statement and adding a 1 to the last
> record's primary key. However, there must be a better way to do this -
> what if someone adds a record right after I reference the last one. I
> hope my question makes sense. Thanks! -Nick[/color]

It has to be done in transaction, or when table is locked, which is not
portable too....

Personaly, I call uniqid() method of PHP, that generates 13 character
string, or 13 digit hex number. Then I use fixed char(13) field as id.
This is somewhat slower them 8-byte long int, but it is portable across
SQL engines.

DG