I've just set up my first table, linked to a MySQL table I have made. I have used the most left option: "Create a table linked to an existing data source."
I fetch the data with select * from 'table'.
This works fine, however, when applying any kind of filter (exact filtering off) it always results in "No matching records found".
This is not expected behavior. How should I proceed? We can be confident that wpDataTables has access to my MySQL table. Sorting works as well, but for some reason all types of filtering fails.
I've tried (1) making a view of my table (2) use your own data set 'dummy employees'.
View did not help, using 'dummy employees' I discovered the filters work.
I suspect the problem arises because of data formatting in SQL, is wpDataTables not compatible with 'text' type format, what about bigint(20) and double ?
When the table is being created manually through wpDataTables' the Integer columns have a range from - 2.147.483.647 to 2.147.483.647 and it is being saved in PHPMyAdmin as INT.
If You were to create a table in PHPMyAdmin and assign the column types Yourself, then wpDataTables can use bigint and double. Please take a look at the screenshots I have attached. You'll see one of them has 2.147.483.647 when I tried to enter a much bigger number (that table was created manually), but when I created the table in SQL, it passed the bigger number without any issues
I believe You can always open the manually created table in PHPMyAdmin and change the type from INT to BIGINT and the issue should be resolved.
Also, TEXT is supported by wpDataTables, but again - if You created a table manually in our plugin and assigned the column editor type to be One-Line edit, it'll set the type to VARCHAR in PHPMyAdmin, and if You want to change it to Multi-Line, You'd need to change the type to TEXT in PHPMyAdmin for the table to be able to accept text longer than 255 characters.
When both with and without a 'view', the filtering always fails when server-side processing is enabled. See screenshot, notice also that total row count is incorrect (10 instead of 2). The same occurs when using a view (create view `test` as select * from `TABLE 55`). Server-side disabled does work, however I have a big sql table in mind for my application so this is undesirable.
I'm glad You found the issue. Thank You for letting me know.
You are right, in our documentation under "Create the wpDataTable and paste the query" the example is without the ' ' but we didn't note that anywhere. Thank You for pointing it out to us. I'll forward this to our development team, so they can work on the issue for one of our future updates.
When you are posting on tickets that are not yours or have questions or issues which are not related to the title of the active ticket can you please open a new one, and we will help you there. In that way, issues and questions which are related to different subjects will be in different tickets so other customers or our support agents can find it easily. Our policy is to have one issue or question per ticket because of the reasons that is described already.
I've just set up my first table, linked to a MySQL table I have made. I have used the most left option: "Create a table linked to an existing data source."
I fetch the data with select * from 'table'.
This works fine, however, when applying any kind of filter (exact filtering off) it always results in "No matching records found".
This is not expected behavior. How should I proceed? We can be confident that wpDataTables has access to my MySQL table. Sorting works as well, but for some reason all types of filtering fails.
I've tried (1) making a view of my table (2) use your own data set 'dummy employees'.
View did not help, using 'dummy employees' I discovered the filters work.
I suspect the problem arises because of data formatting in SQL, is wpDataTables not compatible with 'text' type format, what about bigint(20) and double ?
Hello Willem.
Thank You for Your purchase.
When the table is being created manually through wpDataTables' the Integer columns have a range from - 2.147.483.647 to 2.147.483.647 and it is being saved in PHPMyAdmin as INT.
If You were to create a table in PHPMyAdmin and assign the column types Yourself, then wpDataTables can use bigint and double. Please take a look at the screenshots I have attached. You'll see one of them has 2.147.483.647 when I tried to enter a much bigger number (that table was created manually), but when I created the table in SQL, it passed the bigger number without any issues
I believe You can always open the manually created table in PHPMyAdmin and change the type from INT to BIGINT and the issue should be resolved.
Also, TEXT is supported by wpDataTables, but again - if You created a table manually in our plugin and assigned the column editor type to be One-Line edit, it'll set the type to VARCHAR in PHPMyAdmin, and if You want to change it to Multi-Line, You'd need to change the type to TEXT in PHPMyAdmin for the table to be able to accept text longer than 255 characters.
Please let me know if this helps.
Best regards.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
Dear Aleksandar,
Thanks for your answer. I think the focus has shifted a little;
This is my table:
CREATE TABLE IF NOT EXISTS `TABLE 55` (`date` varchar(9), `ffyear` int(4), `p` int(5), `s` int(2), `e` int(1), `r` decimal(6,5), `d` varchar(10), `m` decimal(6,4), `p1` decimal(3,1), `c` decimal(8,7)) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO `TABLE 55` (`date`, `ffyear`, `p`, `s`, `e`, `r`, `d`, `m`, `p1`, `c`) VALUES ('7/31/1992', 1992, 10001, 11, 3, 0.06383, '', 13.4375, 12.5, 1.06383), ('8/31/1992', 1992, 10001, 11, 3, 0.04, '', 13.975, 13, 1.1063832);
When both with and without a 'view', the filtering always fails when server-side processing is enabled. See screenshot, notice also that total row count is incorrect (10 instead of 2). The same occurs when using a view (create view `test` as select * from `TABLE 55`). Server-side disabled does work, however I have a big sql table in mind for my application so this is undesirable.
What do you make out of this?
Willem
Alright,
After endless trial and error I have found the cause of this issue myself:
SELECT * FROM TABLE_57 works
SELECT * FROM `TABLE_57` does not work
I'm no expert at MySQL, but this seems like an issue you guys need to address in a coming update.
Hello Willem.
I'm glad You found the issue. Thank You for letting me know.
You are right, in our documentation under "Create the wpDataTable and paste the query" the example is without the ' ' but we didn't note that anywhere. Thank You for pointing it out to us. I'll forward this to our development team, so they can work on the issue for one of our future updates.
Best regards.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables
Having similar issues. with SQL server side processing filters return no records. W/o server side processing -- no problem
Hello alphaarchitect
When you are posting on tickets that are not yours or have questions or issues which are not related to the title of the active ticket can you please open a new one, and we will help you there. In that way, issues and questions which are related to different subjects will be in different tickets so other customers or our support agents can find it easily. Our policy is to have one issue or question per ticket because of the reasons that is described already.
Thank you for understanding.
Best regards.
Kind Regards,
Aleksandar Vuković
[email protected]
Rate my support
wpDataTables: FAQ | Facebook | Twitter | Instagram | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Instagram | Amelia demo sites | Docs | Discord Community
You can try wpDataTables add-ons before purchasing on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables