I have a table which gets entries from a website, and as those entries go into the database, they need to be assigned the next category on a list of categories that may be changed at any time.
Because of this reason I can't do something simple like for mapping the first category of 5 to IDs 1, 6, 11, 16.
I've considered reading in the list of currently possibly categories, and checking the value of the last one inserted, and then giving the new record the next category, but I imagine if two requests come in at the same moment, I could potentially assign them both the same category rather then in sequence.
So, my current round of thinking is the following:
lock the tables ( categories and records )
insert the newest row into records
get the newest row's ID
select the row previous to the insertl ( by using order by auto_inc_name desc 0, 1 )
take the previous row's category, and grab the next one from the cat list
update the new inserted row
unlock the table
I'm not 100% sure this will work right, and there's possibly a much easier way to do it, so I'm asking:
A. Will this work as I described in the original problem?
B. Do you have a better/easier way to do this?
Thanks ~
