Quick questions from a newb about preferred method...

mihomes

I will soon be developing the website side of a new project where users will login to their account to view large amounts of various data from an sql database. I came across DataTables and it appears the perfect solution with its included sorting, pagination, filtering.

The problem - I need to allow users to delete selected rows from sql. In a perfect world I would like to show a checkbox next to each row as well as a 'select all' and 'unselect all' option for the entire page. If they hit delete then all checked rows are removed not only from display on the page, but deleted from the sql database as well. I did some brief reading here on the forums and everything I came across involved lots of problems trying to implement this.

I would prefer to use server-side processing for the data-tables, but the above doesn't seem possible without tons of workarounds. Is it possible when NOT using server-side processing in the sense that I could load the table into the dom from sql first? I would imagine this would be pretty slow to load as the current setup uses this method (minus any datatable functionality) and page load times are pretty slow.

Can someone with experience using datatables give me some suggestions, pros, and cons, on how to go about this? If worst comes to worst then I will just use server-side without the delete options, but that functionality is really desired.

Thanks in advance for any help!

tangerine

I'm using PHP/MySQL for all data requirements, with Datatables for presentation. For admin purposes, I have some tables with an added checkbox or input field per row. The (data) id's of ticked checkboxes or completed input fields are passed ($_POSTed) back to PHP for action. It seems pretty straightforward once you get used to it.
I would say that what you want is perfectly possible, with the usual caveats about volume and of course letting real users loose on the data ;-)

mihomes

Thanks for the input... that makes me feel a lot better. I have yet to use Datatables, but everything I found from a search made it seem impossible.

Just to be clear... you had no issues assigning ID's from the server to the table so they corresponded. Did you just do some sort of ajax call to pass the fields to a php script to make changes on the server end of things then (in my case the actual deletion)? I know there is a delete call for the visual datatable to remove rows so I was hoping I could wrap a php call to that to physically remove it from the sql as well.

tangerine

Thinking this through - mine is a one man operation, so I need to question my judgment - I have stayed in my comfort zone with PHP as I am not so comfortable in Javascript.
If you are more skilful than me in JS you might find Datatables' own "Editor" plug-in provides a neater solution. I believe it would do everything you need.
In my own case:
A. My data is only for looking at, as far as end-users are concerned. Thus I have far fewer "What if somebody does such-and-such?" to think about.
B. I have a (sort-of) MVC architecture in PHP, whereby a Controller asks a Model for the data. (Every data record has an id, which Datatables will hide if necessary.) No ajax is involved. Having retrieved the data, the Controller may then add extra array elements (e.g. checkbox) if required. The Controller calls a bunch of setters in a "Datatables Utilities" class, which builds an array of options for DT initialisation. That array is then json-encode()d for insertion into the JS (see next sentence). The utilities class also has to build the Javascript for insertion into the page. The Controller also defines table attributes and column headings.
C. Everything created in B. is handed off to a "build table" routine which uses PEAR Html-Table to build the table, then stuffs it into the relevant view/template, adds the script, and returns it for display. I only started using PEAR's table package recently. and found it easier than I expected.

Taking a step back, that now looks as if it might be unnecessarily complicated when compared with Datatables' Editor; but as I say, I stuck to my comfort zone!

Regarding your deletions, by ticking the relevant checkboxes and clicking a submit button the relevant ids may be returned to PHP for action. In this case, your table would need to be enclosed in tags.