I have a script that ... and get one of the fields from mysql for the row that I have just entered (the ID field, which mysql increments)

from perldoc DBD::mysql

     $insertId = $dbh->{'mysql_insertid'}

     ...

     mysql_insertid
         MySQL has the ability to choose unique key values
         automatically. If this happened, the new ID will be
         stored in this attribute. This attribute is not valid
         for DBD::mSQL. An alternative way for accessing this
         attribute is via $dbh->{'mysql_insertid'}. (Note we are
         using the $dbh in this case!)
[download]

just a reminder, $dbh is the database handle, not the statement handle

also, look here

Will perl for money
JJ Knitis
(901) 756-7693
gt8073a@industrialmusic.com

-Blake

Also note that (from the mysql pages):

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).

i.e.


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
[download]



*G*

That's interesting. If that means what it sounds like it means, I'm at a loss to explain the results of my experiment, where I ran the same script twice, and made the first run hang after it had written the db entry but before it got the id... then it restarts after the other run has written *its* db line, and the first run ends up getting the id created by the second run. It's more an academic point for me now, but I'd still be curious to know what was going on.

§ George Sherston

As I replied to you on DevShed, assuming it was you, of course:

mySQL does support locking, so you can reliably retrieve the last ID from it with a normal SQL query.

It might flow like this: 

prepare your insert statement and get all your data ready,
lock tables
execute your statement and insert the new data
retrieve the last id (which will be from what you just inserted) with 
+a select query
unlock tables
do stuff with your id
[download]

Just remember to keep your tables locked for as short a time as possible.

-Any sufficiently advanced technology is
indistinguishable from doubletalk.

Works for any database.

____________________
Jeremy
I didn't believe in evil until I dated it.

That'll work 99.999...9 % of the time. But it's that 0.000...1% of the time that'll get ya (i.e. I wouldn't do this with critical data).

Hmmm. Not following you. When will it not work? If you are referring to the chance of getting two identical random numbers at the same time, use 50 character strings. Use 100 character strings. And then combine them with the date, time, the phase of the moon and your pid. Date + time + pid alone should be enough, though.

____________________
Jeremy
I didn't believe in evil until I dated it.