Jump to content

Getting the next highest index in a mysql table


SCook

Recommended Posts

Hi all,

Here's the plot: I have a file naming system using the index of a new item with an underscore like this 25_pic.jpg. So, what I do is get the highest current index in the primary key of my table to which the image belongs and then increment it one higher for naming. Okay, this works so long as there are item in the table.

But here's where I hit a snag. I'm doing some testing and currently there's nothing in the table, but becuase of auto_increment, I'm up to index 5 or 6. Now, my code checks for the index, and if it comes back 0 or empty, I do 1_pic.jpg. Of course, this is not correct. So, my qeustion is two pronged.

First, is there a php function to grab this highest index, ie. what will be next, even if the table is currently empty?

And second, is there a way to reset the indexes back to 0?

Thanks gang
Link to comment
Share on other sites

OK.

First when you empty your table you can make sure the auto_insert counter returns to 1.

In phpmyadmin goto the table you want, click teh operations tab at the top and at the bottom of the resulting page you can reset teh counter.

The alternative is to do something with the mysql_insert_id function. After inserting into the table you can update the field of interest using the value rerurned from the mysql_insert_id function. A bit more code but not difficult AND will mean you don't have to worry about it again, even if you empty the table, once it is working.
Link to comment
Share on other sites

[!--quoteo(post=359045:date=Mar 27 2006, 04:56 PM:name=SCook)--][div class=\'quotetop\']QUOTE(SCook @ Mar 27 2006, 04:56 PM) [snapback]359045[/snapback][/div][div class=\'quotemain\'][!--quotec--]
First, is there a php function to grab this highest index, ie. what will be next, even if the table is currently empty?

And second, is there a way to reset the indexes back to 0?
[/quote]

You using MySQL? If you are, the SQL query:
[code]  SHOW TABLE STATUS tablename LIKE 'Auto_increment';
[/code]
... will return the next auto-increment value.

BUT-- could there ever be two people adding images at once? If so, you might get the wrong value. Generally it's better to INSERT the record into the database and then SELECT LAST_INSERT_ID(); to get the value just inserted (you'll always get the right value there, because LAST_INSERT_ID() is connection-specific).

ALTER TABLE can reset the auto-increment value to whatever you like, and TRUNCATE [i]tablename[/i] will reset it to zero.
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.