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

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.

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.

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!

Instructions to create the table

Displaying large MySQL tables in WordPress with wpDataTables

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.

Displaying large MySQL tables in WordPress with wpDataTables

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:

Create a table linked to an existing data source

1. Go to wpDataTables -> Create a Table and choose Create a table linked to an existing data source option.

Creating MySQL-based tables with server-side processing

2. Provide some name for your new table in the wpDataTable name input which will identify it among others.
3. Choose MySQL query in the Input data source 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. Make sure that Server-side processing checkbox is enabled.
6. Click Apply 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 width 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”.
5. Set the Filter type for “Order Date” column as “Date Range“.
6. Set the Filter type for “Product” as “Select box“.
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

Inserting wpDataTables shortcode on page or post

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.