Getting the auto increment key value

[eazyGen]

Hi Guys,

 

Using MySQL, I have a table with an auto increment key. I add rows to this table happily enough.

 

However, is there a simple way of obtaining from the db the value that was allocated by the auto increment?

 

Many thanks in advance,

 

S

[floridaflatlander]

 

You can use a SELECT  query or mysqli_insert_id() after your mysqli_query if you need it right after an INSERT

 

I use: $last_id = mysqli_insert_id($dbc); // Get the items ID

[eazyGen]

 

You can use a SELECT  query or mysqli_insert_id() after your mysqli_query if you need it right after an INSERT

 

I use: $last_id = mysqli_insert_id($dbc); // Get the items ID

 

Looks good - thank you.

 

More information:

 

http://uk3.php.net/mysql_insert_id

 

S

[eazyGen]

Back again on this chaps.

 

I have looked at the documentation for mysql_insert_id() and it says:

 

mysql_insert_id() will convert the return type of the native MySQL C API function mysql_insert_id() to a type of long (named int in PHP). If your AUTO_INCREMENT column has a column type of BIGINT (64 bits) the conversion may result in an incorrect value. Instead, use the internal MySQL SQL function LAST_INSERT_ID() in an SQL query.

 

I have a bigint as the key if my table, so I looked at the LAST_INSERT_ID(). However, during testing I noticed that LAST_INSERT_ID() returned a value with a type of long, which I believe has a maximum value up to: 2,147,483,648. So this doesn't seem to be the solution.

 

Has anyone overcome this issue?

 

Many thanks in advance.

 

S

[PFMaBiSmAd]

I noticed that LAST_INSERT_ID() returned a value with a type of long

 

How exactly did you notice that, because the method used could be giving you false information or could be converting the value.

[eazyGen]

I noticed that LAST_INSERT_ID() returned a value with a type of long

 

How exactly did you notice that, because the method used could be giving you false information or could be converting the value.

I beg your pardon, I think I may have misled you.

 

I made use of the function mysql_insert_id() and then ran it through a debugger. The debugger I used was the one that comes with PhpED:

 

http://www.nusphere.com/products/php_debugger.htm

 

The result was a value of "long".

 

I then ran this code:

 

$qry = "SELECT LAST_INSERT_ID()";

$b = mysql_query($qry);

 

and $b ended up with a type of mysql result.

 

Perhaps I am doing something wrong here. My most important issue is that I must know the type of the integer that comes out of the code as I need to populate a BIGINT with a length of 13.

 

I have searched around but I cannot seem to find a great deal of clarity on this topic. If I cannot get this method to work, I may have to use a different technique.

 

Any comments most welcome.

 

S

[kicken]

I then ran this code:

 

$qry = "SELECT LAST_INSERT_ID()";

$b = mysql_query($qry);

 

You want:

 

$qry = "SELECT LAST_INSERT_ID() as newId";
$res = mysql_query($qry);
$row = mysql_fetch_assoc($res);
//now $row['newId'] holds your value