So, I have several questions regarding the plugin, which BTW is a great tool!  I really appreciate what you all have done with the product.  So here we go:

1.  The select box does not work on any of the tables on my website.  It works on the backend, but not when deployed on the front-end.  The check box option works, but not the drop-down.  Is this a version compatibility issue with WP?

2.  Is there any roadmap to allow SET and/or CALL, Prepare and Execute statements within MYSQL?  

3.  I created a PHP script to handle building a dynamic PIVOT table query within MySQL.  I then call that within wpDataTables using a dynamic select statement with VAR1 defining the table name.  It works great, except the column names don't refresh on the front-end when calling the new table.  I am guessing this is how your tool is built.  It assumes the table structure does not change.  Is there anyway to force a full table refresh on the front-end, so it loads the new columns?  If so, can I also dynamically set table settings through a PHP call or some sort of shortcode?

Thank you for your help in advance and thank you for this tool.  It has been a wonderful time saver for our company.

Jeffrey - COO Innersource

Dear Milan,

Thank you for responding.  The SQL works fine in MySQLWorkbench.  Here is the version I created that does the CREATE VIEW as you suggested.  I also tried a version directly in wpDataTables that was a Select statement:

CREATE TABLE version:

SET @@group_concat_max_len = 9999999;

                                                                SET @sql_dynamic = (SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(CASE WHEN dateCounter = ', "'", dateCounter , "'", ' THEN counterValue END)  AS ', "'" , dateCounter, "'" )) FROM edenener_med1110.enmed_Registration_RoomCountTable LEFT JOIN edenener_med1110.enmed_Registration_MasterTable ON edenener_med1110.enmed_Registration_MasterTable.regID = edenener_med1110.enmed_Registration_RoomCountTable.regID WHERE edenener_med1110.enmed_Registration_MasterTable.formName = "UKYear2,Class5RegistrationForm");

                                                                SET @sql = CONCAT('CREATE VIEW `UKYear2,Class5RegistrationForm` AS SELECT edenener_med1110.enmed_Registration_MasterTable.formName, edenener_med1110.enmed_Registration_MasterTable.attendeeType, edenener_med1110.enmed_Registration_MasterTable.programType, ', @sql_dynamic, ' FROM edenener_med1110.enmed_Registration_RoomCountTable LEFT JOIN edenener_med1110.enmed_Registration_MasterTable ON edenener_med1110.enmed_Registration_MasterTable.regID = edenener_med1110.enmed_Registration_RoomCountTable.regID WHERE edenener_med1110.enmed_Registration_MasterTable.formName = "UKYear2,Class5RegistrationForm" AND edenener_med1110.enmed_Registration_MasterTable.attendeeType != "MIA-Student" GROUP BY formName, attendeetype, programType ORDER BY attendeetype, programType');

                                                                PREPARE stmt FROM @sql;

                                                                EXECUTE stmt;

                                                                DEALLOCATE PREPARE stmt;

SELECT version: 

SET @@group_concat_max_len = 9999999;

SET @sql_dynamic = (SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(CASE WHEN dateCounter = ', "'", dateCounter , "'", ' THEN counterValue END)  AS ', "'" , dateCounter, "'" )) FROM edenener_med1110.enmed_Registration_RoomCountTable LEFT JOIN edenener_med1110.enmed_Registration_MasterTable ON edenener_med1110.enmed_Registration_MasterTable.regID = edenener_med1110.enmed_Registration_RoomCountTable.regID WHERE edenener_med1110.enmed_Registration_MasterTable.formName = "UKYear2,Class5RegistrationForm");

SET @sql = CONCAT('SELECT edenener_med1110.enmed_Registration_MasterTable.formName, edenener_med1110.enmed_Registration_MasterTable.attendeeType, edenener_med1110.enmed_Registration_MasterTable.programType, ', @sql_dynamic, ' FROM edenener_med1110.enmed_Registration_RoomCountTable LEFT JOIN edenener_med1110.enmed_Registration_MasterTable ON edenener_med1110.enmed_Registration_MasterTable.regID = edenener_med1110.enmed_Registration_RoomCountTable.regID WHERE edenener_med1110.enmed_Registration_MasterTable.formName = "UKYear2,Class5RegistrationForm" AND edenener_med1110.enmed_Registration_MasterTable.attendeeType != "MIA-Student" GROUP BY formName, attendeetype, programType ORDER BY attendeetype, programType');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

When I attempted to put this code (with the SELECT version) in wpDataTables I get the error “cannot calculate position” or “set not implemented”, depending on whether or not I include the semicolons.

So, the other issue I had is that when I use a Select from a View Table and if the tables Column headers differ, which it will in my dynamic report.  On the front end of wpDataTables it retains the column headers of the old table and doesn’t pull in the new data.  If I go to the backend and click apply again, it updates the table with the new column headers.

I will work to pull you a screen shot later today.

Warm regards,

Jeffrey

Dear Milan,

Thank you and that is what I was assuming.  Is there anyway to programmatically (I am using PHP to generate the views), to force a backend update with wpDataTables?

Warm regards,

Jeffrey