SQL PHP question

[djpic]

I am inserting a new entry in a mysql database.  I have one field that is an "auto increment."  What I want to do is find what the "auto increment" value for the entry I am about to enter before I enter it.  In other words, ask the database if I was going to insert a new entry what would the "auto increment" value be equaled to.  Please let me know if there is anything in SQL that will allow me to do this.

 

thanks.

[frijole]

query for the auto-increment variable and add 1.

[Psycho]

I don't think there is a fool proof way of doing this unless you were to save the auto-increment value after every record save. The problem is that the next auto-increment value will not be MAX(id) + 1 if the last record created has been deleted.

 

Example, you just inserted a record with the ID of 15. The next auto-increment value will be 16. In that case it WOULD be MAX(ID) +1. But, if the record with the ID of 15 is deleted, the next ID would still be 16 even though MAX(ID) + 1 would now equal 15 (assuming there is a record with the ID of 14).

 

I'm curious why you would need to know the ID before the record is created. Might be a different way of approaching the problem that does not require this.

[frijole]

thats true......Is the objective to find the number of entries in the table? What useful information are you getting from this value?

[PFMaBiSmAd]

Logically what you are asking is back wards. You cannot accurately determine what the next id will be until you actually have it assigned due to an INSERT. See this recent thread - http://www.phpfreaks.com/forums/index.php/topic,184429.msg825262.html#msg825262

[revraz]

PHPMYADMIN knows what the next record will be, why can't it be grabbed via code?

[freenity]

guess you'll have to look it in the phpmyadmins code XD

I'll try to look it later

[PFMaBiSmAd]

The value that phpmyadmin displays is a theoretical value. If x people visit the site and insert x records while you are looking at the value, the value is out of date and has no meaning other than for display purposes.

[Psycho]

http://computer-vet.com/weblog/2006/07/05/find-next-autoindex-using-php-and-mysql.html

 

However, as PFMaBiSmAd alluded to you should NOT rely upon this. If two users access the script which pulls this value then both pages will have the same "Next Auto-Increment" value. But if both were to proceed with the record creation, only one would actually get that value and the other would get the subsequent value. I still think this is a proor approach.

[corbin]

http://us3.php.net/manual/en/function.mysql-insert-id.php

 

That can be used after the insert call to get the insert id.

 

I could be wrong (and I suspect I am), but I think that gets the last ID from the connection passed to it (or assumed).  That means that even if person X inserts into the table, the ID will be correct.  Once again, I could be wrong.  Testing now lol.

 

 

Edit:

 

I ran:

 

<?php

/*

CREATE DATABASE omg;

CREATE TABLE omg (
id INT AUTO_INCREMENT PRIMARY KEY,
lol varchar(255)
);

*/

error_reporting(E_ALL);

mysql_connect('localhost', 'root', 'root');
mysql_select_db('omg');
mysql_query("INSERT INTO omg (lol) VALUES ('php')");
sleep(30);
echo mysql_insert_id();


?>

 

And then in an SQL cmd line window, I inserted 10 or so rows, and it returned the ID of the record inserted in the php script.

 

Not sure if this helps since you asked for the ID before you insert it, but I'm not quite sure why you would need it before.

[PFMaBiSmAd]

The question being asked is how to get the next, unassigned auto increment id, not the last one that was just inserted.

[corbin]

Bleh you typed that while I was editting.

 

Yes, I was aware of that as mentioned in my edit, but why would he need that?  Why would he ever, EVER, need that?  If he does need the ID for some reason or other, maybe a subsequent query or something, he doesn't specifically need it before, and I can't think of why else he would need it before.

[Psycho]

Absolutely. Relying upon the Next Auto Increment value for any functional process is a disaster waiting to happen.

 

EDIT: This must be a homework question since there is an EXACT same post by another user today: http://www.phpfreaks.com/forums/index.php/topic,184429.0.html

[luca200]

you can run a query like this

 

SHOW TABLE STATUS FROM dbname WHERE name = 'tablename'

 

In the 'Auto_increment' column of the result you get what you're looking for

[luca200]

Absolutely. Relying upon the Next Auto Increment value for any functional process is a disaster waiting to happen.

That's true if you can't lock the table.

[corbin]

Edit: Oh... you went down the locking path.  I've seen servers literally with 20 second queries because of profuse amounts of locks.  When ever there's a lock, each insert or update gets added to waiting list.  What if he has high traffic?

 

luca200, that won't [always] work.  What if someone else has an insert run inbetween running the query and using the value?

 

And, to correct my "Why would he ever, EVER, need that?" from earlier before someone else sticks my shoe in my mouth, as shown by the other thread, I guess there are situations where it could be nice to know ahead of time (although I think using an autoincrementing, hence guessable value for order IDs is a bad idea [although authorization would obviously almost eleminate that problem]).  (I don't personally see an advantage of giving the user the order ID ahead of time since it's not technically ordered at that point, but what ever... lol)

[luca200]

We should know exactly what he needs...

you're right, locking tables could cause trouble, and most of all, the best way is inserting the row before other things.

 

But strictly speaking, if he absolutely wants to do that, he can

 

Edit: of course he cannot lock the table for showing the id to the customer, as the lock would be lost between two subsequent http calls. In case that was the problem

[Psycho]

Well, I still think this makes no sense trying to determine the value before it is created since it can, and will, be wrong in some instances.

 

How about this: Once a user starts the order process (by adding products to the cart) create a record in the orders table so you can generate an order number. But, have an "isOrder" column and set the value initially to "0" (false). One the order is finalized, set the "isOrder" column to "1" (true). You can then query for that order number any time before the order is completed. The only downside would be that any order not completed would take up an order number record, but they could be easily weeded out using the "isOrder" column.

[frijole]

isn't it funny we have been debating this question for so long now and the person who actually asked it hasn't even replied yet? It's all part of the learning experience though I guess.

[djpic]

lol...so sorry for the late reply.  The reason why I need the new auto_entrement value is because I am using that as an ProductID of sorts.  When the person / admin enters a new product with a photo, the php application will change the file name to: (productid)_(photonumber).jpg  But if the product has not yet been entered, then I do not know the product ID therefore can not create the correct file name.  I guess I could have the script add the product information and then add the entry and refer to another script which will add the photo with the file name and product ID of the new product if that makes any since.  Just thought it would be better to do it all in one form / script though.

 

Again sorry for the late reply, I didn't subscribe to this thread or say notify me so I kind of forgot about it until I started working on the project again.  That is the problem when you are the only person working on 5 projects at a time.

 

Thanks for the help though.

[Psycho]

Um, you can still do it all in one script without knowing the id ahead of time. Just insert the data into the database and then use mysql_insert_id to get the just inserted record id and change the uploaded image's name.

[djpic]

Htanks mjdamato, that is what I needed.