Page 1 of 1

Adding / updating rows with a multi-row form

Posted: Sun Jan 02, 2005 7:47 am
by Stryks
Hi all,

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.

:)

Posted: Sun Jan 02, 2005 8:32 am
by hawleyjr
If you’re concerned about your primary key getting too high; make sure you are using the best data type. Here is a good reference:

http://kimbriggs.onza.net/Computers/Not ... types.html

As for knowing what was inserted. Check out [php_man]mysql_insert_id[/php_man]

Posted: Sun Jan 02, 2005 5:28 pm
by Stryks
Thanks for taking the time to reply.

My primary key is an int(11) so I would think it has a good chance of lasting a while. If it gets tight, I suppose I can always change it to a bigInt later on. But still, I am wondering if the performance goes down as the numbers go up, though I suspect that it would always be handled with enough memory reserved for the maximum value. WOuld this mean that a bigInt primary key would be slower?

In regards to the mysql_insert_id, I'm not quite sure how to apply that to this situation in a practical way.

I am still not sure if you are saying that it is best to just wipe them all and rewrite the whole thing so long as my primary key has the room, or if I should be looking for a way to find if I need to update or insert the records.

Thanks again