How to find last auto increment?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
RFairey
Forum Commoner
Posts: 52
Joined: Fri Jun 06, 2003 5:23 pm

How to find last auto increment?

Post 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.
Last edited by RFairey on Sat Jul 19, 2003 10:28 am, edited 1 time in total.
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post 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..
RFairey
Forum Commoner
Posts: 52
Joined: Fri Jun 06, 2003 5:23 pm

Post 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.
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post 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).
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post 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.
RFairey
Forum Commoner
Posts: 52
Joined: Fri Jun 06, 2003 5:23 pm

Post by RFairey »

Is there a mySQL equivalent for that?
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post 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
RFairey
Forum Commoner
Posts: 52
Joined: Fri Jun 06, 2003 5:23 pm

Post 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.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post 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.
Boss Hoss
Forum Newbie
Posts: 1
Joined: Mon Jul 28, 2003 12:25 am

Post 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]
Post Reply