Simple WordPress shortcode plugin running MySQL queries

I've recently been asked for advice regarding developing a simple WordPress plugin that can perform explicit interactions with the database.

Having no prior experience of WordPress and very short experience of PHP (and this is dating 20 years back) I had no idea how one would tackle it. I was tempted to just say no, especially that PHP is one of the technologies I just can't produce a spark of love towards. However, I said I will have a look, as curiosity got the best of me. I decided to time-box it to a day of research and prototyping. I got a working solution but I'm sure somebody knowing PHP and/or WordPress well, would point out a number of problems with it. Please do!!

I named the plugin "Pain Inducer" with apprehension of bad things that will happen if I start coding in PHP.

Complete code is available on GitHub.

Docker-compose

If you have WordPress installed on your machine already you can skip to next section.

I was not keen to install the platform directly onto my machine so used Docker instead.

Note: This WordPress activity is probably just a one-off for me as I am happy with Jekyll. I don't use PHP either so why cluttering my system with it? For experimentation like this Docker is brilliant, as I can easily discard the impact of this installation. I also get the environment setup scripted so it is repeatable. If you don't have Docker but are tempted to try it, install it from here. For Docker-compose pop-in here. I'm on Linux machine where it runs natively, but if you are on Mac or Windows I believe you will need to use docker-machine, which (I think) ships with Docker.

Quick search revealed quickstart article on WordPress.

The platform consists of two services:

  • wordpress - as a PHP application bundled with PHP interpreter and webserver
  • db - providing persistence via means of MySQL
 1version: '3.3'
 2
 3services:
 4   db:
 5     image: mysql:5.7
 6     volumes:
 7       - db_data:/var/lib/mysql
 8     restart: always
 9     ports:
10       - "3306:3306"
11     environment:
12       MYSQL_ROOT_PASSWORD: somewordpress
13       MYSQL_DATABASE: wordpress
14       MYSQL_USER: wordpress
15       MYSQL_PASSWORD: wordpress
16
17   wordpress:
18     depends_on:
19       - db
20     image: wordpress:latest
21     ports:
22       - "8000:80"
23     restart: always
24     volumes:
25       - ./my-plugins/pain_inducer:/var/www/html/wp-content/plugins/pain_inducer
26     environment:
27       WORDPRESS_DB_HOST: db:3306
28       WORDPRESS_DB_USER: wordpress
29       WORDPRESS_DB_PASSWORD: wordpress
30volumes:
31    db_data:

I've modified the quickstart docker-compose.yml file as following:

  • @lines 9-10 - map MySQL port so that I can connect to it from the host
  • @lines 24-25 - map a directory from host onto container, so that it appears within WordPress' plugins area

To start them all, navigate to top directory (cloned from Git) and run:

1~/src/wordpress-play$ docker-compose up

Docker has to download all images required to spin up the containers, so the first run will take some time. Subsequent invocations are instant. The command prompt will not appear again (foreground mode) so you have to look for a line similar to the following in your standard output:

1wordpress_1  | Complete! WordPress has been successfully copied to /var/www/html

The above command will run in the foreground, which is easier to stop for novice Docker users (Ctrl-C once to finish gracefully, again to kill it). For background execution you can use ... up -d (to start) and ... down -v (to stop and cleanup). There is more but it isn't an article about Docker.

Setup WordPress on first use

If you have WordPress installed and setup already you can skip to next section.

This step isn't tying into the original advice request, but is included for completeness.

After you start WordPress for the first time you need to set it up. It applies to either option; direct local installation and containerised run.

WordPress installation steps

Plugin files location

Notice that the repo you've cloned from GitHub already contains the PHP files defining the plugin (my-plugins/pain_inducer directory).

Files from repository

It will be automatically found by WordPress because the volumes field of docker-compose.yml maps the host directory <REPO_TOP>/my-plugins/pain_inducer onto /var/www/html/wp-content/plugins/pain_inducer in the container.

Note: if you don't use Docker for this, copy the directory pain-inducer and its contents immediately under your WordPress' plugins directory.

File pain_inducer.php controls the logic of the plugin

File dataset.php is providing means to interact with WordPress' MySQL database.

Activate plugin

Now you can just click on "Plugins" in the left nav you should see something like that:

Plugin activation

You need to choose "Activate" under "Pain Inducer", notice I've already done this here.

You are now ready to use the new shortcode!

Using plugin

Create new post and place the following inside it:

Shortcode usage

You have now made it possible to induce pain, well done!

When you save and preview you'll get view similar to this:

Plugin's presentation

Subsequent screens show adding two items:

Using my WordPress data shortcode plugin

After each submission the database is queried and brings back the newly added item.

The shortcode provides some customisation options (for demonstration).

You can provide a different prompt to the default one (as per the above print-screens):

1[induce_pain]what kind of pain can I serve you today?[/induce_pain]

You can control the length of the listing fetched from the database:

1[induce_pain pain_limiter="3"]

And, finally, you can control the table to store the pain in:

1[induce_pain table_name="some_other_table"]

This table (with applied prefix) will be created on plugin initialisation, if it does not exist.

Credits

Introduction to WordPress Plugin Development - by Chris Reynolds

WordPress – How to create a database table and insert data with HTML forms

Create WordPress Plugins with OOP Techniques