Clever Geek Handbook

MYSQL: SQL query to get value of autoincrement field

I have a table. Primary key id

and its automatic increase. Now when I insert a new record, I need to get the id with which the record was updated. How can i do this? If I use query ...

select max(id) from table_name

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

I am using php and mysql.

Can ayone prove me an example of a code snippet in php with mysql?

+2


source to share


7 replies


If you want to know what is the new id from the auto increment column, you just need to run mysql_insert_id()

right after your insert request.

Same:



//--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 $newest_id

will now contain the id of the last inserted row.

+5


source


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



+3


source


In SQL, you can do this

SELECT LAST_INSERT_ID()

In php, you can call mysql_insert_id ()

+2


source


Using the same connection, run the following query:

SELECT LAST_INSERT_ID()

Your database driver may have a handy function for this.

Read the docs.

+1


source


you can use this to get the next auto_increment value (given that you have already connected to the server and selected 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 to be used further in the auto_increment column. EDIT: I'm sorry .. That wasn't your question ....

+1


source


Here's an 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));
0


source


Request

show create table

contains the information needed in the last line

0


source






More articles:


All Articles