How to obtain next insert ID without adding any data?

[ElegantElephant]

Is it possible to obtain the next insert ID without actually executing a proper insert query? I am writing a file upload script but the name of the folder the file gets uploaded to depends on the next insert ID of a certain table.

Would appreciate any help with this.

[Ezzaral]

That depends on what database you are using. Generally there is a function that you can call to check the current value of a sequence. The next insert ID will be one higher - unless another transaction gets it first.

Unless there is a reason you can't, perhaps you should insert the row and get the ID prior to creating the folder.

[kokoro90]

If you are talking about a numerical autoincrement field, the easiest is to use this query:

select max(insertid) from tablename;

You need to substitute the actual column and table name. Then you add 1 to the result and you have your next insert ID. As the above poster pointed out, there is the possibility of several requests getting the same ID. You will need some logic to prevent that.

[phpuser]

Hi,

Yes its true in kokoro90 's way you can get next id but as he told its risky about surity. so the better way to first insert data in database then get it id and then give it to your folder name in which you want to upload file.

it easy way and good also.you can also do what you want by this way.

Best of luck......

[ElegantElephant]

OK thanks a lot for your help guys really appreciate it :) I need to get he ID before I enter anything because otherwise if the file upload fails I'd need to write another query to delete everything I've inserted before.

[Ezzaral]

That is what transactions are for :)

[phpuser]

Hi,

ElegantElephant If you want to enter data if file upload successful, then you can check first file upload condition and if it uploaded properly only then you fire insert query.

And about file upload folder name, then first you have to create temp folder and upload file in same.and after insert query in table you can rename temp folder.

this is the way on which you can i think so..

[SikoSoft]

I advise against the method posted by kokoro90. I have used it in the past and have found it to be unreliable in many scenarios.

Imagine, for example that you are creating an image for each each of the IDs somewhere - let's say you are doing a banner ad table and each record has a corresponding png image somewhere named "1.png", "2.png", "3.png" and so on (for the ID in the database table). Using that method only pulls the maximum value and doesn't take into account records that have been DELETED - thus, if you add a few records, delete them for some reason and then add another banner ad, it will be given an ID that already has an existing image by that file name. There are other situations when this becomes problematic too, but all basically revolve around the fact that if you delete records after adding them and there are external references to the IDs anywhere, things will fall out of sync.

Instead of checking for the MAX value, I would encourage you to instead check on mySQL's auto-increment value instead, as this avoids any of the pitfalls of using the MAX method. For example, here's a function that will get you the next ID (before inserting data):

function nextAutoID ($tbl)
	{
	$cq = "SHOW TABLE STATUS LIKE '{$tbl}'";
	$cqres = mysql_query($cq);
	$cqr = mysql_fetch_assoc($cqres);
	$nid = $cqr['Auto_increment'];
	return $nid;
	}

[digital-ether]

It is not a good idea to actually have the auto_increment column value in your insert. For very low frequency inserts it will work, but if there are every two inserts close to each other you have the chance they will try and insert the same value for the auto_increment column.

It is best to do an insert, and if you need to delete it just delete it. Auto increment columns are there for a reason, and you should let it do it's job.

You can lock the table writes before you check the auto increment value, then do the insert knowing it would not have changed. That would not be efficient however as you're adding the latency of sending the query to your table lock, instead of having each query run asynchronously.

Considering failed uploads should be a low percentage, it would be more efficient to do an insert blindly then do any deleting if needed.

[loganphp]

But i used following code in many projects

mysql_query("SHOW TABLE STATUS LIKE '{$tbl}'");

.

It is only best way for obtain next auto increment id,but if there are every two inserts close to each other you have the chance they will try and insert the same value for the auto_increment column. So use proper validation before every insert query to avoid this issue easily.