Creating editable tables in WordPress using wpDataTables

Video versionA video overview of editable wpDataTable tables

Setup an editable table or how to edit tables in WordPress

Editing settings in WordPress table

There are 2 primary ways to create editable wpDataTables:

  • Create the  data tables manually (see details here) or import the table data from Excel or CSV (see details here)
  • Create a MySQL-based wpDataTable and configure it as editable.

Tables created manually with Table Constructor are back-end editable by default; to allow front-end editing, simply open the “Editing” tab in the table configuration settings, click the  “Allow editing” option, and click the “Save Changes” button (the ID column and the table name will be pre-configured for this type of tables by default).

Your front-end users will then be able to edit the table data.

To create MySQL-based editable tables, which you previously prepared in your MySQL DB server, the algorithm is a little bit more complicated.

First, you will need to create a table on the MySQL side and a MySQL-query based wpDataTable; the steps for this are described in this tutorial.

Once the table is created, go to the “Editing” tab in table configuration settings and click the “Allow editing” button like from the example above.
The wpDataTables plugin will then try to guess the  “MySQL table name for editing” and the “ID column for editing”  (if the column name is “id” or “ID” or similar, it will usually guess correctly).

In some cases, it is necessary to provide the name for “MySQL table name for editing” and the “ID column for editing” for two reasons: First of all, it is not always possible to parse it from the query; and second – there are situations when you would like to send the changes to a different table rather from than the one used in the query. After you define these settings don’t forget to press the “Save Changes” button.

The best practice for ID columns is to set them up on the MySQL side as auto increment integers.

If you use a column with non-unique values as an ID column, it might happen that when you save the settings on the front-end, not only one column, but multiple columns will be edited. It therefore makes sense to make the ID columns invisible (untick the “Visible” checkbox for this column) so as not to confuse the users, since IDs are usually not human-readable.

Editor roles in wpdatatables

Editor roles – Option form “Editing tab” where you  can set only specific user roles to be able to edit the table, choose in this dropdown. If you use some custom roles from other plugins (like it is S2Member, User Role Editor or any other), those roles will be available here.  Leave unchecked to allow editing for everyone.

Edit buttons to be displayed on the front-end – Option on “Editing tab” in table settings where you can set want to include only certain editing buttons on the front-end, select them from this dropdown. Leave unchecked to show all buttons.

Edit buttons to be displayed on the front-end
Duplicate option in Editing settings

Show duplicate button – New option from version 4.1 where you can include “Duplicate button” in existing edit buttons. This option is turned off by default.

On left picture you can see Duplicate button in “Edit buttons group”, and on right you can see Duplicate modal (that will be shown after click on Duplicate button) with same data from selected row. As you can see, it contains new control button “Apply and duplicate“. You can change only some of entries and when you click it, it will be insert new entry with that data and those save data will be shown in modal after save. Like this you can insert a lot of similar data in table, modifying only some fields if other fields have to be the same.

Duplicate edit button in WordPress tablele
Duplicate modal in WordPress table

For manual tables only, if you need to have Duplicate button only on back-end, then you will turn on options “Allow editing” and“Show duplicate button” on Editing tab in table settings and save table settings by clicking on” Save Changes” button. After that you will turn off option “Allow editing” and again click on “Save Changes” button. Like this manual table will have Duplicate button as well only in back-end area.

Frontend editing

Editable tables with buttons

When the table is saved and inserted in the post or page, the look of the table in the front-end is slightly different from that of a typical non-editable table. You’ll see three new buttons in the TableTools section of the table (or just these three if you didn’t enable TableTools for this wpDataTable or four if you use Duplicate Edit button).

Manual tables will be always editable on the table configuration page, and you will always see these three buttons independently from the “Allow editing” feature.

It is also possible to select a row in the table by clicking on it. When you do so, the row will become highlighted (you can change the highlight color along with other table colors on the table settings page).

To make editing possible, you need to choose editor input types for the columns we want to allow users to edit. For this, you need to open the column configuration panel by clicking on “Column settings” button wpDataTables column settings or by clicking the “Column list”  button above the table which enables you to open the column configuration for each column you want to make editable. Then, you need to make a selection in the “Editor input type” dropdown under the “Editing” tab. The possible input types are fully explained in this section.

When the editor input types are chosen and saved, you can return to the table front-end (open the post or page where you inserted the table), choose any row, and click “Edit“; or click “New” to create a new entry. You will see an editor popup. The popup is responsive, so it will work correctly on mobile and tablet devices as well.

  • On the editor popup, you will see the names of the columns and the editor inputs for the cells.
  • Above those columns, you will see navigation buttons:
    • < Prev – selects the previous row of the table, unless the first one is selected. If necessary, it switches to the previous page and puts the data from this row in the front-end editor. Changes that were not saved will be discarded.
    • Next > – selects the next row of the table, unless the last one is selected. If necessary, it switches the table to the next page and puts the data from this row to the front-end editor inputs. Changes that weren’t saved will be discarded.
  • On the bottom side, you will see the control and navigation buttons. Cancel – discards all changes and closes the editor popup.
    • Apply and add new – saves the data from the editor in the current row, it doesn’t close the popup but opens a new editor with the blank input fields. If in option “Edit buttons to be displayed on the front-end” is set only Edit option, then this button will be removed from edit modal.
    • Submit – saves the data from the editor in the current row, and closes the editor popup.
    • Right top “X” button is equal to “Cancel” – this discards all unsaved changes and closes the popup

When you click “Apply and add new” or “Submit“, data is first validated and then sent to the MySQL engine. If the data is invalid, or MySQL returns an error, you will see an error message which tells you which field is problematic and what you need to change.

While the data saving is in progress, you will see an overlay indicating that the process is going on in the background. If the data is sent correctly, you will see a success message.

Please read the next documentation section to see full details on how to configure columns in the editable table correctly.

Edit modal in WordPress table
Edit modal for old skins

Please note that the edit modal from above is for new table skins like “Aqua“, “Purple“, and  “Dark“. For all other skins, the edit modal will look like this (image on the left side)

Limitation

Please note these limitations on editing tables with wpDataTables.

  • Only one MySQL table can be edited at a time. Queries from multiple tables with joins cannot be used as an editable feature, since SQL UPDATE and INSERT statements are generated automatically, and there currently is no way to update multiple tables.
  • Only MySQL tables or tables created with “Create a table manually” and “Create a table by importing data from data source” options of “Create a Table” page can be edited.
  • Server-side processing MUST be turned on for front-end editing – Note: It will turn on automatically if you forget to enable it.
  • MySQL Views cannot be edited properly – only one of the underlying tables can be edited if you pass the proper ID.