Jump to content

Archived

This topic is now archived and is closed to further replies.

Kris

Returning the next integer from an auto_increment field

Recommended Posts

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?

Share this post


Link to post
Share on other sites

[!--quoteo(post=333977:date=Jan 6 2006, 03:37 PM:name=SemiApocalyptic)--][div class=\'quotetop\']QUOTE(SemiApocalyptic @ Jan 6 2006, 03:37 PM) 333977[/snapback][/div][div class=\'quotemain\'][!--quotec--]

Anyone have a error-proof method of returning the next auto_increment integer?

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());
?>

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

[!--quoteo(post=333977:date=Jan 6 2006, 08:37 AM:name=SemiApocalyptic)--][div class=\'quotetop\']QUOTE(SemiApocalyptic @ Jan 6 2006, 08:37 AM) 333977[/snapback][/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?

 

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

 

:D

Share this post


Link to post
Share on other sites

[!--quoteo(post=333981:date=Jan 6 2006, 08:47 AM:name=LazyJones)--][div class=\'quotetop\']QUOTE(LazyJones @ Jan 6 2006, 08:47 AM) 333981[/snapback][/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

 

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

Share this post


Link to post
Share on other sites

[!--quoteo(post=333983:date=Jan 6 2006, 01:49 PM:name=obsidian)--][div class=\'quotetop\']QUOTE(obsidian @ Jan 6 2006, 01:49 PM) 333983[/snapback][/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

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

Share this post


Link to post
Share on other sites

[!--quoteo(post=333983:date=Jan 6 2006, 08:49 AM:name=obsidian)--][div class=\'quotetop\']QUOTE(obsidian @ Jan 6 2006, 08:49 AM) 333983[/snapback][/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.

 

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

Share this post


Link to post
Share on other sites

[!--quoteo(post=333986:date=Jan 6 2006, 08:55 AM:name=SemiApocalyptic)--][div class=\'quotetop\']QUOTE(SemiApocalyptic @ Jan 6 2006, 08:55 AM) 333986[/snapback][/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--]

 

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) 333989[/snapback][/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

 

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.

Share this post


Link to post
Share on other sites

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) 333977[/snapback][/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?

 

[!--quoteo(post=333989:date=Jan 6 2006, 02:00 PM:name=LazyJones)--][div class=\'quotetop\']QUOTE(LazyJones @ Jan 6 2006, 02:00 PM) 333989[/snapback][/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

Its a bit of a messy way of doing it, but thats looking like my only option at the moment I think...

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

[!--quoteo(post=334013:date=Jan 6 2006, 09:58 AM:name=AndyB)--][div class=\'quotetop\']QUOTE(AndyB @ Jan 6 2006, 09:58 AM) 334013[/snapback][/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 :)

 

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.

Share this post


Link to post
Share on other sites

[!--quoteo(post=334013:date=Jan 6 2006, 02:58 PM:name=AndyB)--][div class=\'quotetop\']QUOTE(AndyB @ Jan 6 2006, 02:58 PM) 334013[/snapback][/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 :)

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

[!--quoteo(post=334079:date=Jan 6 2006, 10:58 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 6 2006, 10:58 AM) 334079[/snapback][/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.

 

You, my friend, are a lifesaver.

Share this post


Link to post
Share on other sites
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'?

Share this post


Link to post
Share on other sites
Look it up first...

Share this post


Link to post
Share on other sites
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 --- "

Share this post


Link to post
Share on other sites
[quote]
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'?
[/quote]

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

Share this post


Link to post
Share on other sites
If you know the id of the image you're currently displaying is some value, and you want the next image with a larger id:
[code]
SELECT * FROM table WHERE id > {$id} AND cat='PHOTO' ORDER BY id ASC LIMIT 1
[/code]

[quote]But do you have to be a jerk?  You're a moderator, right?  Isn't part of your job to help?[/quote]
AFAIK, none of the moderators are paid to run this site, so it's none of their jobs to do anything.  Also, when fenway said "look it up first," I think he may have meant look up the next record that is of the same category and create a link for it.

Not everyone is out to get you, ya know?

Share this post


Link to post
Share on other sites
[quote author=mystic_bovine link=topic=82759.msg709862#msg709862 date=1191599960]
At least point me in a direction
[/quote]
I thought I did...

[quote author=fenway link=topic=82759.msg709354#msg709354 date=1191534821]
Look it up first...
[/quote]
Didn't realize that I had to say "in the table where you just put this record"; thanks for having my back, roopurt18.

[quote author=mystic_bovine link=topic=82759.msg709862#msg709862 date=1191599960]
But do you have to be a jerk?  You're a moderator, right?  Isn't part of your job to help?
[/quote]
It's quite obvious that in all the time you've spent here with your 7 posts and all that you haven't noticed that I've NEVER told anyone to "go searching" for anything online in any of my 5,749 posts.  Maybe from now on you'll be more receptive and less defensive.

Share this post


Link to post
Share on other sites
Fenway,

Sorry, I honestly read "Look it up first..." as "look it up first in the forum then post the question". 

Like I said I really appreciate the resource that this forum and phpfreaks.com is.  I'm slowly teaching myself PHP and mySQL and I've only posted 7 times because I usually find the answer first. I hope one day to be knowledgeable enough to post some answers.

Share this post


Link to post
Share on other sites
[quote author=mystic_bovine link=topic=82759.msg711294#msg711294 date=1191760383]
Fenway,

Sorry, I honestly read "Look it up first..." as "look it up first in the forum then post the question". 

Like I said I really appreciate the resource that this forum and phpfreaks.com is.  I'm slowly teaching myself PHP and mySQL and I've only posted 7 times because I usually find the answer first. I hope one day to be knowledgeable enough to post some answers.
[/quote]

No worries...

Share this post


Link to post
Share on other sites

×

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.