I'm displaying rows of data in a table where the user can edit values that already exist in the database, or add new items to the database. New records are added by simply clicking on a checkbox at the beginning of the row.
To clarify this, the form presents a series of options which are allowable from a specified category, so lets say I have a bike and I choose wheels as my category, I am shown a table with five rows showing the five options I can have. I am allowed to have more than one part, so any items I check the box on are inserted into the database.
The next time I come back to the form, the script finds records for the tyre category and so displays the entire category list again with the options I checked automatically checked and the others unchecked. In this way I can adjust my settings without the need to re-select the category.
I can then choose another category and it is displayed above the other now saved category, so we have two categories showing, one ready to be marked, and one as was saved. This process repeats for as many categories as is needed by the subject of the form.
Given this format, I have simply taken to removing all entries from the database where the user id matches the current user, and then inserting all the data, edited old entries and newly checked entries, all at once using an insert query in a loop of all checked entries.
So, my question is, is this the best way to handle this task?
I mean, my primary keys column is going to start going up at an alarming rate I would imagine, and as far as I am aware, they don't recycle at any point, so I suppose there may come a time I hit an upper limit. As for the effect of this on performance ... who knows.
The alternative would appear to be either running a query for each row to determine the status and then run either an insert or update query as required, or inserting a value into a hidden field on each row to hold the load-time status of the field and either updating or inserting based on that. Either one complicates matters somewhat, though not so much that it wouldn't be worth doing.
Any advice or comments about the best approach would be appreciated.
Thanks for taking the time to read.