I have a CMS that I’m developing and on one table in my database I have a ‘position’ column. Right now when the user adds a new item I query the DB get the number of rows and add ‘1’ to get the next position. Easy enough.
When the user edits an item I get the number of rows and display them as options in a dropdown menu. This allows the user to reorder the how the items will appear on the page. However, there is nothing stopping the user from assigning any given position to multiple items. How can I prevent this? Is there something I could do when the edited item is resubmitted to the DB and if the selected position is occupied by a different item then add ‘1’ to all the positions following it?
I don’t know what the best way to do this is. Any suggestions or tips would be appreciated. By the way I’m pretty new at PHP. If you could explain any suggestion thoroughly I would definitely appreciate it ?