Page 1 of 1

How to find last auto increment?

Posted: Fri Jul 18, 2003 7:06 pm
by RFairey
How do I discover the value of the auto increment after inserting a row using mysql_query()?

ie:

Have table threads with fields
threadID (primary key, autoincrement)
title
parentboardID

Code: Select all

$result=mysql_query("INSERT INTO threads (title,parentboardID) VALUES ('$title','$boardID')");
$threadID= //What goes here to find threadID of last entry?
$result=mysql_query("INSERT INTO posts (message,parentthreadID) VALUES ('$message','$threadID')");
It's no good using the most recent entry, since if two users post at once, that may not be the correct entry by the time the script runs.

Posted: Fri Jul 18, 2003 11:57 pm
by Stoker
$threadID = mysql_insert_id();

Keep in mind that autoincrement is a MySQL-only type thing, using this will make your script more difficult to port to other DB's later..

Posted: Sat Jul 19, 2003 7:59 am
by RFairey
Does that return the ID of the last insert from this script, or from all scripts running? If two users post, then this function will return only the most recent ID, so the one posted first gets lost.

Posted: Sat Jul 19, 2003 7:35 pm
by Stoker
It returns most recent for current thread yeah, you should capture it immediately after query and only once. If you have multiple db connection and queries in progress within the same thread (script) you should always use link and resource identifiers for every db function (as the manual indicates).

Posted: Sun Jul 20, 2003 7:02 pm
by fractalvibes
Stoker wrote:$threadID = mysql_insert_id();

Keep in mind that autoincrement is a MySQL-only type thing, using this will make your script more difficult to port to other DB's later..
Actually most modern RDBMS have some sort of autoincrement. You can fetch that ID you just inserted:

MS Sql - I think you query for @identity

DB2 - identity_val_local gets you the same.

Phil J.

Posted: Sun Jul 20, 2003 7:59 pm
by RFairey
Is there a mySQL equivalent for that?

Posted: Sun Jul 20, 2003 9:10 pm
by Stoker
most RDBMS have a sequencer type system yeah, but not many use Autoincrement columns the same way as mysql does..
Others are typically a INT column NOT NULL and default value is thefunctionthatgetsasequencenumber('sequencename') or something like that

Posted: Mon Jul 21, 2003 9:42 am
by RFairey
I see... so theres nothing in either raw SQL or PHP that can tell what number was reached?

I'll probably just assume that its so unlikely to happen (its a very small messageboard atm) that its not worth worrying about - thanks for your help tho.

Posted: Mon Jul 21, 2003 10:29 pm
by fractalvibes
I think what was previously posted:

$threadID = mysql_insert_id();

Should work ok for you. Sure, other RDBMS have different methods and options, start at value, increment value,generated by default, cache X # of IDs. The main concern is that each ID be unique, whether using an autonumber(Identity) field or a sequence object; otherwise the whole think falls apart...

Phil J.

Posted: Mon Jul 28, 2003 12:25 am
by Boss Hoss
I struggled with this at one time and you have to add the $connection string to get it to work..and the insert_id needs to follow the query call

$Result1 = mysql_query(...
$threadID = mysql_insert_id($my_connection);

where

$my_connection = mysql_pconnect(....
[as defined in your connection file]