Table Settings

WP Data Access > Data Explorer > Manage > Settings > Table Settings

Row count (use estimation to improve performance)
Row level access control (to improve security)
Process hyperlink columns

WP Data Access - Data Explorer Table Settings

Row count #

Tables are using pagination by default. Although pagination improves the user experience, it slows down performance, especially for large tables. Pagination requires counting:

All table rows
All matching table rows

To improve the performance of queries on large tables the pagination features was optimized in version 4.2 and does no longer require to count all table rows on each request:

For MyISAM tables the table row count is taken from the MySQL data dictionary
For InnoDB tables and views (including system views) the row count is taken from the MySQL data dictionary if:
- The estimated table row count exceeds the Max row count, or:
- This is defined for a specific table

A table row count is only performed on InnoDB tables and views if the criteria mentioned above are not met. This improved search strategy was added to all WP Data Access tools.

As a result the row count for InnoDB tables and views might be an estimate instead of the real row count. Whenever a estimate is given, the ~ character is show in front of the table row count (see image above #Rows – click to enlarge). The estimate can be calculated by the plugin or a hard estimate.

As a plugin user you can change this behaviour:

Change the Max row count (> Settings > WP Data Access > Back-end > Max row count)
Change the behaviour of specific tables (see image above Row count – click to enlarge)

Row level access control #

The plugin uses table access control by default. Table access control prevents unauthorized table editing. If a user has no access to a specific table (project), the user will not be allowed to access the page and edit the table.

For most use cases table level access control is sufficient. For projects using user-sensitive data, row-level access control can be enabled. This prevents users editing the same table from overwriting each other’s data. An example is a student profile page or an affiliates page.

Row-level access control can be used to create links to data entry forms (in a dynamic hyperlink for example). For row-level access control an additional authorization step is required. The plugin needs an additional wpnonce to allow users to access the data entry form for a specific table row. The format for the wpnonce is:

wpda-row-level-security-{$table_name}{$keys}

Where

$table_name = table name
$keys = key list and key values which the user is allowed to edit

Example of a $keys construction:

foreach ( $this->wpda_list_columns->get_table_primary_key() as $key ) {
	$keys .= "-{$key}-" . get_key_value( $key );
}

Process hyperlink columns #

To add a custom hyperlink to a table row, a column can be defined as type hyperlink (see Column Settings). Hyperlink columns are stored in JSON or plain TEXT format. This is defined on table level in this section.

If hyperlinks are stored in JSON format, a hyperlink column contains a hyperlink label, url and target. A hyperlink column must be large enough to store the whole JSON string. If hyperlinks are stored in TEXT format, the full URL is entered in plain text.

When a column is set to hyperlink and is stored in JSON format, the column shows a link icon on the data entry (behind the hyperlink column) and allows to enter a label, url and target (see image below – click to enlarge). When stored as plain TEXT it is represented as a normal text field.

Data Explorer – Data Administration (click to enlarge)

Still stuck? How can we help?

Updated on 2023-02-09

4 Comments

SERGET on 2021-11-10 at 12:24 pm

I am wondering that is a very simple question, but my mother, HOW I CAN CHANGE THE COLOR WTHIN THE ROW OR COLUM?????

THIS PLUGIN SHOWS TO ME WHITE TABLE ON A WHITE BACKGROUND AND I CAN DO NOTHING TO IT!!

HOW TO CHANGE THE COLOR OF THE CELL??

WHERE IS IT IN DOCS??????
Reply
- Peter Schulz on 2021-11-11 at 9:10 am
  
  When you are seeing white text on a white background in the back-end, your WordPress back-end styling is overwritten. WP Data Access does NOT overwrite any standard WordPress back-end css! This must be the result of some other plugin or other code fragment. Use the inspector to find out which file is responsible for changing your back-end css an take appropriate action.
  
  Let me know,
  Peter
  Reply
Wilfred Derksen on 2022-01-18 at 2:58 pm

Hi Peter,

still think this is a wonderful project you have delivered! Thank you for that.
Now, I have a stored procedure filling some tables. One of the fields contains a URL of an image on my website. Not an image in teh media library, but uploaded in a woocommerce product to store a passphoto. If I add manually a (longblob) column and then use wpda to manually add a label and copy the url in that field, then it works and do really get a link. But this is a manual workaround. Afterwards I checked the result in mysql workbench but then it only shows “blob” for that field.

So, what I now did manually with wpda, i don’t know how to do that in a mysql statement. Can you explain this to me?
Reply
- Peter Schulz on 2022-01-19 at 8:39 am
  
  Hi Wilfred,
  
  A URL can be stored in a table in two formats: JSON and plain text. This option is defined in “Process hyperlink columns as”. JSON is the default. If set to JSON, the plugin expects a JSON format. When you enter a hyperlink through the data entry form, the plugin will store the hyperlink in JSON format. So if you enter a plain text hyperlink (through sql insert or update for example) the plugin will not be able to process the hyperlink.
  
  To store hyperlinks in plain text format, just set “Process hyperlink columns as” to “plain text”. This allows you to use SQL insert and update or the data entry form. When this options is set to plain text the plugin will store the hyperlink as plain text when you use the data entry form as well.
  
  Hope this helps,
  Peter
  
  PS The JSON format allows you to add a title and target.
  Reply

Submit a Comment Cancel reply