Raw PHP and MySQL Eager Loading

Working with complicated and joined data sets can be tricky for MySQL database performance. For example, What if you need to load multiple authors and their books from a database. How would you work around the N+1 problem and avoid unnecessary queries to the database without an expensive join statement? The answer is quite simple.

Use eager loading.

Eager Loading

Eager loading is simply fetching records preemptively. For example, when the author records are requested you can aggregate their ID. Then, you can query for only the books used by those authors beforehand.

SELECT * FROM `authors` LIMIT 3;

If this query returns the authors of the IDs 1,2,3 you can save queries on books by running just one select statement,

SELECT * FROM `books` WHERE `author_id` IN ( 1,2,3 );

PHP: Solutions

There are two great ways to work with eager loading in PHP.

  1. Using a handy ORM.
  2. Manually using PHP's PDO and your own code.

ORM

With an ORM such as Eloquent in Laravel eager loading is very simple. Laracasts has a great video explaining the situation and then covers the solution and there is a more recent video if you have an account.

PDO

There are times you do not want the overhead of an ORM. You might also want to build eager loading yourself. In these cases, you will need to use PHP's PDO class.

Let's say we have 10 authors. If you queried their books without eager loading you would run 11 SQL queries.

query("SELECT * FROM `authors` LIMIT 10;");

// Query books 10 times!
foreach($authors as $author) {
var_dump($author);
$id = (int) $author['id'];
$books = $pdo->query("SELECT * FROM `books` WHERE `author_id` = {$id}");
foreach($books as $book) {
var_dump($book);
}
}

The code is nice and brief but at the expense of speed. 11 queries are not ideal. Imagine the speed if 100 authors are returned. Ouch!

If you use eager loading you can have the same net result with only 2 queries.

query("SELECT * FROM `authors` LIMIT 10;");

// Setup eager list of IDs
$ids = [];
foreach($authors as $author) {
$ids[] = (int) $author['id'];
}
$author_ids = implode(',', $ids);

// Get all books and group them
$grouped_books = [];
$all_books = $pdo->query("SELECT * FROM `books` WHERE `author_id` IN ( {$author_ids} )");
foreach($all_books as $book) {
$grouped_books[$book['author_id']][] = $book;
}

// Final loop
foreach ($authors as $author) {
var_dump($author);
var_dump($grouped_books[ $author['id'] ]);
}

Bulk Deleting Posts In WordPress using SQL and the WP Admin

While working on a WordPress project I needed to delete every post of a specific post type. I thought this little trick was worth sharing.

Run a simple Query to the database to move your post_type posts to the trash.

UPDATE wp_posts SET `post_status` = 'trash' WHERE post_type = 'my_post_type_here' 

Then, go to the post types trash in the WP Admin and click the “Empty Trash” button. Deleting posts this way will be sure WordPress does all the little clean up tasks.

If you have SSH access you can install and use the WP CLI too.

Installing Xdebug for PHP7

Xdebug deepens debugging PHP apps and websites to a level you can’t receive from the manual process of using code level var_dump().

Setup

To install Xdebug for PHP7 on Ubuntu you will need to do so manually. Ubuntu 15 and lower will not come with a package for PHP7 or its xDebug counterpart.

First, be sure you are using PHP 7 already by checking your version.

php -v

Should give you something like:

PHP 7.0.0-2+deb.sury.org~trusty+1 (cli) ( NTS )
Copyright (c) 1997-2015 The PHP Group
...

In my case I’m using the Larval Homestead PHP 7 branch with Vagrant and the VirtualBox provider. So, I have PHP 7 already installed and working with Nginx.

Get your php.ini

Next output your php.ini information into a file or place you can get to the information from. I like to save mine to a file called php-info.txt.

sudo php -i > ~/php-info.txt

Use the Xdebug Wizard

Send the text file information into the wizard at Xdebug Wizard. Then follow the instructions the wizard supplies.

Also, if you are using OSX you may need to run brew install autoconf.

Example

The instructions should look something like:

  1. Download xdebug-2.4.0.tgz (I like to use wget -O ~/downlaods/xdebug-2.4.0.tgz http://xdebug.org/files/xdebug-2.4.0.tgz on Ubuntu)
  2. Unpack the downloaded file with tar -xvzf xdebug-2.4.0.tgz
  3. Run: cd xdebug-2.4.0
  4. Run: phpize (See the Xdebug FAQ if you don’t have phpize.)

As part of its output it should be like:

Configuring for:
...
Zend Module Api No:      20151012
Zend Extension Api No:   320151012

If it does not, you are using the wrong phpize. Please follow this FAQ entry and skip the next step.

  1. Run: ./configure
  2. Run: make
  3. Run: cp modules/xdebug.so /usr/lib/php/20151012
  4. Edit /etc/php/7.0/cli/php.ini and fpm and add the line
zend_extension = /usr/lib/php/20151012/xdebug.so

Enable Remote Xdebug

To use Xdebug remote debugging on a host computer you need to enable remote debugging on the guest server. In my case the guest is the Vagrant Homestead VM.

In the guest php.ini file add:

xdebug.remote_enable = 1
xdebug.remote_connect_back=1
xdebug.remote_port = 9000
xdebug.scream=0
xdebug.show_local_vars=1
xdebug.idekey=PHPSTORM

Since I use PhpStorm for Debugging I set my Xdebug key to PHPSTORM. In the browser, the Xdebug and PhpStorm will look for a cookie called XDEBUG_SESSION with the value PHPSTORM. To set this cookie I use the Chrome Browser extension Xdebug helper.

Reboot Services

To load the new configurations reload your PHP and HTTP server services. In my case that is nginx and php7.0-fpm.

sudo service php7.0-fpm restart
sudo service nginx restart

Configure PHP Storm

Now that Xdebug is install we are ready to configure a “PHP Remote Debug” for PhpStorm.

  1. Under PhpStorm > Preferences > Languages & Frameworks > PHP > Debug be sure Xdebug is set to port 9000
  2. Under PhpStorm > Preferences > Languages & Frameworks > PHP > Servers add a server using port 80 and set the Absolute path on the server.
  3. Under Run > Edit Configurations... add a “PHP Remote Debug”
  4. Set the server and the Ide key(session id) to PHPSTORM

Using PhpStorm with Xdebug on a Remote Server

To start receiving requests from the remote server enable “Debug” with Xdebug helper in Google Chrome on the page you want to debug. Then, unlike local testing that uses “Start Listening for PHP Debug Connections” use Run > Debug instead.

Using the “Debug” command will open up the signal manually and open the direct connection to the remote server.

Set breakpoints and you are off.

Out of Network Servers

If you are trying to remote debug a website with Xdebug from outside your local network be sure the 9000 port is not blocked on your network. This is typically done at the router level.

As always consult the Xdebug remote documentation.

Next, ask Google “what is my public ip” and it will tell you.

Then be sure your remote server is not blocking your IP address.

Making an API endpoint in WordPress using add_rewrite_rule

There are many ways to build an API in WordPress. I personally like to build them against the base URL. If I was building one for this site it might be https://php-built.com/php-built-api/v1/.

I like to prefix my API’s to help prevent conflicts. If I was using a framework like Laravel or Slim I might take a different approach, but this is a post about WordPress.

To build an API in WordPress using your own URL it is dead simple. You only need to use three hooks: query_vars, template_include.

We will be using PHP 5.4 and WordPress 4.2.

Register your API endpoint

For now, let us focus on establishing our endpoint. Later we can get into making use of it.

To register the endpoint against the base we can use add_rewrite_rule. This is the simplest way to get a custom URL going. We can use the admin_init hook to do this.

add_action('init', function() {
$regex = 'php-built-api/v1/([^/]*)/([^/]*)/?';
$location = 'index.php?_api_controller=$matches[1]&_api_action=$matches[2]';
$priority = 'top';
add_rewrite_rule( $regex, $location, $priority );
});

In this example, we are not following a pattern like REST or RPC. We are simply drawing out the basics so we can use these ideas to build any API we want.

Don’t forget to flush your rewrite rules.

The basics of add_rewrite_rule

If you are not familiar with add_rewrite_rule it is fine. We only need to know three things when building the example API: matching the URL, fetching our desired location, the priority of the endpoint.

Matching

Matching the URL in WordPress requires regular expressions. If you are not familiar with RegEx you need to be at this point (regex101.com is a great playground).

Note: WordPress hacks RegEx by escaping / for you and adding ^ to the beginning of your expression. I imagine they are trying to be developer friendly and keep sloppy plugins for disturbing user experience.

RegEx 101 API example

Location

Now, every match we find is passed into $location as the $matches variable. This lets us assign them to specific query variables in the URL. In RegEx, matches are defined between parenthesis ().

In our code we are looking for two matches 'php-built-api/v1/([^/]*)([^/]*)/?'. We then assign them to the location 'index.php?_api_controller=$matches[1]&_api_action=$matches[2]'.

API WordPress Figure A1

A URL request to https://php-built.com/php-built-api/v1/posts/update/ would fetch our location and assign the matches as directed https://php-built.com/index.php?_api_controller=posts&_api_action=update.

Priority

Since WordPress has lots of rewrites already registered in the options table under the option_name rewrite_rules we need to be sure ours is the first one it looks for. Otherwise, a page named php-built-api might cause issues for us in the future.

We tell add_rewrite_rule to send our rewrite to the ‘top’ of the list and all is well.

$priority = 'top';
add_rewrite_rule( $regex, $location, $priority );

Establishing our custom matches as safe query variables

WordPress wants to keep you safe, believe it or not, and so we need to establish that our custom query variables are part of the program. In our case _api_controller and _api_action are the query variables in the $_GET request we want to make available.

Using the query_vars filter hook we can drop them into WordPress as safe. This will let us access them in our templates.

add_filter( 'query_vars', function($vars) {
array_push($vars, '_api_controller');
array_push($vars, '_api_action');
return $vars;
} );

Plain and simple.

Constructing our Response

Finally, we are on to the fun part and really the thing we care about most. The Response.

To construct a response we need to override the default templating engine using the template_include filter hook. This will let us define what we want WordPress to send back.

We don’t want the themes index.php template file after all.

To detect a request coming to the API use get_query_var and check for our query variables. If they are present we should send a custom response.

add_filter( 'template_include', function($template) {

$controller = get_query_var('_api_controller', null);
$action = get_query_var('_api_action', null);

if($controller && $action) {
$template = __DIR__ . '/api/v1.php';
}

return $template;
}, 99 );

This example will produce the response using our v1.php file. We can respond with JSON, XML or any other format we like. Just be sure to set the appropriate headers in the new template file.

JSON Response

If we wanted to send JSON we can use wp_send_json in our v1.php file.

wp_send_json(['api' => 'v1'] );

If we look at the HTTP response we will get exactly what we want.

HTTP/1.1 200 OK
Content-Length: 12
Content-Type: application/json; charset=UTF-8

{"api":"v1"}