Next Auto-Incremented Id BEFORE query

[kubluehm]

Hi

I have a slight problem. I need to be able to determine the next auto_increment value for my primary key BEFORE I do a query. I can get this AFTER a query using: mysql_insert_id();

The reason for this is because I am uploading images and need to concatinate the unique id to the filename, insert this value into the database, then upload the file with the concatinated name.

I want to be able to do this with as little extra work (queries, filereads, etc) as possible.

I considered storing the value in a text file a reading/writing it everytime an insert is done, but this seems to be a waste of resources and time.

I also considered storing the filename to perform the upload after the query is done, but this causes a problem where the db would need an update query to correct the filename in the database. (Another waste of resources/time)

Does anyone have any suggestions?

Thanks

[lig]

if you want to figure out the next auto increment why not do something like…

SELECT MAX(ID) AS last FROM table;
and then add 1 to last

Granted this assumes that the ID is the auto increment and that the auto increment hasn’t recyled itself through but it should work.

[kubluehm]

What about deleted items?

[lig]

In MySQL auto_incremented columns do not take into account deleted items. If you wish to then the column needs to be an INT with you handling the numbering.

The way I know is if a record is deleted you push that number onto a stack/array of available ID’s. When you go to insert a record you first check the stack to see if there is a number available. If there is you pop it off and us it as the ID. If not then you take the maximum number and add 1 to it and use that.

Not impossible and an interesting way to learn about stacks. Whether not it is what you want - I don’t know.

[kubluehm]

It doesn’t seem there is anyway to get away from another query, so I just did my mysql_insert_id after the insert, and then did an update on the name of the upload.

It uses 1 more query than I wanted, but it works.

[Racing_Time]

Hi,

Run a query to enter a blank record which will also fill in the increment field. Grab the record, get the number, do what you need to do, then edit that record using UPDATE.

So really you are creating the record and posting it… then going back and filling in the other fields once you have grabbed the increment number.

HTH