How do i get the SQL query ID after processing it?

Question

Hi,

This a bit looks complicated, i want to get the id column value of the processed query.

example:

$qu = mysql_query("INSERT INTO msgs (msg,stamp) VALUES('$v1','$v2')");  

i want to get the ID of this query after storing the infos in database.

if it's impossible, is getting the last ID record and adding 1 will be safe and guaranteed?

Note: i use PHP and mySQL

Thanks

Answer 1

Here are the MySQL docs on this.

http://dev.mysql.com/doc/refman/5.1/en/getting-unique-id.html

Answer 2

If it's an auto_increment column, use the PHP function mysql_insert_id.

Answer 3

Hope this helps:

int mysql_insert_id ([ resource $link_identifier ] )

http://us3.php.net/mysql_insert_id

Answer 4

If you are using standard mysql in PHP, all you have to do, is to use mysql_insert_id() which takes resource $link as parameter (your mysql connection)

http://php.net/manual/en/function.mysql-insert-id.php

Basic usage:

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

    mysql_query("INSERT INTO mytable (product) values ('some product')");

    echo 'Last inserted record has id of ' . mysql_insert_id();
?>

Answer 5

Hi,

check here if it`s not problem for you to use mysqli for php

Answer 6

No, it is not safe to grab the highest id and just add 1 and use that as the new id. This is what's known as a race condition. If two queries attempt to do this operation at the same time, it could be that they both select the SAME highest id, both attempt to add 1, and then BOTH attempt to use the same id (which would be bad).

It's much better to use AUTO_INCREMENT fields as you're doing, and you can retrieve the inserted id in PHP as follows (from http://php.net/manual/en/function.mysql-insert-id.php):

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

mysql_query("INSERT INTO mytable (product) values ('kossu')");
printf("Last inserted record has id %d\n", mysql_insert_id());
?>

Answer 7

Here's the code:

$qu = mysql_query("INSERT INTO msgs (msg,stamp) VALUES('$v1','$v2')"); 
$recent_id = mysql_insert_id();

The variable recent_id is what you are looking for.

Getting the last ID record and adding 1 MAY NOT be safe and IS NOT guaranteed. You are better off using the solution that I have indicated if you really want to play it safe.

Hope it helps.