Obtaining an auto-incrimented field value.

[Siberd]

Howdy.

I am writing an SQL/PHP forum as you may have guessed from my previous post. Its come time to add the reply functions. I need to obtain the post_id value from the database, as this is the only unique field.

Unfortunately, as this is the only unique field, i'm not sure how to get the post_id without already knowing it.

If there was a way to read a field using a query ID or something similar I should have no problem.. Can this be done?

Many thanks

/Sib
programmer in the making
icq: 44167565
e-mail: siberdude@ntlworld.com

 

[sleipnir214]

From PHP, see mysql_insert_id()

On the MySQL side, see last_insert_id()[link]

MySQL maintains the last-generated insert ID on a per-connection basis, so use of these functions is multiuser-safe.


Want the best answers? [url=http://www.catb.org/~esr/faqs/smart-questions.html]Ask the best questions!

TANSTAAFL!!

 

[Siberd]

Thanks for the info, I managed to resolve my problem using a work around, which should be pretty safe.

I did a look up on the primary key, and took the highest value. Because I had not closed the SQL connection, till the end of the script, I am sure nothing else can be writtern to the database until the script completes, thus meaning the last primary key created has to be the correct one.

/Sib
programmer in the making
icq: 44167565
e-mail: siberdude@ntlworld.com

 

[sleipnir214]

You're using PHP, from which I infer you we are talking about a web application. With web applications, it is more than possible for two or more users to be running the same script concurrently.

Suppose "foo.php" inserts a record into a table with an auto_increment ID, then finds the maximum of the auto_increment values. Suppose further that UserA and UserB run the script concurrently. It is more than possible that the order of events will be:[ol][li]UserA's run of the script inserts a record[/li][li]UserB's run of the script inserts a record[/li][li]UserA's run of the script finds the maximum of the auto_increment column, which is UserB's record.[/li][li]UserB's run of the script finds the maximum of the auto_increment column, which is UserB's record.[/li][/ol]

UserA's run of the script and UserB's run of the script are now both operating on UserB's data. This will mess up your database and could expose sensitive information to the wrong user. This is a bad thing.

I very strongly recommend that you use PHP's mysql_insert_id() or MySQL's last_insert_id().


Want the best answers? Ask the best questions!

TANSTAAFL!!