MYSQL: SQL query to get the value of autoincrement field

Question

I have a table. The primary key is id and its auto incremented. Now when I insert a new record, I need to get the id with which the record was updated. How can I do that? If i use the query...

select max(id) from table_name

...after executing I can get the id. But can I be sure that its the id of the record that was just inserted? Because many people will be using the app at same time.

I am using php and mysql.

Can ayone provie me a sample code snippet in php with mysql?

Answer 1

Using the same connection, run this query next:

SELECT LAST_INSERT_ID()

Your database driver may have a convenience function for that.

Read the docs.

Answer 2

In SQL you can do this

SELECT LAST_INSERT_ID()

In php you can call mysql_insert_id()

Answer 3

Assuming you're using the MySQLi PHP extension, the command you want is mysqli_insert_id

Answer 4

If you want to find out what the newest ID from an auto increment column is, you just have to run the mysql_insert_id() function right after your insert query.

Like so:

//--connection already made to database
//--now run your INSERT query on the table
mysql_query("INSERT INTO table_name (foo_column) VALUES ('everlong')");

//-- right after that previous query, you run this
$newest_id = mysql_insert_id();

And now $newest_id will contain the ID of the latest row inserted.

Answer 5

Here is example in PHP:

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('mydb');

mysql_query("YOUR QUERY HERE", $link);
$inserted_id = mysql_insert_id($link));

Answer 6

Hi,

you can use this to get the value of the next auto_increment value (given that you already connected to the server and selected the db;

$query = "SHOW TABLE STATUS LIKE 'tbl_name'";
$result = mysql_query($query);
$auto_incr_val = mysql_result($result, 0, 'Auto_increment');
echo $auto_incr_val;

This will give you the value that will be used next in the auto_increment column. EDIT: I'm sorry.. That wasn't your question....

Answer 7

Query

show create table

contains information you need in last string