I have a very large table (40,000+) rows, and I am having a lot of problems getting the filtering/search to work. The search box is not working at all. As soon as I type anything at all my table is empty. Filtering works on some fields but does not work on the main field that I need filtered. When I type anything (even just one letter) into the filter box I get no results at all (an empty table), but the bottom of the table says "showing 1 to 25 of 31...". I also know that there are more than 31 rows that should be returned.
I have also tried setting the filtering up with the checkbox and selectbox options and get the same results. I noticed that the "read from table" box in the Data tab is not working at all. When I try to click it nothing happens. (I have tested it on multiple tables including much smaller ones.) But even if I manually type in the values I can not get the filtering to work.
Is my table too large? Or is there something else I should try?
Having a file that big will not work with non-server-side tables, so Excel, Google Spreadsheet, PHP array, XML, CSV cannot be used. The only option would be to use SQL query based tables, or imported tables (manual tables).
If you can't filter through your server-side tables, the issue is probably with the query you're using. Any query that has JOIN, CONCAT, GROUP, UNION, etc. statements will cause issues with filtering, search and sorting.
To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.
Please note some this when working with the server-side processing feature:
Please do not use “LIMIT” in the SELECT statement. wpDataTables adds it automatically and it will be overridden.
Please do not use “ORDER BY” in the SELECT statement. wpDataTables has its own sorting engine so it makes no sense to use MySQL’s sorting, since it will be overridden. Also, server-side processing feature adds this part of statement automatically when users trigger the sorting on the front-end, and having it in initial statement may cause the table to crash.
Thank you for getting back to me. Using the View option did fix the problem.
I now see that I am not able to export the entire document unless I use the View All option. Unfortunately I am not able to use the View All for so many lines. Do you know if there will be an option in the future to export the entire document without using view all?
We did have this request like this before but I cannot say an exact ETA when this might be implemented so please take the time to leave your suggestion following this link so it will go straight to our developers.
I have a very large table (40,000+) rows, and I am having a lot of problems getting the filtering/search to work. The search box is not working at all. As soon as I type anything at all my table is empty. Filtering works on some fields but does not work on the main field that I need filtered. When I type anything (even just one letter) into the filter box I get no results at all (an empty table), but the bottom of the table says "showing 1 to 25 of 31...". I also know that there are more than 31 rows that should be returned.
I have also tried setting the filtering up with the checkbox and selectbox options and get the same results. I noticed that the "read from table" box in the Data tab is not working at all. When I try to click it nothing happens. (I have tested it on multiple tables including much smaller ones.) But even if I manually type in the values I can not get the filtering to work.
Is my table too large? Or is there something else I should try?
TIA
Hi Liz
Thank you for reaching out to us.
Having a file that big will not work with non-server-side tables, so Excel, Google Spreadsheet, PHP array, XML, CSV cannot be used. The only option would be to use SQL query based tables, or imported tables (manual tables).
If you can't filter through your server-side tables, the issue is probably with the query you're using. Any query that has JOIN, CONCAT, GROUP, UNION, etc. statements will cause issues with filtering, search and sorting.
To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.
Please note some this when working with the server-side processing feature:
Hi Blazenka,
Thank you for getting back to me. Using the View option did fix the problem.
I now see that I am not able to export the entire document unless I use the View All option. Unfortunately I am not able to use the View All for so many lines. Do you know if there will be an option in the future to export the entire document without using view all?
Liz
Hi Liz
I'm glad to hear that
We did have this request like this before but I cannot say an exact ETA when this might be implemented so please take the time to leave your suggestion following this link so it will go straight to our developers.
I had the same issue, and went thru all possible documentation for several months.
Finally, found the solution to be editing .../wp-content/plugins/wpdatatables/source/class.wpdatatable.php in row 1566 (or around there).
I removed $table from private function getLikeExpression for vendor MYSQL return statement. Worked like a charm.
This is how it look in mine which is working now:
private function getLikeExpression($vendor, $table, $column, $pattern) {
if ($vendor === Connection::$MYSQL) {
//$search .= '`' . $tableName . '`.`' . $aColumns[$i] . "` LIKE '%" . $columnSearch . "%' ";
return "$column LIKE '$pattern'";
Hope it helps.
Miguel
Hi Miguel
Thank you for sharing this solution! We will save it for future reference, I am sure many of our clients will find it useful
Have a wonderful day!