MySQL - how to know the auto increment id of row

Question

I want to insert data into a mysql database with orders for my site.

So i store the username and an auto increment order number in the database.

The question is, how i extract the order number out again.

I have tried this:

SELECT ordernumber FROM transactions WHERE.....

How to finsh this?

Answer 1

you can use

SELECT LAST_INSERT_ID();

more info on http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

Note that you cannot use the C API function to retrieve the value for LAST_INSERT_ID(expr) after executing other SQL statements like SELECT or SET. So when you do INSERT Statement and just after that you do

SELECT LAST_INSERT_ID();

it works but when there are other statements in the middle it will return 0 and the only way to get it is described bellow.

the other option is to sort DESC the table and get the first number

SELECT ordernumber FROM transactions ORDER By ordernumber DESC LIMIT 1;

Answer 2

just use mysql_insert_id function it will give you last inserted id.

Answer 3

call mysql_insert_id() function after the insert query executed code

Answer 4

It depends on what methods you are using to insert the data into the database.

For mysql_* functions, you can use the mysql_insert_id() function.

For PDO use and mysqli_*, see this answer: What is equivalent of mysql_insert_id(); using prepared statement?

Answer 5

can also use LAST_INSERT_ID() or mysql_insert_id

Answer 6

$lastOrderNumber = mysql_insert_id();
$sql = "SELECT ordernumber FROM transactions WHERE ordernumber = $lastOrderNumber;";

Answer 7

As said in the other answers you should use mysql_insert_id.

For example you can use this:

$sql = "INSERT INTO table VALUES ('username')";
$result = mysql_query($sql);
echo "ID of last inserted record is: " . mysql_insert_id();

The above will output this:

ID of last inserted record is: (The ordernumber here)

For more info about mysql_insert_id you can take a look here.

Answer 8

there is a function to know what was the last id inserted in the current connection

for example after inster data you can get as below

mysql_query('INSERT INTO transactions  VALUES(\'b\')');

$id = mysql_insert_id();

Answer 9

Another way is to run a query

select max(ordernumber) as lastordernumber from transactions;

here, lastordernumber is the desired value.