Creating Editable Tables in WordPress where users can see and edit only their own data

Video version

Allowing users to edit only their own dataConfiguring wpDataTables as a CRUD interface allowing users to see and edit only their own rows

Many users have inquired about possibly allowing their front-end users to see and edit only their own data; for example, own orders or inquiries, own elements added to stock, etc. The main idea was that users would be able to have full “CRUD” (Create, Read, Update, Delete) functionalities for their entries, but would not be able to see, edit, or delete entries made by others.

Since wpDataTables 1.6 it is an out-of-the box feature – you simply need to configure a table in a proper way. You can do so from the wpDataTables back-end by creating a manual table (or you can create a MySQL query-based table – whichever you prefer) – This tutorial will lead you through the process.

Let’s configure a ‘dummy’ orders table, where each logged in user would be able to see and edit his own orders, but would not be able to see orders made by others.

First, we need to open WordPress Admin, go to wpDataTables -> Create table, choose the option “Create a table manually”, and click “Next“:

Then, we need to prepare the columns. If, for example, we want the users to be able to enter these fields: order date, product, comment. One more field that we would need to add for such a table would be the user’s ID, since wpDataTables needs to identify which row belongs to which user. Configuring on the next step would be as follows:

  • First column: Name = “Order date“, type = “Date
  • Second column: Name = “Product“, type = “One-line selectbox“, possible values = “Rectangle, Circle, Square
  • Third column: Name = “Comment“, type = “Multi-line string
  • Fourth column: Name = “User ID“, type = “Integer

Then click “Create the table” and choose between standard and Excel-like editor

The table is already back-end editable, but it is not yet front-end editable. To make it editable from the front-end and allow users to edit only their own rows, click the “Editing” tab.

Then, you need to do the following:

  • Enable the “Allow editing” switch to enable front-end editing;
  • Enable the “Users see and edit only their data” switch to enable the features of editing only own rows.
  • Select “userid” in the “User ID Column” dropdown.
  • Open columns settings and uncheck the “Visible on front-end” checkbox for the “User ID” column since we don’t need to see this information in the table.
  • Click Apply.

NOTE: From 2.7 version of our plugin there is a new option

Show all rows in back-end – if this is enabled, users will see all data for this table in admin area. ( This is also working for the Gravity tables, that are created with our Gravity Forms integration for wpDataTables Add-on)

Now, if you insert the table in the post or page, it will look like this:

Add a few rows.

Now if you log out and log in under a different user, you will not see these rows. Alternatively, users will always have access only to their own records.

The only essential difference between the usual editable wpDataTables, and editable wpDataTables where users can edit only their own rows, is the user ID field. If you create a table on MySQL side yourself, just prepare an integer column for storing your user_id.

From version 2.1, if you use some custom roles from other plugins (like it is S2Member or any other), those roles will be available in the same selectbox on the backend page of wpDataTables plugin.

From the 2.5 version of wpDataTables  you can use this checkbox.
By turning this on this checkbox you will show all possible values in edit modal from separate table that is connected with foreign keys when option Users can see and edit own data is enabled.