maintaining MySQL tables: best way to go?

jerry1970

Hi all,

I am trying to find a way that is quick to implement table maintenance. I have several tables to maintain, the largest will be about 2000 records (Artists) and just a couple of fields; others will have linked data (showing the Albums for a selected Artist; about 100 items per Artist max). I want to let my team members be able to edit the data, using jQuery dialogs. I want to use DataTables for the tables, so people can sort and filter easily.

People will be changing data, adding rows, deleting rows. Seems I have to choose between ajax or fixed tables. With ajax, I guess there will be a php file returning JSONs but after updating the table (edit, add, or delete), I have to let DataTables re-load the whole table? With fixed data, I can change, remove, or add a single row, but it takes more code to do that, right?

I am a PHP/javascript programmer, and have some years of jQuery experience, but not a lot of DataTables experience (yet).

What would be the best way to go here? Any advise is very welcome.

Thanks!
Jerry

allan

There are three ways (probably more, but three off the top of my head!) of doing this:

1. Server-side processing - probably the easiest to implement, since all the data is stored at the server. If you want to update the table, you just call fnDraw and it will get the latest information. So you would call fnDraw after each edit, delete, insert. Downside? Each draw is an XHR, and you would need another XHR to do the add/edit/delete action (so 2 XHR calls per change). You would also need a server-side processing script - although there are plenty of examples of those on the development section of this site.

2. Client-side full Ajax refresh - Load data by Ajax, and then after each update clear the table and reload it fully. I'd say option 1 is preferably to this, unless you think that the editing aspect of the table will rarely be used, and it will mainly be about filtering, sorting etc.

3. Client-side processing - you need to send your XHR to the server still to tell it to update the database, but rather than loading new data from the server, you would use the DataTables API to add/edit/delete a row - thus saving an XHR call. Overall faster, but you will have to make absolutely sure that the client-side and server-side are in sync. If the server needs to do processing on the data, or formatting or default values, then would you need to implement that on the client side as well.

Allan

jerry1970

Allan, thanks for the quick reply! And a good reply - it lists the pros and cons for each option. Thinking of option 1 and 2 now...

The downside to full server side processing is it needs calls to the server for sorting and filtering and changing page length as well.

There will be a lot of editing going on (mostly adding) but also sorting and filtering to find the Artist that needs editing. What I think would be used most of the times is to find an artist quickly, then click a link to show the albums for an artist and add an album to that list of albums (which is relatively short since it's only a list of albums for the selected artist).

Which option would you use in this case?

Thanks!!

allan

Either would work just fine I think. If you go with option 2 use the fnReloadAjax API plug-in to reload the data. And yes, option 2 would mean hitting the server less, which is good :-)

Allan

jerry1970

Thanks again, you have been very helpful!