I have a wpdata table created from MySQL with values joined from another table.
The table displays fine but when I filter the table for any value I get "DataTables warning: table id=table_1 - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1", which doesn't help as there is no error in the console.
When I check the XHR files on network I see this error.
<div id="error"> <p class="wpdberror"> <strong>WordPress database error:</strong> [You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'wp_posts ``.`Job Type` LIKE '%Installation%' ORDER BY `Work Order/Activity` ASC ' at line 1]<br /> <code>SELECT SQL_CALC_FOUND_ROWS CONCAT('<a href="',guid,'">',(SELECT meta_value FROM `wp_postmeta` WHERE post_id = work_orders.ID AND meta_key = 'wo_number'),'/',(SELECT meta_value FROM `wp_postmeta` WHERE post_id = work_orders.ID AND meta_key = 'activity'),'</a>') AS ' Work Order/Activity', CONCAT((SELECT meta_value FROM `wp_postmeta` WHERE post_id = work_orders.ID AND meta_key = 'job_location_community'),' - ',(SELECT meta_value FROM `wp_postmeta` WHERE post_id = work_orders.ID AND meta_key = 'job_location_lot_number')) AS Location, (SELECT meta_value FROM `wp_postmeta` WHERE post_id = work_orders.ID AND meta_key = 'job_type') AS 'Job Type', (SELECT meta_value FROM `wp_postmeta` WHERE post_id = work_orders.ID AND meta_key = 'job_status') AS 'Job Status', (SELECT meta_value FROM `wp_postmeta` WHERE post_id = work_orders.ID AND meta_key = 'stake_holders_subcontractors') AS Subcontractor, (SELECT meta_value FROM `wp_postmeta` WHERE post_id = work_orders.ID AND meta_key = 'stake_holders_builder') AS Builder, CONVERT((SELECT meta_value FROM `wp_postmeta` WHERE post_id = work_orders.ID AND meta_key = 'order_date'),DATE) AS 'Order Date' FROM `wp_posts` AS work_orders WHERE work_orders.post_type = 'work_orders' AND work_orders.post_status = 'publish' AND `` wp_posts ``.`Job Type` LIKE '%Installation%' ORDER BY `Work Order/Activity` ASC LIMIT 10 </code> </p> </div>{"draw":2,"recordsTotal":"11655","recordsFiltered":"7","data":[]}
There could be quite a few things going wrong here, so the best way to go about it would be if you could, please provide me a temporary WP-admin login for your site where this happens, so we could log in and take a look ‘from the inside’ as that’s the most efficient way to see and resolve the issue. We do not interfere with any data or anything else except for the plugin (in case that’s a production version of the site), and of course we do not provide login data to third party. You can write credentials here just check Private Reply so nobody can see them except us.
This is probably happening because wpDataTables server has problems with parsing of the query and building new queries dynamically (rarely happens, but does sometimes). 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.
I have a wpdata table created from MySQL with values joined from another table.
The table displays fine but when I filter the table for any value I get "DataTables warning: table id=table_1 - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1", which doesn't help as there is no error in the console.
When I check the XHR files on network I see this error.
<div id="error"> <p class="wpdberror"> <strong>WordPress database error:</strong> [You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'wp_posts ``.`Job Type` LIKE '%Installation%' ORDER BY `Work Order/Activity` ASC ' at line 1]<br /> <code>SELECT SQL_CALC_FOUND_ROWS CONCAT('<a href="',guid,'">',(SELECT meta_value FROM `wp_postmeta` WHERE post_id = work_orders.ID AND meta_key = 'wo_number'),'/',(SELECT meta_value FROM `wp_postmeta` WHERE post_id = work_orders.ID AND meta_key = 'activity'),'</a>') AS ' Work Order/Activity', CONCAT((SELECT meta_value FROM `wp_postmeta` WHERE post_id = work_orders.ID AND meta_key = 'job_location_community'),' - ',(SELECT meta_value FROM `wp_postmeta` WHERE post_id = work_orders.ID AND meta_key = 'job_location_lot_number')) AS Location, (SELECT meta_value FROM `wp_postmeta` WHERE post_id = work_orders.ID AND meta_key = 'job_type') AS 'Job Type', (SELECT meta_value FROM `wp_postmeta` WHERE post_id = work_orders.ID AND meta_key = 'job_status') AS 'Job Status', (SELECT meta_value FROM `wp_postmeta` WHERE post_id = work_orders.ID AND meta_key = 'stake_holders_subcontractors') AS Subcontractor, (SELECT meta_value FROM `wp_postmeta` WHERE post_id = work_orders.ID AND meta_key = 'stake_holders_builder') AS Builder, CONVERT((SELECT meta_value FROM `wp_postmeta` WHERE post_id = work_orders.ID AND meta_key = 'order_date'),DATE) AS 'Order Date' FROM `wp_posts` AS work_orders WHERE work_orders.post_type = 'work_orders' AND work_orders.post_status = 'publish' AND `` wp_posts ``.`Job Type` LIKE '%Installation%' ORDER BY `Work Order/Activity` ASC LIMIT 10 </code> </p> </div>{"draw":2,"recordsTotal":"11655","recordsFiltered":"7","data":[]}
Please, help me!
Hello Felipe.
Thank you for your purchase.
There could be quite a few things going wrong here, so the best way to go about it would be if you could, please provide me a temporary WP-admin login for your site where this happens, so we could log in and take a look ‘from the inside’ as that’s the most efficient way to see and resolve the issue. We do not interfere with any data or anything else except for the plugin (in case that’s a production version of the site), and of course we do not provide login data to third party. You can write credentials here just check Private Reply so nobody can see them except us.
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
Sorry, about that. It's working now.
Hello again Felipe.
Yeah, I guessed you have a query similar to this.
This is probably happening because wpDataTables server has problems with parsing of the query and building new queries dynamically (rarely happens, but does sometimes). 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:
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