Jump to content


Photo

Returning the next integer from an auto_increment field


  • Please log in to reply
23 replies to this topic

#1 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 06 January 2006 - 01:37 PM

I need to be able to pull the next number from an auto_increment field in my database. I started doing it like this:[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] id FROM table ORDER BY id DESC LIMIT 1 [!--sql2--][/div][!--sql3--] Then +1 to the returned value. But this won't always work, say you have id's 1,2,3,4 already set, then you delete the row with id 4, the next id will still be 5 but the above query will return 4 again. Anyone have a error-proof method of returning the next auto_increment integer?

#2 Honoré

Honoré
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts
  • LocationAntwerp - Belgium

Posted 06 January 2006 - 01:44 PM

[!--quoteo(post=333977:date=Jan 6 2006, 03:37 PM:name=SemiApocalyptic)--][div class=\'quotetop\']QUOTE(SemiApocalyptic @ Jan 6 2006, 03:37 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Anyone have a error-proof method of returning the next auto_increment integer?
[/quote]
Use function mysql_insert_id() to get the id of an inserted row.

Copied from the manual:
<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('mydb');

mysql_query("INSERT INTO mytable (product) values ('kossu')");
printf("Last inserted record has id %d\n", mysql_insert_id());
?>


#3 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 06 January 2006 - 01:47 PM

As far as I'm aware, mysql_insert_id() returns the last inserted id from a query in the current 'session', so one would have to insert a row, and then call that to return the id. I need to be able to return the next id without having to insert a row prior.

#4 LazyJones

LazyJones
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 06 January 2006 - 01:47 PM

[!--quoteo(post=333977:date=Jan 6 2006, 08:37 AM:name=SemiApocalyptic)--][div class=\'quotetop\']QUOTE(SemiApocalyptic @ Jan 6 2006, 08:37 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
I need to be able to pull the next number from an auto_increment field in my database. I started doing it like this:[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] id FROM table ORDER BY id DESC LIMIT 1 [!--sql2--][/div][!--sql3--] Then +1 to the returned value. But this won't always work, say you have id's 1,2,3,4 already set, then you delete the row with id 4, the next id will still be 5 but the above query will return 4 again. Anyone have a error-proof method of returning the next auto_increment integer?
[/quote]

Before the SELECT, insert one row, get the value, then delete it. Then you'll certainly know the next value...

:D

#5 obsidian

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

Posted 06 January 2006 - 01:49 PM

[!--quoteo(post=333981:date=Jan 6 2006, 08:47 AM:name=LazyJones)--][div class=\'quotetop\']QUOTE(LazyJones @ Jan 6 2006, 08:47 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Before the SELECT, insert one row, get the value, then delete it. Then you'll certainly know the next value...

:D
[/quote]

be careful, because different SQLs will handle IDs differently, and you could very well skip over an id entirely this way.

SA, i'd just do this (may be sloppy, but it works):
<?php
$nextID = mysql_result(mysql_query("SELECT MAX(id) + 1 AS max FROM tableName"), 0, 'max');
?>

hope this helps
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

#6 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 06 January 2006 - 01:55 PM

[!--quoteo(post=333983:date=Jan 6 2006, 01:49 PM:name=obsidian)--][div class=\'quotetop\']QUOTE(obsidian @ Jan 6 2006, 01:49 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
be careful, because different SQLs will handle IDs differently, and you could very well skip over an id entirely this way.

SA, i'd just do this (may be sloppy, but it works):
<?php
$nextID = mysql_result(mysql_query("SELECT MAX(id) + 1 AS max FROM tableName"), 0, 'max');
?>

hope this helps
[/quote]
Wouldnt that be the same as adding 1 to this though?[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] id FROM table ORDER BY id DESC LIMIT 1 [!--sql2--][/div][!--sql3--]

#7 LazyJones

LazyJones
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 06 January 2006 - 02:00 PM

[!--quoteo(post=333983:date=Jan 6 2006, 08:49 AM:name=obsidian)--][div class=\'quotetop\']QUOTE(obsidian @ Jan 6 2006, 08:49 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
be careful, because different SQLs will handle IDs differently, and you could very well skip over an id entirely this way.
[/quote]

What do you mean by this? Auto increment works differently? If so, there's no solution to this one.

Let's say you do it my way. Add a row, the ID becomes 7, delete it and next will (or will it?) be 8

#8 obsidian

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

Posted 06 January 2006 - 02:07 PM

[!--quoteo(post=333986:date=Jan 6 2006, 08:55 AM:name=SemiApocalyptic)--][div class=\'quotetop\']QUOTE(SemiApocalyptic @ Jan 6 2006, 08:55 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Wouldnt that be the same as adding 1 to this though?[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] id FROM table ORDER BY id DESC LIMIT 1 [!--sql2--][/div][!--sql3--]
[/quote]

yep... it's just letting SQL do all the work for you.

[!--quoteo(post=333989:date=Jan 6 2006, 09:00 AM:name=LazyJones)--][div class=\'quotetop\']QUOTE(LazyJones @ Jan 6 2006, 09:00 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
What do you mean by this? Auto increment works differently? If so, there's no solution to this one.

Let's say you do it my way. Add a row, the ID becomes 7, delete it and next will (or will it?) be 8
[/quote]

that's exactly right... when you insert a row, and the id becomes 7... delete it and insert a new one, the ID is now 8.
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

#9 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 06 January 2006 - 02:10 PM

But thats what I'm trying to avoid because...
[!--quoteo(post=333977:date=Jan 6 2006, 01:37 PM:name=SemiApocalyptic)--][div class=\'quotetop\']QUOTE(SemiApocalyptic @ Jan 6 2006, 01:37 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]But this won't always work, say you have id's 1,2,3,4 already set, then you delete the row with id 4, the next id will still be 5 but the above query will return 4 again. Anyone have a error-proof method of returning the next auto_increment integer?[/quote]

[!--quoteo(post=333989:date=Jan 6 2006, 02:00 PM:name=LazyJones)--][div class=\'quotetop\']QUOTE(LazyJones @ Jan 6 2006, 02:00 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
What do you mean by this? Auto increment works differently? If so, there's no solution to this one.

Let's say you do it my way. Add a row, the ID becomes 7, delete it and next will (or will it?) be 8
[/quote]
Its a bit of a messy way of doing it, but thats looking like my only option at the moment I think...

#10 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 06 January 2006 - 02:58 PM

Let me insert some lateral thinking here.

Given that it may not always possible to get the 'next' id value or that some code may return the wrong value for 'next', take a step back in your design and ask "why do I need to know/need to care about the 'real' value of the next auto-incremented id?". Assuming this isn't just a mental exercise, the answer to that question might set you on a better path for your design problem. Then again ... maybe it won't :)
Legend has it that reading the manual never killed anyone.
My site

#11 obsidian

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

Posted 06 January 2006 - 03:02 PM

[!--quoteo(post=334013:date=Jan 6 2006, 09:58 AM:name=AndyB)--][div class=\'quotetop\']QUOTE(AndyB @ Jan 6 2006, 09:58 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Let me insert some lateral thinking here.

Given that it may not always possible to get the 'next' id value or that some code may return the wrong value for 'next', take a step back in your design and ask "why do I need to know/need to care about the 'real' value of the next auto-incremented id?". Assuming this isn't just a mental exercise, the answer to that question might set you on a better path for your design problem. Then again ... maybe it won't :)
[/quote]

good point. in cases where i've needed a truly sequential pattern for whatever reason, i've added an extra column that i've populated myself.
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

#12 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 06 January 2006 - 03:04 PM

[!--quoteo(post=334013:date=Jan 6 2006, 02:58 PM:name=AndyB)--][div class=\'quotetop\']QUOTE(AndyB @ Jan 6 2006, 02:58 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Let me insert some lateral thinking here.

Given that it may not always possible to get the 'next' id value or that some code may return the wrong value for 'next', take a step back in your design and ask "why do I need to know/need to care about the 'real' value of the next auto-incremented id?". Assuming this isn't just a mental exercise, the answer to that question might set you on a better path for your design problem. Then again ... maybe it won't :)
[/quote]
Its for an image upload script, instead of having an extra column in my database for the image path, I'm renaming all uploaded images to the row id, so row 4 in my database corrisponds to ../images/4.jpg. So I need to know the next id to name my image, then should the upload be successful, insert the data into the database which picks up the same row id as the image name.

I have thought of inserting the data first, then naming the image but if the image doesn't upload for some reason the database is pointing to an image that doesn't exist. I suppose I could do it this way, then run a check to see if the image was uploaded, then remove the data if it wasnt. Or am I looking at this whole thing the wrong way?

Or I could just add an extra field for the image path...

#13 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 06 January 2006 - 03:37 PM

What I do is this:

One form to upload the image AND acquire the data I want to go into the database. If the image upload fails, then I don't write to the database. If the image upload is successful then I write to the database, use the mysql_last_insert (or whatever that function is) to determine the id of the database record I just inserted and then rename or move/rename the successfully uploaded image to match.
Legend has it that reading the manual never killed anyone.
My site

#14 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 06 January 2006 - 06:10 PM

i've done something like this. you need to check $_FILES['inputname']['error'] and also is_uploaded_file($_FILES['inputname']['tmp_name']) before inserting the path to the db. Also, if you have privilege to move the file, you might as well have the privilege to rename the file after the db entry is made.

On a side note, you can set the auto_increment value with the following query
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']ALTER TABLE[/span] tbl AUTO_INCREMENT = 4 [!--sql2--][/div][!--sql3--]
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#15 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 January 2006 - 06:58 PM

Wow! I missed a lot of action on this thread. That's what I get for sleeping in....

First, if you're simply making the name of the file the UID, then there's no need to store it -- simply store the path to the image. Second, if you really feel the need to get the next auto_increment value (which you should never, ever need to know), you can always query the table metadata directly:

SHOW TABLE STATUS LIKE 'myTableName';

And check the value of the "Auto_increment" (case-sensitive!) field, much like you would parse out an ENUM set.

Hope that helps.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#16 renwoshin

renwoshin
  • Members
  • PipPip
  • Member
  • 14 posts

Posted 07 January 2006 - 05:32 AM

[!--quoteo(post=334079:date=Jan 6 2006, 10:58 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 6 2006, 10:58 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Wow! I missed a lot of action on this thread. That's what I get for sleeping in....

First, if you're simply making the name of the file the UID, then there's no need to store it -- simply store the path to the image. Second, if you really feel the need to get the next auto_increment value (which you should never, ever need to know), you can always query the table metadata directly:

SHOW TABLE STATUS LIKE 'myTableName';

And check the value of the "Auto_increment" (case-sensitive!) field, much like you would parse out an ENUM set.

Hope that helps.
[/quote]

You, my friend, are a lifesaver.

#17 mystic_bovine

mystic_bovine
  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 03 October 2007 - 06:50 PM

My problem is similar to this. 

I have a database that I'm using to store my portfolio.    When a user clicks on one of the logos I designed they go to a gallery page that shows the logo as a larger image.  Then I would like them to be able to navigate to the next logo while in the gallery page by clicking "next" or "previous".  But the next record may not be a logo, it might be a newsletter.  I have a field for 'category' and there are 3 categories - logo, communication and web.

So how would I get the next record that where category = 'logo'?



#18 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 04 October 2007 - 09:53 PM

Look it up first...
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#19 mystic_bovine

mystic_bovine
  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 05 October 2007 - 03:59 PM

Thanks.  I had looked it up first, or I wouldn't have posted the question.  My search brought me to this thread.    I appreciate the help in these forums, and I appreciate the time people take to answer questions, and I appreciate the fact that the same question can get asked over and over again. 

But do you have to be a jerk?  You're a moderator, right?  Isn't part of your job to help?  At least point me in a direction - like "try searching for --- "

#20 poe

poe
  • Members
  • PipPipPip
  • Advanced Member
  • 143 posts

Posted 06 October 2007 - 01:58 AM

My problem is similar to this. 

I have a database that I'm using to store my portfolio.    When a user clicks on one of the logos I designed they go to a gallery page that shows the logo as a larger image.  Then I would like them to be able to navigate to the next logo while in the gallery page by clicking "next" or "previous".  But the next record may not be a logo, it might be a newsletter.  I have a field for 'category' and there are 3 categories - logo, communication and web.

So how would I get the next record that where category = 'logo'?


could you not: SELECT * FROM table WHERE category = 'logo'
then scroll through these records only




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users