Geting the highest primary key from a MySQL db with php

[SCook]

Hi all,

Here's my problem, I'm adding a new item to the databvase, and this item has an image also.  The image is named using the primary key of the item.  So, what I need is to get the next primary key index number before I upload the image and insert it into the db.  I used to knwo how to do this, but I haven't done it in ages, so now I'm up a creek :-)  Any help would be great.

[obsidian]

out of curiosity, why do you need to know the [b]next[/b] key before you insert it? why not insert it and then get the id you just put in with mysql_insert_id()?

[SCook]

Well, first, I don't use mysql_insert_id.  I use mysql_query, and the id is auto_incremented.  Now, I could just insert the data, then query the db again with the data to be sure I get the right key, but in the event the db is being used by many users, you could get incorrect data.  I guess that's probably the solution, but I know there's a way to do this, I just an't remember, or find the code :-)

[Orio]

[code]<?php
//dont forget to connect to the db
$query="SELECT id FROM `table` ORDER BY id DESC LIMIT 1";
$result=mysql_query($query);
$row=mysql_fetch_array($result);
echo $row['id']; //will echo the last id
?>[/code]

Just change "id" with the column name and "table" with the table's name.

Orio.

[sasa]

lock table
insert some data
mysql_inser_id
unlock table
update data

[obsidian]

[quote author=SCook link=topic=103726.msg413274#msg413274 date=1155224269]
Well, first, I don't use mysql_insert_id.  I use mysql_query, and the id is auto_incremented.  Now, I could just insert the data, then query the db again with the data to be sure I get the right key, but in the event the db is being used by many users, you could get incorrect data.  I guess that's probably the solution, but I know there's a way to do this, I just an't remember, or find the code :-)
[/quote]

ok, check out the manual for mysql_insert_id(). it's not a replacement for mysql_query(), it simply [b]RETURNS THE ID OF THE LAST INSERTED ROW[/b], which apparently is what you're after. so, you would do something like this:
[code]
<?php
$sql = mysql_query("INSERT INTO tableName (myColumns) VALUES (myValues)");
if ($sql) {
  // query successful, echo generated ID
  echo mysql_insert_id();
}
?>
[/code]

this removes the risk of someone inserting another record before you can query for the MAX(id). it also saves you from having to lock your tables (never a good option from a user interface). it returns the last inserted id from your [i]current connection[/i], so you can be sure it's the record you just inserted.