Jump to content


Photo

Geting the highest primary key from a MySQL db with php


  • Please log in to reply
5 replies to this topic

#1 SCook

SCook
  • Members
  • PipPipPip
  • Advanced Member
  • 73 posts

Posted 10 August 2006 - 03:23 PM

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.

#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 10 August 2006 - 03:26 PM

out of curiosity, why do you need to know the next key before you insert it? why not insert it and then get the id you just put in with mysql_insert_id()?
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 SCook

SCook
  • Members
  • PipPipPip
  • Advanced Member
  • 73 posts

Posted 10 August 2006 - 03:37 PM

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 :-)

#4 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 10 August 2006 - 04:16 PM

<?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
?>

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

Orio.
Think you're smarty?

(Gone until 20 to November)

#5 sasa

sasa
  • Staff Alumni
  • Advanced Member
  • 2,804 posts
  • LocationHrvatska

Posted 10 August 2006 - 04:29 PM

lock table
insert some data
mysql_inser_id
unlock table
update data

#6 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 10 August 2006 - 05:21 PM

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 :-)


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

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 current connection, so you can be sure it's the record you just inserted.
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users