WordPress Dev Section 27 – Plugin Development: Custom SQL Database Table


WordPress

Updated Mar 14th, 2022

Table of Contents

Chapter Details

Understanding The Pros and Cons of the “Post” Paradigm

Storing Plugins data in the database.

WordPress uses the “custom-post-type” way versus the “custom database” solution. The “custom database” solution has 10x faster on queries. Why is this?

9 out of 10 times, the WP way of handling data is the smarter, correct choice. Need to know when to reach for the “custom database” option.

Accessing the “mySQL” database via “adminer” tab of “local,” (local by flywheel local dev environment).

CPT is fast but adding “meta” is what slows down the query. The flexibility comes at a price.

Adds a “wp-pets” table.

When we use our own table we give up a ton of free features that WP handles for us. We go back to the stone age and need to handle everything ourselves, Admin UI for CRUD operations, Rest API endpoints for CRUD operations, Auto-ties posts to permission-types or permission-roles, etc.

Go the “custom-table” route only if both of these are true. 1.) Need to query (not just store but actually search) on custom meta-data. 2.) And you anticipate there being, not just 100’s or even 1,000-2,000, of a custom-post-type. The example in the course has 100,000 items.

Creating Our Own Custom Table

De-activate plugin to create same functionality without “custom-post-types.”

Download a starter zip “new-database-table” folder from the resources of this chapter and save it into the “plugins” folder. Activate the plugin.

We only need to create the skeleton and structure and shape once. So we have a hook that triggers an “onActivate” function.

class PetAdoptionTablePlugin {
  function __construct() {
    global $wpdb;
    $this->charset = $wpdb->get_charset_collate();
    $this->tablename = $wpdb->prefix . "pets"

    add_action('activate_new-database-table/new-database-table.php', array($this, 'onActivate'))
    // add_action() admin head
    // add_action()  wp_enqueue_scripts
    // add_action()  template_include
  }
}

function onActivate() {
  require_once(ABSPATH . 'wp-admin/includes/upgrade.php')
  dbDelta("CREATE TABLE $this.tablename (
    id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    birthyear smallint(5) NOT NULL DEFAULT 0,
    petweight smallint(5) NOT NULL DEFAULT 0,
    favfood varchar(60) NOT NULL DEFAULT "",
    favhobby varchar(60) NOT NULL DEFAULT "",
    favcolor varchar(60) NOT NULL DEFAULT "",
    petname varchar(60) NOT NULL DEFAULT "",
    species varchar(60) NOT NULL DEFAULT "",
    PRIMARY KEY  (id)

  ) $this.charset;")
}

“dbDelta” function is a WP function that is not always available. “delta” means change so if we ever de-activate and then re-activate it.

Note: You need two spaces after the “PRIMARY KEY,” check out the WP docs for stuff like this.

To make the table name and the charset dynamic, we use global object “global $wpdb” by storing in the class “__construct” function.

Work on the “onAdminRefresh()” function

Leverage the existing “populateFast()” function that inserts 100,000 pets into the table in less than 10 seconds.

Querying Our Table

Start by focusing on SQL by using the test area in “adminer” to practive writing a SQL statement.

SELECT *
FROM `wp_pets`
WHERE species = 'cat' AND birthyear > 2017
ORDER BY birthyear DESC
LIMIT 100

Go into the “new-database-table.php” file and checkout the “loadTemplate” function.

Go into “/inc/template-pets.php” file.

<?php 
  global $wpdb
  $pets = $wpdb->get_results("SELECT * from wp_pets WHERE species = %s LIMIT 100")
  $var_dump($pets)
?>

Importance of security. SQL injection. Use values from the address bar.

WP gives us a tool called “$wpdb->prepare(a, b)”

Populate table as html with “php foreach”

Building Dynamic Queries (Part 1)

“GetPets.php” file

Hardcode the URL to test

Using the “prepare()”

How to make dynamic URL

$query.= $his->createWhereText()

Quick Note About PHP Arrays

PHP does not distinguish between indexed and associative arrays.

To review: you can follow along with the next video step-by-step without any errors, but just know that my placeholders property is technically unnecessary; you can instead feed our args property into $wpdb->prepare as the second argument.

Building Dynamic Queries (Part 2)

Working in the “GetPets.php” file

Build out a “createWhereText()” with an “if statement” and a “foreach.”

Create a separate function named “specificQuery” that uses a switch statement for cases such as “minweight” and “maxweight” and “minyear” etc.

Swap out the results hard-coded area.

Sending two requests is more performant surprisingly.

In the “template-pets.php” file hollow out hardcoded values.

Form that generates the URL on submit is not shown.

Create Pet From Front-End

Add a form to add a new pet. Use “if statement” to check if the user is the “administrator.”

Use “php” in the form’s “action” attribute to post to a WordPress system file.

action="<?php echo esc_url(admin_url('admin-post.php'))?>"

Add another input with “type=hidden” and “name=action.”

“createPet” function

Delete Pet From Front-End

in template file add a label (only to admin users)

Add a form with inputs and with button to delete pet.

Write the function and action hook.

“wp_safe_redirect” function

“number_format” to get comma separated 000s.