CODEWITHSUNDEEP
phpsqlmysql
user177785
user177785

Reputation: 2269

MYSQL: SQL query to get the value of autoincrement field

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?

Upvotes: 3

Views: 2711

Answers (7)

Anatoliy
Anatoliy

Reputation: 30103

Query

show create table

contains information you need in last string

Upvotes: 0

user254875486
user254875486

Reputation: 11240

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....

Upvotes: 1

Vertigo
Vertigo

Reputation: 2746

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));

Upvotes: 0

random
random

Reputation: 9955

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.

Upvotes: 5

dnagirl
dnagirl

Reputation: 20446

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

Upvotes: 3

Paul Dixon
Paul Dixon

Reputation: 301055

In SQL you can do this

SELECT LAST_INSERT_ID()

In php you can call mysql_insert_id()

Upvotes: 2

gahooa
gahooa

Reputation: 137502

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.

Upvotes: 1

Related Questions