Jump to content

Geting the highest primary key from a MySQL db with php


SCook

Recommended Posts

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.
Link to comment
Share on other sites

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 :-)
Link to comment
Share on other sites

[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.
Link to comment
Share on other sites

[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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.