reading auto increment number before it is written?

[x0054]

Ok, this is a stupid problem, I admit. I have a scrip that adds records to
a table. The records are for photos. So, after adding a record the scrip
also uploads a picture from users computer and renames it to
{$recordID}.jpg .

The problem is, my recordID field is auto incrementing in mySQL. So
currently I just take the last added record and assume that it the one I
just added, and rename the file to the recordID of that record. It works,
but clearly isn't the best way to do this as 2 users can add records at the
same time and then I would have a problem.

So what I am wondering is if there is a way to write a record to mySQL and
then have mySQL return recordID of the record just written?

- Bogdan

[ImOk]

Re: reading auto increment number before it is written?

>From the docs:

LAST_INSERT_ID([expr])
Returns the last automatically generated value that was inserted into
an AUTO_INCREMENT column. See section 8.1.3.130 mysql_insert_id ().
mysqlSELECT LAST_INSERT_ID( );
-195

The last ID that was generated 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
insert many rows at the same time with an insert statement,
LAST_INSERT_ID( ) returns the value for the first inserted row. The
reason for this is to make it possible to easily reproduce the same
INSERT statement against some other server. If expr is given as an
argument to LAST_INSERT_ID( ), then the value of the argument is
returned by the function, and is set as the next value to be returned
by LAST_INSERT_ID( ). This can be used to simulate sequences: First
create the table:
mysqlCREATE TABLE sequence (id INT NOT NULL);
mysqlINSERT INTO sequence VALUES (0);

Then the table can be used to generate sequence numbers like this:
mysqlUPDATE sequence SET id=LAST_INSERT_ ID(id+1);

You can generate sequences without calling LAST_INSERT_ID( ), but the
utility of using the function this way is that the ID value is
maintained in the server as the last automatically generated value
(multi-user safe). You can retrieve the new ID as you would read any
normal AUTO_INCREMENT value in MySQL. For example, LAST_INSERT_ID( )
(without an argument) will return the new ID. The C API function
mysql_insert_id () can also be used to get the value. Note that as
mysql_insert_id () is only updated after INSERT and UPDATE statements,
so you can't use the C API function to retrieve the value for
LAST_INSERT_ID( expr) after executing other SQL statements like SELECT
or SET.

x0054 wrote:

Ok, this is a stupid problem, I admit. I have a scrip that adds records to
a table. The records are for photos. So, after adding a record the scrip
also uploads a picture from users computer and renames it to
{$recordID}.jpg .
>
The problem is, my recordID field is auto incrementing in mySQL. So
currently I just take the last added record and assume that it the one I
just added, and rename the file to the recordID of that record. It works,
but clearly isn't the best way to do this as 2 users can add records at the
same time and then I would have a problem.
>
So what I am wondering is if there is a way to write a record to mySQL and
then have mySQL return recordID of the record just written?
>
- Bogdan

[x0054]

Re: reading auto increment number before it is written?

"ImOk" <jon.macaroni@g mail.comwrote in
news:1152903982 .691416.105900@ m79g2000cwm.goo glegroups.com:

>>From the docs:

>
LAST_INSERT_ID([expr])
Returns the last automatically generated value that was inserted into
an AUTO_INCREMENT column. See section 8.1.3.130 mysql_insert_id ().
mysqlSELECT LAST_INSERT_ID( );
-195
>
The last ID that was generated 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 insert many rows at the same time with an insert statement,
LAST_INSERT_ID( ) returns the value for the first inserted row. The
reason for this is to make it possible to easily reproduce the same
INSERT statement against some other server. If expr is given as an
argument to LAST_INSERT_ID( ), then the value of the argument is
returned by the function, and is set as the next value to be returned
by LAST_INSERT_ID( ). This can be used to simulate sequences: First
create the table:
mysqlCREATE TABLE sequence (id INT NOT NULL);
mysqlINSERT INTO sequence VALUES (0);
>
Then the table can be used to generate sequence numbers like this:
mysqlUPDATE sequence SET id=LAST_INSERT_ ID(id+1);
>
You can generate sequences without calling LAST_INSERT_ID( ), but the
utility of using the function this way is that the ID value is
maintained in the server as the last automatically generated value
(multi-user safe). You can retrieve the new ID as you would read any
normal AUTO_INCREMENT value in MySQL. For example, LAST_INSERT_ID( )
(without an argument) will return the new ID. The C API function
mysql_insert_id () can also be used to get the value. Note that as
mysql_insert_id () is only updated after INSERT and UPDATE statements,
so you can't use the C API function to retrieve the value for
LAST_INSERT_ID( expr) after executing other SQL statements like SELECT
or SET.
>
x0054 wrote:

>Ok, this is a stupid problem, I admit. I have a scrip that adds
>records to a table. The records are for photos. So, after adding a
>record the scrip also uploads a picture from users computer and
>renames it to {$recordID}.jpg .
>>
>The problem is, my recordID field is auto incrementing in mySQL. So
>currently I just take the last added record and assume that it the
>one I just added, and rename the file to the recordID of that record.
>It works, but clearly isn't the best way to do this as 2 users can
>add records at the same time and then I would have a problem.
>>
>So what I am wondering is if there is a way to write a record to
>mySQL and then have mySQL return recordID of the record just written?
>>
> - Bogdan

>
>

Thank you very much, that's exactly what I was looking for.

- Bogdan