Last Key in MySQL

[Dris]

I'm writing a web application, and I need to be able to find the last key ID added to a given table. Or, better yet, I'd like to know the next key to be added before I even add one.

Such a vital and simple function *must* have been applied to SQL somewhere.

Thanks in advance!

 

[octane]

I'm writing a web application, and I need to be able to find the last key ID added to a given table.

I think you must be wanting mysql_insert_id() // get the id from the previous INSERT operation.

If you want to know the next id, take the id number returned and increment it!

Hope that helps?..

 

[Dris]

I think you must be wanting mysql_insert_id() // get the id from the previous INSERT operation.

Thanks, that'll do fine!

If you want to know the next id, take the id number returned and increment it!

Well, not necessarily. I think that if I'd deleted the last INSERT that I did, incrementing would return the ID of the deleted row, and MySQL doesn't fill in key gaps.

Anyways, thanks for the hasty response! I was at a stopping-point until I had that info.

 

[octane]

Thanks, that'll do fine!

Well, not necessarily. I think that if I'd deleted the last INSERT that I did, incrementing would return the ID of the deleted row, and MySQL doesn't fill in key gaps.

Anyways, thanks for the hasty response! I was at a stopping-point until I had that info.

Then you just do a SELECT MAX(id_col_name) FROM table WHERE... and use that number.

 

[Dris]

Okay, I did a little digging and I found out how to retrieve the next ID to be written to a table. Use the query "SHOW TABLE STATUS LIKE 'tableName';" and retrieve the column "Auto_increment". Here's how you'd do it in PHP:

$result = mysql_query('SHOW TABLE STATUS LIKE "tableName";');
$row = mysql_fetch_assoc($result);
echo $row['Auto_increment'];

I haven't tested that code yet, but it should work as described. Hope this helps somebody!