🌟 Making Dynamic SQL Queries Fun and Friendly with Placeholders! 🌟

As we move forward towards what I call the “Automation of Automation” and much better “less siloed” data interactions I decided to create some proof of concepts on WordPress with this plugin wpdatatables.  While the plugin doesn’t do everything I would like (especially related to automation), it actually has some really cool features especially with dynamic data driven placeholder Functionality.  (It really is dynamic data substitution or variable substitution.  Their nomenclature is Placeholders.). We will cover the wpdatatables placeholders in WordPress. 

Currently, wpDataTables plugin supports 6 types of cool placeholders:

1. %CURRENT_POST_ID% – Transforms into the ID of the active WordPress post/page you’re on. So if you’re on a post about “Pumpkin Pie Recipes,” this placeholder will grab that post’s ID. Neat, right?
2. %CURRENT_USER_ID% and %CURRENT_USER_LOGIN% and %CURRENT_USER_EMAIL% – If you’ve logged in, these three are your best pals! They’ll know who you are by your ID, username, and email.
3. %VAR1% to %VAR9% – These are the superheroes of placeholders. Think of them as blank canvases that you can paint any value on. And they remember the last value you gave them unless you say otherwise. Remember our earlier Halloween example? Yep, that’s them in action!💡 Practical Example: Let’s say you’re hunting for questions related to “halloween” on your website. Use the placeholder like this:
   • Place your desired word in the placeholder spot:
     plaintext

• placeholder for %VAR1%:
%halloween%

• Craft your SQL magic like this:
  sql

3. • SELECT ID, post_author, post_date, post_title, post_name, post_parent
FROM wp_posts
WHERE post_type='question'
AND post_status='publish'
AND post_name like '%VAR1%'
LIMIT 5;

     By using this placeholder now I can retrieve from the wp_posts table all the posts with halloween within their post_name.  Technically I could also override this placeholder with dynamic variables from the querystring, etc.
4. %WPDB% – Shout out to WordPress database fans! This auto-fills the prefix of your WP database. Usually, it’s “wp_”, but who knows? You might have switched things up!
5. %CURRENT_USER_FIRST_NAME% and %CURRENT_USER_LAST_NAME% – Pretty self-explanatory! If you’re logged in, these will fetch your first and last names.
6. %CURRENT_DATE%, %CURRENT_DATETIME%, %CURRENT_TIME% – Keep track of time! These buddies will always tell you the current date, date and time, or just the time.

Once you’ve picked your table type, you’ll see a little block where you can play with these placeholders. And you can mix and match them in your query as if they’re actual values.

💡 Another Example: Let’s see another example, shall we?

Here, the query is saying, “Hey, show me everything from my_table where my_field is in between the values of VAR1 and VAR2, and only if the user matches the currently logged-in user”. So if VAR1=10 and VAR2=50 and you’re the user logged in, you’ll see rows that fit that specific criterion.

By default, our placeholder buddies %VAR1% to %VAR9% are shy and don’t have values. But you can pep them up with default values. Say, make %VAR1% equal to 0 and %VAR2% equal to 100. Once you’ve set them up, your query will take those numbers and run with them, returning you the desired data.

And the fun doesn’t stop there! Embed your wpDataTable’s shortcode in your posts/pages, and it’ll fetch data for that specific user or situation. But hey, remember, you’re the boss here. Override the variable settings anytime you want, and make those placeholders dance to your tunes.

Placeholders even work in the “Predefined Value” input for columns, making it super simple to set default values for filters or editor inputs.

One last tip for the road: If you’re using a string for the placeholder (like our Halloween example), wrap %VAR1% with single quotes in the query. It’s just SQL being fancy!

So there you go! Dive into the wonderful world of placeholders, make your data dance, and have fun while you’re at it! 🎉🚀🌟