Can I read an entire table in the postEdit event that updates a row?

TomBajzek

I have an application that needs to write out a flat file of data from a table whenever a row in the table has been edited. The purpose of this app is to manage a groupfile for use with the HTTP AUTH protocol. The datable has a row for each user that defines which group(s) a given user belongs to. A groupfile is constructed from the contents of this table.

The datatable in the app is populated with only the record to be edited, which is loaded by a link provided when a row is selected in a related table, so only the row to be edited is presented to the user of the app. This one-row table has a bubble editor defined for every field, to make a compact, lightweight editor that suits its purpose very well. Editing a row redefines the group(s) the edited user belongs to, at which point the update groupfile needs to be constructed and written.

The place to do this is in a postEdit event handler ( perhaps more correctly in a submitSuccess handler ) on the server. However, I don't see how I can read back the contents of the entire table to construct the new group file at that point. Can someone provide a suggestion for doing that?

Thanks,
Tom

kthorngren

how I can read back the contents of the entire table to construct the new group file at that point.

Use the Datatble API rows().data() to get the table data.

Kevin

TomBajzek

Kevin,

Is there a way to do this server-side? That's where the groupfile that I need to rebuild is located.

Thanks,
Tom

kthorngren

I'm not clear on the process you are trying to complete but to send/receive data from the server you would use jQuery ajax(). Then use the success function to handle anything client side that needs to happen after the ajax request is complete.

Kevin

allan

Just to add some thoughts to this - typically when I've done this kind of thing myself I will use the server-side postEdit (and postCreate) event handlers to kick off a process, or call a function, that will build the file. You'll need to have that function / process read the information from the database though, since the full data set isn't given to the event handler.

The other option is to use the rows().data() method that Kevin suggested to read the data form the client-side table (which you could do with the client-side submitComplete) and then using jQuery.ajax() to submit it to a server which would create the file.

I wouldn't suggest the second approach be put into action though. It would be too easy for someone to make changes on the client-side and then have it write the file without submitting the changes to the server. So you could end up out of sync, or worse in this case, someone adding access rights that you don't know where they have come from.

Allan

TomBajzek

Allan,

I intended to do that server-side, as your first paragraph suggests. What I need to know is how to read back the entire table, post-editing it, to build the flat file. I have prior working code that does knows how to write the groupFile when it has the database table, but I don't know how to get PHP to read back the updated database table.

That's what my question really is.

Tom

allan

Hi Tom,

->on('postEdit', function ($editor) {
  $fullData = $editor->$db()->select('myTable')->fetchAll();
  // ...
});

That will read the entire data set from myTable into $fullData. You can then do what you want with it. This is the API reference docs for the database class (which $editor->db() is accessing).

Allan

TomBajzek

Hi Allan,

This suggestion worked perfectly.

Thanks,
Tom

TomBajzek

Since your helpful answer, I've needed to narrow the selection. Unfortunately, I seem to have done this incorrectly. Using your response above, I made the following line of code that worked correctly:

$groupData = $editor->db()->select('adminGroups')->fetchAll();

Now, I've needed to modify this to select only the records where the field 'active' has the value 'yes'. Therefore, I changed that line of code to be as follows:

$groupData = $editor->db()->select('adminGroups','*',['active','yes'])->fetchAll();

However, when I run the program with the latter line, it fails with a PDO Exception:
PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'where clause', in line 105 of MysqlQuery.php, the protected function _exec().

I believe I've interpreted the documentation (https://editor.datatables.net/docs/2.0.1/php/classes/DataTables-Database.html#method_select) incorrectly, but nothing I've tried here solves the problem. Can you provide some guidance, please?

Thanks,
Tom

TomBajzek

I've figured out this problem. Thanks,
Tom