Returning the next integer from an auto_increment field

[zq29]

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?

[Honoré]

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

[zq29]

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.

[LazyJones]

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

 

[obsidian]

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

 

 

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

[zq29]

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

[LazyJones]

[!--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

[obsidian]

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

[zq29]

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

[AndyB]

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

[obsidian]

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

[zq29]

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

[AndyB]

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.

[ryanlwh]

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

[fenway]

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.

[renwoshin]

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

[mystic_bovine]

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

[fenway]

Look it up first...

[mystic_bovine]

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

[poe]

[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

[roopurt18]

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?

[fenway]

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

[mystic_bovine]

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.

[fenway]

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