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.
|1||Yoshio W. Roach||Cakewalk||30/10/2014||Product Five||10||136.00|
|2||Sawyer M. Good||Cakewalk||26/11/2012||Product Seven||1||1,806.00|
|3||Flynn M. Bryant||Sibelius||23/09/2013||Product Four||4||154.00|
|4||Stephanie Q. Robles||Adobe||25/03/2014||Product Three||1||1,576.00|
|5||Lacey T. Rocha||Borland||10/03/2013||Product Five||7||1,990.00|
|6||Garrison J. Foreman||Adobe||04/04/2014||Product Three||3||810.00|
|7||Maisie M. Park||Macromedia||21/02/2014||Product Five||10||1,202.00|
|8||Salvador Y. Jennings||Lycos||01/05/2014||Product Two||1||834.00|
|9||Valentine D. Knight||Cakewalk||18/12/2012||Product Six||3||1,028.00|
|10||Abdul L. Brewer||Borland||17/01/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:
1. Go to wpDataTables -> Create a Table and choose Create a table linked to an existing data source option.
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.
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%“.
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.
- Full information on Server-side processing
- Creating editable tables manually
- MySQL-based tables basics
- Making MySQL tables editable
- Creating non-editable tables based on Excel files
- Creating non-editable tables based on Google Spreadsheeets
- Creating editable tables by importing Excel, CSV of Google Spreadsheet data