MySQL: Auto Incrementing [Resolved]

[Electroman]

When using the auto increment ability on my primary key I want to also find out what the next value of it will be. Does anyone know how to do this?

Thanx

[Chroder]

You could use mysql_insert_id() to grab the last insert ID, then add one to it.

[Electroman]

Not sure how that works, if I added the new record letting the auto increment sort out the id then ran mysql_insert_id() would it return the id I just created?

[The Hobo]

Originally posted by Electroman
Not sure how that works, if I added the new record letting the auto increment sort out the id then ran mysql_insert_id() would it return the id I just created?

Yes.

[Electroman]

Thank you.

Have now completed that script, Unique Visitors Counter . I actually worked round it by calculating what it should be, because the number is actually used in the same statement, but I think I'll redo it using this method. Then do an Update statement after it to include it in the record.

[CornedBee]

I think there's also LAST_INSERT_ID and NEXT_INSERT_ID functions that you can use in SQL statements.

[The Hobo]

Originally posted by CornedBee
I think there's also LAST_INSERT_ID and NEXT_INSERT_ID functions that you can use in SQL statements.

Yes, there is. But I always found it easier to just use the PHP function. I guess there's really no difference. I'm sure the PHP function just calls a query for LAST_INSERT_ID.

A long time ago, Matt, phpman, and I had a big debate about how to do this properly. I don't think we ever resolved anything, but there might be something worthwhile in the thread.