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

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

TomBajzekTomBajzek Posts: 163Questions: 36Answers: 1

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

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    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

  • TomBajzekTomBajzek Posts: 163Questions: 36Answers: 1

    Kevin,

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

    Thanks,
    Tom

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    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

  • TomBajzekTomBajzek Posts: 163Questions: 36Answers: 1

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin
    Answer ✓

    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

  • TomBajzekTomBajzek Posts: 163Questions: 36Answers: 1

    Hi Allan,

    This suggestion worked perfectly.

    Thanks,
    Tom

  • TomBajzekTomBajzek Posts: 163Questions: 36Answers: 1

    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

  • TomBajzekTomBajzek Posts: 163Questions: 36Answers: 1

    I've figured out this problem. Thanks,
    Tom

This discussion has been closed.