Find next autoincrement index beforehand

Anon

Ok, this is sort of an odd request, so let me try to explain it as best as I can. I have a user-system that, among other things, lets users upload pictures to the website.

The user's picture will be stored at [primary key of user's record in MySQL database].[jpg or gif]. I, somehow, need to know the primary key number of the user's record in the database ahead of time in order to rename the image on the server to it.

I cannot simply do the INSERT query first, then search for that record because one of the fields in the record would be the url to the image (ie: www.site.com/25.jpg).

I don't know if this is universal behavior or not, but in my MySQL server, if you delete a record, it will forever skip that record's key upon INSERT querys, so I cannot simply find the greatest key and then add one to that to find the next key number.

Ok, thats it. I know this is complicated, but if you could help I would really appriciate it.

Thanks in advance.

[deleted]

Well, you really don't need to store the URL to the image in the table. Think about it....you are storing the image at:

www.site.com/25.jpg

Which is:

www.site.com/[primary key].jpg

So, when you pull a row for a user out of the table, you know the image name is [primary key].jpg and you know where you store all your images. So, you could insert the row, then grab the key, then rename the image. In fact, this is really the safest way to do it. If you grab the key first, what happens when you have two users signing up at the same time? You might get unexpected results.

Anyway, if you want to grab the next autoincrement value from mySQL, you can execute this SQL query:

SHOW TABLE STATUS FROM db_name LIKE 'tablename'

Then there will be a column returned called Auto_increment which will hold the next value....

Matt Wade
http://codewalkers.com/

[deleted]

I'd just like to repeat that getting the auto_increment value without inserting a record is VERY VERY dangerous.

Do not do it. Never ever.