Making dynamic SQL Queries with placeholders

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

It’s worth noticing that placeholders are case-sensitive, so adding %var1% in the shortcode is not the same as %VAR1%, and it will not work.

Currently, wpDataTables has 13 types of placeholders:

• %CURRENT_POST_ID% – this placeholder will be replaced with the ID of a currently active WordPress page or post. Provide a value here to be used for table generation.
• %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).
• %CURRENT_USER_EMAIL% – this placeholder will be replaced with the email of the currently logged-in user (if the user is logged in).
• %VAR1%, %VAR2%, %VAR3%, %VAR4%, %VAR5%, %VAR6%, %VAR7%, %VAR8%, %VAR9% – 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 the WordPress database (defaults to “wp_“).
• %CURRENT_USER_FIRST_NAME% – this placeholder will be replaced with the First Name of the currently logged-in user. Provide a value here to be used for table generation.
  
• %CURRENT_USER_LAST_NAME% – this placeholder will be replaced with the Last Name of the currently logged-in user. Provide a value here to be used for table generation.
• %CURRENT_DATE% – this placeholder will be replaced with the current date.
• %CURRENT_DATETIME% – this placeholder will be replaced with the current date and time.
• %CURRENT_TIME% – this placeholder will be replaced with the current time.

The placeholders’ configuration block appears when you choose all table types.

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%
AND user_id = %CURRENT_USER_ID%

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

Before you try to save the table, you first need to see if this query returns anything. By default, the placeholders %VAR1% up to %VAR9% 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 the MySQL side:

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

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.

Placeholders are also supported in the “Predefined Value” text input on the Filtering tab in column settings. You can define the placeholders %VAR1% up to  %VAR9% in the “Predefined Value” input of the column, and then pass a value of the variable through a shortcode parameter where it will be used as the 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.