Welcome to WebmasterWorld Guest from 18.104.22.168 , register , free tools , login , search , pro membership , help , library , announcements , recent posts , open posts Become a Pro Member
Auto increment two fields simultaneously? bkeep msg:4343061 3:07 pm on Jul 23, 2011 (gmt 0) In my db table I have these fields id,sort_order,info,etc The id field is setup as an autoincrement primary key the sort_order field is setup as just an int(5) id sort_order info 1 1 test 2 2 test 3 4 test 4 3 etc When a new item is added is there a way to autoincrement the sort_order field at the same time as the id field or should I just use two queries and set the sort_order with last insert id? Best Regards, Brandon
Pico_Train msg:4343204 8:03 am on Jul 24, 2011 (gmt 0)
Yeah, that's what I was going to say, get the last inserted ID and then update the sort order and set it the same as the id. Readie msg:4344247 9:17 pm on Jul 26, 2011 (gmt 0)
Have you tried saying: INSERT INTO table_name SET a = "val a", b = "val b", sort_order = id Think that should work. penders msg:4346673 1:54 pm on Aug 2, 2011 (gmt 0)
Just to clarify... 12.1.10. CREATE TABLE Syntax [ dev.mysql.com]
There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. : For MyISAM and BDB tables, you can specify an AUTO_INCREMENT secondary column in a multiple-column key. See Section 3.6.9, Using AUTO_INCREMENT. But having an "AUTO_INCREMENT secondary column in a multiple-column key" is not what is required in this instance. penders msg:4346721 4:05 pm on Aug 2, 2011 (gmt 0)
...or should I just use two queries and set the sort_order with last insert id? Just wondering... it kind of feels you should be able to do something like this in 1 query?