Making dynamic SQL Queries 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 a currently active WordPress page or post.
  • %CURRENT_USER_ID% – this placeholder will be replaced with the ID of a 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 they 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 than variable 1, but less then variable 2, and user_id is equal to the currently logged in users’s ID”.

Before you try to save the table, you first need to see if this query returns anything. By default, %VAR1%, %VAR2% and %VAR3% are equal to empty strings, and %CURRENT_USER_ID% is equal to the user that creates the table – typically the Admin user, most probably with ID=1. 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 likely, such a query would not return anything, and wpDataTables would say there’s no data to build the table upon. To avoid this, you will 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 they will be always used as default if not overridden by shortcode parameters. Only the %CURRENT_USER_ID% is not saved, and the provided value is used only at the instant of the table generation, after which the actual user ID is always fetched.

The query would then be parsed like this on MySQL side:

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

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 you can also override the variable settings. Simply provide the variable values in the shortcode like this:

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

and 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)

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, and then pass a value of the variable through a shortcode parameter where 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

From version 2.2 it is possible to use placeholders with other table types – JSON, XML, and PHP array through the shortcodes and as predefined values in filters and editing.

Do you have the best

essential plugins installed?

Grab the best 46 plugins. Tested and sorted.

Plugins for SEO, Caching, Security, Design and UX, Stats & analytics, Social sharing. Only the best ones.

By clicking "subscribe" you consent that the information you provide via this form will be securely processed and stored in compliance with EU privacy laws (GDPR).

Thank you for subscribing! Please check your inbox to download the WordPress goodies.