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. First, to advise in general, when it comes to custom Queries pulling data such as Custom Posts or User metaData.
As the custom posts/fields, are a part of the WordPress database;
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.
that one has a wider range of all tables you can choose from your Database outside of WPDB.
- 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.
SELECT * FROM yourDatabaseTable
WHERE columnName = '%VAR1%'
%VAR1% can be defined in the Placeholders tab when you're creating a table,
or you can pass a different VAR1 value in the table's shortcode.
-
2. Or, if you don't add the condition in an SQL query ( or don't use an SQL Table),
you can add %VAR1% in column settings -> Filtering, under "Predefined filtering value".
Then, you can have different versions of the same table, with the VAR1 value passed in the shortcode,
let's say if we had a "Regions" column and we wish to filter a different region per each page.
If we want page 1 for example only filtered for "Region A" , you can set predefined filter to this column as %VAR1% (filter type has to be set as text) :
Then for a page where we need only Region 1 filtered, use shortcode [wpdatatable id=1 var1='Region A'] ,
page 2 for only "Region B", shortcode would be [wpdatatable id=1 var1='Region B'] and so on,
to apply the values defined in the shortcode to the filter.
( This filtering method can be used for any table type, besides Simple Tables)
3. A custom idea what you can try if you have coding skills.
If you wish to make a custom workaround to pass a variable via API or PHP to change a Query :
You can take a look at our available filters for developers here ;
but combining these two or perhaps even just by using that hook/filter,
you might be able to achieve this use-case.
We do like to give examples for certain solutions, but for this use-case, we, unfortunately, don't have anything yet, so at this time you can only try to make a custom solution to make this work.
I hope this helps.
Please be advised that custom solutions with hooks are not included in our support, we can only provide ideas as examples or advise.
And i just wanted to point out that you can try wpDataTables before purchasing
on our sandbox Demo sites ( you can find links for the main plugin Demo,
as well as add-on Demos, in my signature),
and there is a 15-day money-back guarantee period,
so if you purchase the plugin, you can safely fully test it out, and if it doesn't fit your needs you can request and receive a refund in that period. ( same goes for all plugins and add-ons)
Hi,
I'm interested in your plugin for building a lot of DataTable using custom SQL queries.
But I need to make these queries dynamic, and pass one or several parameters that would go into the where clause.
For example, getting a custom post type that has a meta_value equal to get_current_user_id().
Or a search in the posts table where post_author equal to get_current_user_id().
Is there a way to create a datatable from an SQL query with variable parameters ? Maybe pass a parameter in the shortcode ?
I didn't find anything on your tutorial :
https://wpdatatables.com/documentation/creating-new-wpdatatables-with-table-constructor/generating-wordpress-db-queries-with-table-constructor/
Thank you in advance for your response.
Have a nice day.
Hello,
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. First, to advise in general, when it comes to custom Queries pulling data such as Custom Posts or User metaData.
As the custom posts/fields, are a part of the WordPress database;
pulling this data can only be done through 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.
A good way to achieve this can be experimenting using our WPDB SQL Query Constructor tool.
You can also try this other SQL Query helper Tool,
Create a MySQL-query-based table by querying a database,
that one has a wider range of all tables you can choose from your Database outside of WPDB.
- 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.
2. In regards to adding variable placeholders as WHERE to our Query :
There can be different ways to pre-filter tables in our plugin.
If you wish to try Pre-filtering tables through URL, please check out this page with examples.
-
You can also use placeholders for table generation.
1. For example, if you have a MySQL query-based table, you can use this query:
%VAR1% can be defined in the Placeholders tab when you're creating a table,
or you can pass a different VAR1 value in the table's shortcode.
-
2. Or, if you don't add the condition in an SQL query ( or don't use an SQL Table),
you can add %VAR1% in column settings -> Filtering, under "Predefined filtering value".
Then, you can have different versions of the same table, with the VAR1 value passed in the shortcode,
let's say if we had a "Regions" column and we wish to filter a different region per each page.
If we want page 1 for example only filtered for "Region A" , you can set predefined filter to this column as %VAR1% (filter type has to be set as text) :
Then for a page where we need only Region 1 filtered, use shortcode [wpdatatable id=1 var1='Region A'] ,
page 2 for only "Region B", shortcode would be [wpdatatable id=1 var1='Region B'] and so on,
to apply the values defined in the shortcode to the filter.
( This filtering method can be used for any table type, besides Simple Tables)
3. A custom idea what you can try if you have coding skills.
If you wish to make a custom workaround to pass a variable via API or PHP to change a Query :
You can take a look at our available filters for developers here ;
https://wpdatatables.com/documentation/information-for-developers/filters/
-
They pointed out this one :
wpdatatables_filter_mysql_query( $query, $tableId )
because you can use that to manipulate the Query.
There is also an option with Placeholders,
but combining these two or perhaps even just by using that hook/filter,
you might be able to achieve this use-case.
We do like to give examples for certain solutions, but for this use-case, we, unfortunately, don't have anything yet, so at this time you can only try to make a custom solution to make this work.
I hope this helps.
Please be advised that custom solutions with hooks are not included in our support, we can only provide ideas as examples or advise.
And i just wanted to point out that you can try wpDataTables before purchasing
on our sandbox Demo sites ( you can find links for the main plugin Demo,
as well as add-on Demos, in my signature),
and there is a 15-day money-back guarantee period,
so if you purchase the plugin, you can safely fully test it out, and if it doesn't fit your needs you can request and receive a refund in that period. ( same goes for all plugins and add-ons)
Let me know if you have any additional questions.
Kind Regards,
Miloš Jovanović
[email protected]
Rate my support
Try our FREE mapping plugin! MapSVG - easy Google maps, interactive SVG maps and floor plans, choropleth maps and much more - https://wordpress.org/plugins/mapsvg-lite-interactive-vector-maps/
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