[Resolved] Getting last_insert_id problems.

Kudose

Hello all.

I have a problem here. I cannot seem to get the Next ID to be inserted into my database. I have tried using LAST INSERT ID, but cant get it work right.

Here is my DB setup:
uid int(10) UNSIGNED No auto_increment

Here is my unworking code:

$sql = "SELECT LAST_INSERT_ID() FROM ".$prefix."inventory";
						$result = mysql_query($sql);
						$uid_sql = mysql_fetch_array($result);
						$uid = $uid_sql[0]++;

Any help would be great.

I just need to ID that would be inserted on the NEXT insert.

TIA.

🙂

devinemke

$result = mysql_query('SELECT MAX(uid) + 1 FROM ' . $prefix . 'inventory');
echo mysql_result($result, 0);

Kudose

Thanks devinemke, but I cant use that.

Since I have the column as auto_increment, 53 is the next ID to be inserted according to phpMyAdmin, but the MAX(uid) is 27.

Thanks thus far.

devinemke

SHOW TABLE STATUS has what you are looking for

Kudose

I'm not able to format the right syntax from the examples. Can you help me out just a little more?

I've got this, but I know it's wrong.

$sql = "SHOW TABLE STATUS FROM ".$prefix."inventory LIKE Auto_increment";
$result = mysql_query($sql);
$uid_sql = mysql_fetch_array($result);
$uid = $uid_sql[0];

devinemke

$sql = "SHOW TABLE STATUS LIKE '" . $prefix . "inventory'";
$result = mysql_query($sql) or exit(mysql_error());
echo mysql_result($result, 0, 'Auto_increment');

Kudose

You the man. Thank you!

Drakla

Kudose, can you explain what this is beign used for? I remember somebody using the table status to find the value of the next insert getting absolutely panned because of the race conditions that will occur if you're using it in a multi-threaded environment, where by the time you read it, it might have already changed when you go to use it.

LAST_INSERT_ID() is available after you do an insert in the same script, and you can also read it with [MAN]mysql_insert_id()[/MAN] - but note that if you do a multiple insert statement it'll only return the value of the FIRST item entered.

The reason your code doesn't work is that you just read it alone, with an optional connection handle

SELECT LAST_INSERT_ID()

And it returns the last insert YOUR script did to any table with an auto increment.

Kudose

Drakla, I just needed a quick fix to stick an ID number to an image without that ID having been generated yet.

I've learned alot from this thread though, thanks for BOTH of your inputs.