Getting the next highest index in a mysql table

[SCook]

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

[ToonMariner]

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.

[gavinandresen]

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