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
|1||Yoshio W. Roach||Cakewalk||30 Oct 2014||Product Five||10||136,00|
|2||Sawyer M. Good||Cakewalk||26 Nov 2012||Product Seven||1||1.806,00|
|3||Flynn M. Bryant||Sibelius||23 Sep 2013||Product Four||4||154,00|
|4||Stephanie Q. Robles||Adobe||25 Mar 2014||Product Three||1||1.576,00|
|5||Lacey T. Rocha||Borland||10 Mar 2013||Product Five||7||1.990,00|
|6||Garrison J. Foreman||Adobe||04 Apr 2014||Product Three||3||810,00|
|7||Maisie M. Park||Macromedia||21 Feb 2014||Product Five||10||1.202,00|
|8||Salvador Y. Jennings||Lycos||01 May 2014||Product Two||1||834,00|
|9||Valentine D. Knight||Cakewalk||18 Dec 2012||Product Six||3||1.028,00|
|10||Abdul L. Brewer||Borland||17 Jan 2014||Product Six||3||1.100,00|
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.
Now when we have the query prepared, we can build a wpDataTable based on this query.
Please open your WordPress admin, and:
- Go to wpDataTables -> Add new from Data source.
- Provide some name for your new table in the Table title input which will identify it among others.
- Choose MySQL query in the Table type selectbox.
- Put the SQL query that we prepared in the step 1 in the MySQL Query editor (“SELECT * FROM sample_large_table“).
- Click “Save” so that wpDataTables would initialize the table and columns metadata from the results of this query.
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:
- Enabled “Limit table layout” and “Word Wrap” checkboxes.
- Disabled the “Visible” checkbox for “id” column.
- 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“.
- Set the filter type for “Company” as “Select box” and defined the possible options separated with a | sign.
- Set the filter type for “Order Date” column as “Date Range“.
- Set the filter type for “Product” as “Select box” and defined the possible options separated with a | sign.
- Set the filter type for “Amount” as a “Number Range“, set the Width to “10%”
- 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%“.
When you finished preparing your MySQL-based wpDataTable, you just need to insert it to your post or page.
- Create or open a WordPress post or page,
- Place the cursor in the position where you would like to insert your table.
- Click the “Insert a wpDataTable” button in the MCE editor panel.
- Choose the MySQL-based table that you prepared from the list and click OK.
- Full information on Server-side processing
- Creating editable tables manually using Table Constructor
- MySQL-based tables basics
- Making MySQL tables editable
- Creating non-editable tables based on Excel files (Add from Data Source)
- Creating non-editable tables based on Google Spreadsheeets (Add from Data Source)
- Creating editable tables by importing Excel, CSV of Google Spreadsheet data (Table Constructor)