Instantly retrieving autoincrement value of new MySQL record

I'm building a forum-like site in PHP, and when a new post is submitted, a record is added to the POSTS table in the MySQL database. Each record is uniquely identified by an autoincrementing key assigned by the database at the time of its creation.

However, what I also want to do within the same function, is update the LATEST_POST field in the MEMBERS table for that member, with the unique id of the post they've just submitted.

The only method I can think of is to run a SELECT query on the POSTS table to find the record that's just been stored, using criteria such as the member id, and extract the post id from that. This seems deeply flawed however, as it makes assumptions about what has and hasn't happened to the data since the relevant INSERT query was run.

I suspect there's a much better way of obtaining the value I want, probably put into practice by many forum scripts out there. Anybody know?

 

Hi if i understood you correctly
you are looking to use

this function

$lastID = mysql_insert_id($link);

which will return the id of the last insert query

heres some reference

http://us3.php.net/mysql_insert_id

 

That sounds exactly what I'm looking for, thank you!

 

Hello all,

I have also same problem

The above example $lastID = mysql_insert_id($link); will work once we insert the value in the table.

But i need the Auto Increment value without inserting any value in the table.

For eg,

Table structure like this

id ( auto increment, primary key),
code ( varchar)

i want to store if the "id" (auto increment) value is 10, then the "code" should be "ABC10"

any one have idea?

 

$sql = "SHOW table status from database where name = 'table_name' "
$query = mysql_query($sql);
$result = mysql_fetch_assoc($query);
$auto_increment = $result["Auto_increment"];


also this "show table status" query gives you more data about your database like
(Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment)

in case you need any

 

Can't a simple select max(id) do the job?

 

Best way to do what you want is a trigger on the posts table. Check this for information on triggers.

 

no, it can't

let me explain.
say you have 100 rows in your db with id's 1,2,3,4,5,...100.
now your auto-increment = 101.

so you get max(id)+1 and get 101.
everything seems to be working.

now, delete rows 98,99,100 from your db,
your auto-increment is still 101.
but now " max(id)+1 " query will bring you 98, a wrong result.

auto-increment number is different is not equal to (highest id + 1)

(though most times the two can be equal, does not mean they should be)

 

Oh yes yleico, very good point, auto-increment maintain their last values even if you delete the records!
You made a good point. You can only retrieve values by consulting system tables

That's why I avoid using auto-increments fields, they get messy when you replicate tables, from server to server.

 

What is $link? Do I need to set that variable?

 

You don't need it ,only if you have multiple db connections on your script