mysql Q
- Started
- Last post
- 7 Responses
- jpea
so, say i have a table that has a bunch of entries each with a unique ID (not a sequential id or anything, just an assigned one). what i want is to figure out what the largest entry ID is so when a new entry is made via php, it just assigns the new entry and ID that is +1 to the current largest entry
i couldn't really figure out how to phrase it for google, so i thought i'd try you folks
- jpea0
nevermind, got it
SELECT MAX(Fieldname)
- ********0
Why do you not have it sequential?
You’re executing an unnecessary query to get the next ... sequential ... id.
Always have auto-increment on if you are going to have an Unique ID column.
If you don’t like the starting number (0 or 1 or whatever) you can always set it to something higher.
I usually bust ones to 1000, or 100000, depending on the data I am going to be holding in the table.
- jpea0
hmm, good point. i'm pretty much a noob at best practices in php/mysql so anything like this helps.
- acescence0
another handy thing is call mysql_insert_id() after an insert and it will return the last inserted id
- ********0
I do mostly MSSQL DB Work, but can you also use:
SET @VariableID = @@IDENTITY
After declaring the ID, as opposed to mysql_insert_id()?
That way if you need to make multiple inserts, however, still need an ID you can use it anywhere afterwards in the script?
- acescence0
yes, i believe you can, though i've never used it before. i'm usually using mysql in the context of a php application, so i'd probably be looping through the queries and i'd just put the ids in an array individually or something
- neverblink0
if you have MySQL 4.1+ then you can also do this with a subquery
INSERT INTO 'table' ('id', 'content') VALUES (SELECT MAX(table.id)+1, 'foobar')