Hello.

I see what you mean.

Yes, if you wish to use a Date Type column, and if you do not wish to use a Date Range, but simply to type in one specific date,

you can use a Text Filter on the Date column.

But important to note,

this only works if the Date Format which you set in the main plugin settings is matched with the same Date Format coming from the source data.

Here is my Video example that shows this behaviour :

https://watch.screencastify.com/v/bdWIyMhanynIqXW8nGvv

As you see on my Video,  i got a Manual Table - which pulls data from my SQL Database.

In that Database Table, i have Date Format as "Y-m-d" so if i try a different Date Format in my wpDataTables, then the Text filter fails.

Same goes for tables that are linked to other existing data sources such as Excel/CSV/Google Sheet etc;

you have to make sure to match the same Date Format in the source file and it should work.

Let me know if that helps. 

Thank you.

Hi, IBED.

Firstly, I would like to sincerely apologize for the delayed response as we have been experiencing an unusually high number of tickets. I am sorry that it has taken longer than usual to respond to your concern and your patience is highly appreciated.

-

1. No problem, let me know how it goes when you try that about the initial question.

2. For this new question, regarding the Query not being able to process correctly through our SQL Parser;

yes, that is a limitation of our SQL Feature.

Our logic is based on a PHP SQL parser which has full support for the SQL dialect for the following statement types

SELECT, INSERT, UPDATE, DELETE, REPLACE, RENAME, SHOW, SET, DROP, CREATE INDEX, CREATE TABLE, EXPLAIN and DESCRIBE.

Some of them are disabled for security reasons.

Filtering, sorting, and search may not work properly if you include:

• Accent graves ( ` ) around the table name
• JOIN functions
• UNION functions
• CONCAT functions
• sub-queries

-

Looking at your Query, i believe this is a combination of SQL functions and statements that our parser is not able to properly process, but there's a solution.

If you confirmed that this Query can return the data as you need it when you run it in your Database Management Tool, such as PHPMyAdmin,

then you can make an SQL VIEW from this Query, name it anything you like, let's say "View_Name",

then in our SQL Table in the plugin, you can call it like :

SELECT * FROM View_Name

and it should return the data with no errors.

 If you need help with that, you can see our video, where we show an example of using View in our plugin.

-

Let me know If that helped

Hi, Davide.

Thank you for your patience and understanding.

I will do my best to cover all the details, and if needed, we can double-check with our senior 2nd Tier Team for any points,  let me know if i miss any of them.

1. Regarding the "single date" filter with using the Date Picker.

I will double-check with our 2nd Tier Team if they know any workaround that might allow to use a single Date with a Date Picker for a Date Filter.

As far as i am aware, it is only possible with a Text filter with our current capabilities,

but i understand that this is inconvenient for your use-case.

As soon as they advise we will come back to confirm this.

2. "How can I do to enable the statement types that are currently disabled (I mean in my setup)? Can you explain to me which plugin files I have to modify for this, and how?"

- If you mean to allow the additional SQL Statements which are disabled from our SQL Parser;

i am sorry, but it is not possible to do this in any way.

Our SQL Feature is based on this SQL Parser, and the statements which are disabled for security reasons can not be added at the moment.

Maybe it can be done with some custom work, but i am not sure if it is possible even with that;

our developers are very busy at the moment, working on some priority tasks and fixing bugs and issues with our plugins, so they won't be having the time for custom work in the near future.

If you wish, i can keep a lookout and i can advise you if one of our developers becomes available for custom work in the near future, if you don't find anyone else in the meantime.

-

But this being said, i will still reach out to our 2nd level Team, to see if we can go over your original Query which you sent us and assess if perhaps some basic modifications could be done,

in order for you to get the Query results needed without having to use the SQL VIEW.

-

When they check all of this, we will report back to see what we can do.

I can't promise that we will return with a working solution for your use-case, but we will do our best.

Thank you for your patience.

3.  "Is it possible to use placeholders %varX% to intercept an input? 

An example: a logged-in user performs an action on the front-end (e.g. 'click on a button') setting a variable that fills out one of the placeholders which, therefore, the query can read.

 In other words, allow wpdatatables to receive an input from outside. If yes, how?"

-

Unfortunately, we do not have an exact working example like what you described,

so a custom solution would be needed.

-

I went through some cases that seem similar, so I will share it with you, i hope it might help.

For example, this user wanted to convert the values in one of the columns to a link that when clicked will run another query passing in the value clicked as a variable in MySQL and then display a second table with the new data.

-

This is how we first advised him, to construct the column by using CONCAT with our SQL Query based tables :

if there's a page on your website, that contains a string from a column (for example "page1" ), 

you can use the SQL CONCAT function, like:

SELECT column1,
        CONCAT('<a href="www.yourwebsite.com/',yourTable.column2,'/">',yourTable.column2,'</a>') AS LINK
FROM yourTable

That would append "page1" found in column 2 to www.yourwebsite.com/, so the generated link would be www.yourwebsite.com/page1/, hyperlinked behind "page1" - entry in the cell.

-

The user then installed "Insert PHP Code Snippet", and this is what he said :

"Using that plugin, I created a shortcode that looks like this:

Shortcode Name: GetReport

<?php echo do_shortcode('[wpdatatable id=4 var1="' . $_GET['ID'] . '"]'); echo do_shortcode('[wpdatatable id=3 var1="' . $_GET['ID'] . '"]'); ?>

Then in my page where the table is generated, I insert the shortcode below to display the tables using the ID param from the URL:

[xyz-ips snippet="GetReport"]

Hopes that can help the others looking for a solution".

-

And another example from a different user.

We called this case "PHP Array Forward GET parameters ( Pass Dynamic Data)"

There is a hook available in our plugin:

wpdatatables_filter_url_php_array

In the hook, you will do something like this:

function test ($url,$id) {
// all data from current user
// you can fetch only what you need
$userData = wp_get_current_user()->data; 
  if(isset($userData)){  
     $url .= '?' . http_build_query($userData);    }
return $url;
}
add_filter('wpdatatables_filter_url_php_array', 'test', 2 , 10)

In the PHP file, you will use the $_GET method to take the values from the URL, which is filtered with the hook

// you can check then for ID or user_login or user_pass or user_nicename or user_email ...
if(isset($_GET['user_login'])){    
  //do somthing
}

Just wanted to share that, but i am not sure if that will help.

-

At this time, we don't have any built-in solution to achieve this, but you can suggest it to our developers - they will do their best to make a solution in the future.

Please feel free to search on our suggestions page, 

 to see if someone may be already suggested this feature. If you can't see it, feel free to add your suggestion there,  and as more people vote, the feature will move higher on the priority list.

You can certainly follow our changeLog page if you'd like ( it is also available in the plugin dashboard), where we state any changes/new features/bug fixes during updates;

and our newsletter, so you're informed about new features, bug fixes, freebies, etc.

-

If you have coding skills and wish to try to make a custom solution now,

you can check out our available hooks for Developers on this documentation and see if you can find any hook that might help.

Please be advised that custom solutions with hooks are not included in our support.

You can also research resources such as Stack Overflow to see if any other user perhaps found a workaround.

( We do like to give examples for certain solutions, but for this use-case, we, unfortunately, don't have anything yet)

-

I will still double-check all of this with our senior 2nd level Team,

and i will come back to report if they know of any additional workaround that i haven't shared already.

Thank you for your patience.

Hi, Davide.

I was advised by out 2nd level Team.

The main issue are the sub-queries you have here.

Our SQL Parser is not able to fully work with that, unfortunately.

That is why a VIEW is needed in this situation.

Unfortunately, out SQL Feautre is not able to run the same Queries that your Database Management Software can,

and it is not meant to be used as a database management tool.

We can see from your original Query that you are combining data from User Meta with some other tables.

Generally speaking, when we need to pull data from User Meta, or custom posts/fields, are a part of the WordPress database;

pulling this data can only be done through a custom Query with MySQL query-based tables.

Please note: Using this plugin feature requires at least a basic knowledge of SQL. It is assumed that you can create the table in some MySQL data manager (e.g., PHPMyAdmin, MySQL Workbench), and prepare a SQL query that will return the data you need. 

But as we pointed out on the MySQL Tables Documentation from that hyperlink,

our SQL Feature is based on an SQL Parser ,which will not work the same as Database Management Tool such as PHPMyAdmin,

it has various limitations, and those points i sent in the previous reply in more details.

A good way to achieve this can be experimenting using our WPDB SQL Query Constructor tool.

- Please note: this tool is not an ultimate query generator. It simply constructs a suggestion of a query. We are constantly working to improve it, but SQL is such a complicated and flexible language that full automation for constructing queries is next to impossible. 

Consequently, the more complicated the query, the higher is the probability that it will not return exactly what you need. So, you will often need to play around with the resulting query.

Please be advised that writing custom SQL Queries or debugging Queries does not fall under what our support covers.

You can check out this official page of WP codex,  for help with writing custom Queries.

-

I am sorry that we are not able to provide further help,

but currently the only "easy way" to solve this will be with an SQL VIEW,  as you did;

if you need to avoid the VIEW, we are not able to provide debugging of the custom SQL Query, unfortunately.

2. And regarding your last question,

our senior Team does not have any workaround  to achieve what you described at the moment.

At this time, we don't have any built-in solution to achieve this or any workaround example,

 but you can suggest it to our developers - they will do their best to make a solution in the future.

Please feel free to search on our suggestions page, 

 to see if someone may be already suggested this feature. If you can't see it, feel free to add your suggestion there,  and as more people vote, the feature will move higher on the priority list.

You can certainly follow our changeLog page if you'd like ( it is also available in the plugin dashboard), where we state any changes/new features/bug fixes during updates;

and our newsletter, so you're informed about new features, bug fixes, freebies, etc.

-

If you have coding skills and wish to try to make a custom solution now,

you can check out our available hooks for Developers on this documentation and see if you can find any hook that might help.

Please be advised that custom solutions with hooks are not included in our support.

You can also research resources such as Stack Overflow to see if any other user perhaps found a workaround.

( We do like to give examples for certain solutions, but for this use-case, we, unfortunately, don't have anything yet)

Hi, Davide.

- In regards to freezing first column;

our developers are actively working on adding this feature,

they will also add "freeze header row" feature;

They are working on finishing both very soon, we just can't promise an exact ETA on it.

You can certainly follow our changeLog page if you'd like ( it is also available in the plugin dashboard), where we state any changes/new features/bug fixes during updates;

and our newsletter, so you're informed about new features, bug fixes, freebies, etc.

-

At this time, if you wish to try it right now, it can only be made with custom solutions.

Here is a workaround suggestion from our customer that managed to achieve a similar solution :

"I actually created a fixed header and column using the translation of x and y (no absolute or fixed position used), very similar to the fixed header provided by weihs5  on this site, and here's one solution, take a look:

First of all, you gotta set the z-index for each component below:

jQuery("#table td").css({"z-index": "-1", "position": "relative"});
jQuery("table tr td:first-child").css({'z-index': '1', 'position': 'relative'});
jQuery("table th:first-child").css({ 'z-index':'2','position':'relative'});

Then you gotta add listener on scroll like this:

jQuery('document').scroll(function () { 
  scrollBlock();});

If your table is set to be scrollable, then you might want to add this in as well:

jQuery('.wdtscroll').scroll(function () {
 scrollBlock();});

And here's the scrollBlock() part, it is ugly though, but it works for me though.

 (noted: the numbers work for my theme, if it is not working correctly on yours, then try to adjust the number until it fits.)

function scrollBlock(){  var table = document.querySelector('table');
var top = table.getBoundingClientRect().top;
top = document.body.className.search('admin') > 0 ? top - 32 : top + 3; 
// this value can change    
        var left = table.getBoundingClientRect().left;     
       if(left >= 20){ 
// before scrolling left (before transforming)           
                     if(top >= 0){
 // before reaching header bar          
          jQuery("#table_1 thead th").css("transform", "translateY(0px)");       
              translate(jQuery("table th:nth-child(1)"), 0, 0);              
       translate(jQuery("table td:nth-child(1)"), 0, 0);                      
              } else{ // after reaching header bar                  
   jQuery("#table_1 thead th").css("transform", "translateY( " + (-top) + "px)");       
              translate(jQuery("table th:nth-child(1)"), 0, -top);           
          translate(jQuery("table td:nth-child(1)"), 0, 0);         
        }            } else { 
// after scrolling left            
                    if(top >= 0){      
              jQuery("#table_1 thead th").css("transform", "translateY(0px)");        
            translate(jQuery("table th:nth-child(1)"), -left + 46, 0);    
                translate(jQuery("table td:nth-child(1)"), -left + 46, 0);       
                             } else{              
       jQuery("#table_1 thead th").css("transform", "translateY( " + (-top) + "px)");  
                   translate(jQuery("table th:nth-child(1)"), -left + 46, -top);        
             translate(jQuery("table td:nth-child(1)"), -left + 46, 0);          
           }           
     }      
      }
function translate(element, x, y) {
var translation = "translate(" + x + "px," + y + "px)"
element.css({    
  "transform": translation,    
   "-ms-transform": translation,      
    "-webkit-transform": translation,   
  "-o-transform": translation,    
  "-moz-transform": translation, 
  }
);
}

It is kind of messy, you definitely can't copy and paste and expect it to work for you. You need to spend some time to figure it out. Hope this helps everyone!"

We hope this might help to try a custom solution, but important to note that this is just shared from our user, we are not able to confirm the solution or to provide troubleshooting for it.

-

And an important note for future questions.

This time we covered multiple subjects in the same ticket, and as you see, if we had to escalate any questions for our senior Team, it would be difficult to read the entire ticket in a clear way;

so for the future, 

when you have multiple questions or issues which are for different subjects/topics,

 please open a new ticket for each subject, and we will help/advise more effectively. 

In that way, issues and questions which are related to different subjects will be in separate tickets so other users or our support agents can find them easily. 

Our policy is to have one issue or question per ticket for the reasons described already.

Thank you for understanding.

Hi, Davide.

Sorry for my late reply to this issue.

-

1. In regards to the "auto refresh", this is not related to automatically updating your table with new data.

The auto refresh is just set to automatically refresh the table on the page with that the table has in the back-end.

-

But this is a Manual Table, and it is not possible for it to be automatically synchonized/linked with any changes you make on the source Excel.

We will explain more how this works :

There are two ways to create wpDataTables with Excel:

1. Create a table linked to an existing Excel file,

if you choose to Create a wpDataTable and upload the Excel file :

 in which case you get to select the file (upload it) that'll be used to pull the data.

 This file will be stored in ../wp-content/uploads/YEAR/MONTH/ folder. 

The file on that location can be changed/edited, but the column structure can't. 

So, if you have the file with the same name,

 the same number of columns, same headers, and the same order of columns - you'd be able to replace the original file with the new file, and wpDataTables would pick that up.

-

Alternatively, if you select "URL from any domain"

Creating a table linked to an Excel file by providing its URL is pretty much the same as it is for creating a table by uploading an Excel file. 

The only difference is that you would choose the URL from any domain option instead of the WordPress Media Library option under File location when creating the table,

and when you want to edit the source file,   you don't have to edit the one that is now in your Media Library folder,

but you can edit on the original source file.

2. 

 If you import the file ( such as in this case), the plugin reads the source file and creates a manual table. 

Once the table is created, it is no longer linked to the source file, so changing data in the table will not show up in Excel or Google Spreadsheet, and vice-versa. 

To modify this table, you can either switch to the Excel-like view, 

and then select and copy the range of cells you want to paste from the source file, and paste it in the Excel-like view, 

or you can import a new/edited CSV to update the table, as well, in the following ways :

• Replace data within an existing table.
• Add data to an existing table.
•   Replace the entire table with new data.

You can see more details on Updating manual tables from source files (CSV, Excel or Google sheet) here.

So to summarize, when you import the Excel data to make a new Manual Table - it will no longer be connected to the source file which was used for import.

When you need to update that table, you can only manually update the table with new data from a file later on.

Let me know if that makes sense and if anything is unclear. Thanks.

Hi, Davide.

Sorry to disappoint you, but it seems there was a misunderstanding.

We explained in the previous reply, but we will try to clarify with additional details or maybe to summarize the main points.

The short version is , something like that is not possible with our plugin.

If you link the source Excel with a table, the table will update any changes you make on the connected Excel file,

but it does not make a new table in your database/ it is not possible to make SQL Query from this data.

-

On the other hand, if you import the Excel, then it does make a new Manual Table/ with its own columns and rows , you can make SQL Queries from it ,  but it will lose any link/connection with the source Excel file.

To elaborate in a bit more details :

When you work with an Excel file in our plugin,  if you choose this option :

1. First option : Create a data table linked to an existing data source

This is going to make a link between your source Excel file, so any changes you make in that file are going to output in our table.

But this does not make a new MySQL Table in your database.

Thesedatatables that are linked to live data source are not saved in the database like manual tables, 

but instead, their data is stored in database table 'wp_wpdatatables_rows'. 

So, pulling data from that table is very complex because this database table stores data in JSON format, not like manual tables (columns * rows).

Then, even if you  pull the data as needed, you would also need to unserialize the JSON data.

This falls under advanced MySQL, and unfortunately, it will not be covered by our support.

If you wish to try that, you would have to search for custom solutions, for example, such as this one from Stack Overflow, i hope it helps.

2. Second option : Create a data table by importing data from a data source.

This will take the data from the Excel,  and it makes a new table in your SQL Database.

This table will have its own set of columns and rows - it will be editable in our plugin,

but it will lose any connection/link from the source Excel.

So any change you make in the source Excel,  are not going to output in this table.

-

Later on, if you need to update the changed data from the Excel file,

you can only manually upload this data, as explained in this Documentation :

Update manual tables from source files (CSV, Excel or Google sheet)

-

Let me know if that explains it, or if anything is still not clear. Thank you.