Please wait...

Creating MySQL-based tables with server-side processing

Video versionA video overview of creating MySQL-based wpDataTables with Server-side processing

wpDataTable MySQL server-side processing

When your data set is larger then a couple of thousands of rows it can’t effectively be loaded in the page from Google Spreadsheet, CSV, Excel file, or other sources: each of these methods first read the data from the source, then print out complete table data on your page; so as the row count grows it makes both page generation time on server side and page load and initialisation time on client side slower and slower. And if your host has a certain memory or timeout limit defined  for PHP scripts it can even ‘break’ the page because script would try to allocate more memory then it’s allowed. But no worries, wpDataTables has a solution for that – server-side processing!

Server side processing is available only for MySQL-based tables, it means that all filtering, sorting, pagination, and other data processing routines will be delegated to MySQL engine, and only several rows will be fetched from the server by a background AJAX request at a time.

Please refer to this documentation section for full explanation about the Server-side processing feature.

In this tutorial we will use a data set of 10.000 rows, you can download the SQL dump using this link.

A table without server-side processing with 10.000 rows would load for probably 3-5 minutes (if it would load at all). Let’s see how it performs with server-side processing enabled. Just try to filter or sort the table.

The step-by-step tutorial on how to create this table follows.

MySQL table with server-side processing example

Please note: if the result of MySQL query is really large (hundreds of thousands of rows, or millions), and the MySQL queries and tables are not optimized properly (with indexes and proper data types) tables can still work slow – but wpDataTables aren’t “responsible” for this since the delays are derived from MySQL engine. Optimize the MySQL tables and queries structure and wpDataTables will speed up as well!

This tutorial and this wpDataTable creation method assumes that you have at least some knowledge about SQL and you are able to prepare the data on MySQL side and can fetch it with a query. If you don’t know how to do this please refer to Table Constructor documentation section.

Instructions to create the table

First thing we need to take care of, is to prepare the data on MySQL side. Normally, you would already have tables and the query, but here we will assume that you use the same sample table as in the example on this page.

Here’s a link to download the SQL dump of this table.

Go to your PHPMyAdmin, choose the database that wpDataTables is configured to use, open “Import” tab, go to “Choose file“, open the file that you just downloaded and click “Go” so that PHPMyAdmin would import the table:

You can then click on the “sample_large_table” in tables browser on the left to review if the data loaded correctly:

Now when the data is in there, we need to prepare an SQL query that would fetch this data from MySQL. In this case it’s simple – “SELECT * FROM sample_large_table“. You can try this query out in SQL tab of PHPMyAdmin to see that it returns what we need.

Create a wpDataTable with server-side processing feature enabled

Now when we have the query prepared, we can build a wpDataTable based on this query.

Please open your WordPress admin, and:

  1. Go to wpDataTables -> Add new from Data source.
  2. Provide some name for your new table in the Table title input which will identify it among others.
  3. Choose MySQL query in the Table type selectbox.
  4. Put the SQL query that we prepared in the step 1 in the MySQL Query editor (“SELECT * FROM sample_large_table“).
  5. Click “Save” so that wpDataTables would initialize the table and columns metadata from the results of this query.

(Optional) - Define additional table and columns settings

At this point table is already prepared. But to improve the usability we need to define some additional settings for the table and for its columns. In this case we defined these settings:

  1. Enabled “Limit table layout” and “Word Wrap” checkboxes.
  2. Disabled the “Visible” checkbox for “id” column.
  3. Defined more nice and readable displayed headers for the columns – “Name” instead of “name“, “Order Date” instead of “order_date“, “Product” instead of “product“, “Amount” instead of “amount“, “Total” instead of “total“.
  4. Set the filter type for “Company” as “Select box” and defined the possible options separated with a | sign.
  5. Set the filter type for “Order Date” column as “Date Range“.
  6. Set the filter type for “Product” as “Select box” and defined the possible options separated with a | sign.
  7. Set the filter type for “Amount” as a “Number Range“, set the Width to “10%”
  8. Set the filter type for “Total” as “Number Range“, Column type to “Float“, “Display text before” as “$” so that currency sign would be displayed, and Width to “10%“.

Insert the wpDataTable in WordPress post or Page

When you finished preparing your MySQL-based wpDataTable, you just need to insert it to your post or page.

  1. Create or open a WordPress post or page,
  2. Place the cursor in the position where you would like to insert your table.
  3. Click the “Insert a wpDataTable” button in the MCE editor panel.
  4. Choose the MySQL-based table that you prepared from the list and click OK.

Never miss new features!

Join 2000+ newsletter subscribers

Never miss notifications about new cool features, promotions, giveaways or freebies - subscribe to our newsletter! We send about one mail per month, and do our best to keep our announcements interesting.

We never spam or disclose your address to anyone.