Please wait...

Making SQL queries dynamic with Placeholders

Video versionVideo overview of wpDataTables placeholders

wpDataTables placeholdersPlaceholders (variables) definition

wpDataTables Placeholders

Placeholders can be understood as predefined “search and replace” templates that will be replaced with some actual values at the execution time. They are usually used for MySQL queries.

Currently wpDataTables has 5 types of placeholders:

  • %CURRENT_POST_ID% – this placeholder will be replaced with the ID of current active WordPress page or post.
  • %CURRENT_USER_ID% – this placeholder will be replaced with the ID of currently logged in user (if the user is logged in).
  • %CURRENT_USER_LOGIN% – this placeholder will be replaced with the currently logged in username (if the user is logged in).
  • %VAR1%, %VAR2%, %VAR3% – these are the ‘variable’ placeholders. You can provide a default value here that will be used for table generation and when a different one is not defined in the shortcode.
  • %WPDB% –  this placeholder will be replaced with the current prefix of WordPress database (defaults to “wp_“).

The placeholders configuration block appears when you choose the “MySQL query” table type.

As shown in the screenshot on the right, you can insert placeholders in the query as if these are some specific values. For example, you can provide the query like this:

SELECT * FROM my_table
WHERE my_field > %VAR1%
AND my_field < %VAR2%

In this example the query means: “show me everything from my_table, where my_field is greater then variable 1, but less then variable 2, and user_id is equal to currently logged in users’s ID”.

Before you try to save the table you need to see if this query actually returns anything. By default %VAR1%, %VAR2% and %VAR3% are equal to empty strings, and %CURRENT_USER_ID% is equal to yours (user that creates the table – typically the Admin user, most probably with ID=1), so if you do not redefine the defaults, this query will be parsed like this on MySQL side:

SELECT * FROM my_table
WHERE my_field > ” 
AND my_field < ” 
AND user_id = 1

wpDataTables Placeholders
wpDataTables Placeholders

Most probably such a query would not return anything, and wpDataTables would say that there’s no data to build the table upon. So to avoid this you would need to expand the placeholder configuration block, and define the default values for the variables: e.g. set %VAR1% equal to 0, and %VAR2% equal to 100. Also you can redefine the value for %CURRENT_USER_ID%, e.g. if the table does not contain any values related to the admin’s ID, set it to “15“.

The variable values are then saved in the table settings, and will be always used as default if not overriden by shortcode parameters. Only the %CURRENT_USER_ID% is not saved and the provided value is used only at the moment of the table generation, later the actual user ID is always fetched.

So then the query would be parsed like this on MySQL side:

SELECT * FROM my_table
WHERE my_field > 0 
AND my_field < 100 
AND user_id = 15

And if this query returns any data it will successfully create a wpDataTable.

Later you can insert this wpDataTable’s shortcode in your posts or pages, and it will fetch the data for the current user. But also you can override the variable settings. Just provide the variable values in the shortcode like this:

[ wpdatatable id=12 var1=150 var2=350]

then the values from shortcode attributes would be applied to the variable and the query would be treated like:

SELECT * FROM my_table
WHERE my_field > 150 
AND my_field < 350 
AND user_id = (current_user_id)

So with using placeholders like this you can use a single wpDataTable to produce many different output tables on different pages.

Variable placeholders are also supported in the “Default value” input in column settings. You can define %VAR1%, %VAR2% or %VAR3% in the “Default value” input of the column, then pass a value of the variable through a shortcode parameter and it will be used as default value for advanced filter on the page, and for the editor input, if the table is editable.

If you are using String for Placeholder ‘variable’ %VAR1% should be surrounded with Single Quote (‘) in the query.

wpDataTables Placeholders

Never miss new features!

Join 2000+ newsletter subscribers

Never miss notifications about new cool features, promotions, giveaways or freebies - subscribe to our newsletter! We send about one mail per month, and do our best to keep our announcements interesting.

We never spam or disclose your address to anyone.