Custom Web Service for an Online Directory

Transform makes it easy to integrate information from other data sources into your WordPress site. In this example we’ll be reading data from an external MySQL database that contains employee names, titles and contact information and display a directory. The display will only show the contact information to uses who are logged into the site.

The Contacts Database

Our contacts database table definition is below. The owner of this data would like the telephone and email information to only be display when a person is logged into their WordPress site (eg, a client).

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(128) | NO   |     | NULL    |                |
| last_name  | varchar(128) | NO   |     | NULL    |                |
| title      | varchar(255) | NO   |     | NULL    |                |
| telephone  | varchar(32)  | YES  |     | NULL    |                |
| email      | varchar(128) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

Creating a Web Service in PHP

Our web service simply queries the table of contacts and returns JSON data. The fields returned are based on the “PATH_INFO” sent as part of the web service request – as we’ll see below, our web service URL will include the ID of the user viewing our page. If this ID is 0 (a non-authenticated user) then we’ll only return the first_name, last_name and title fields, otherwise we’ll include the telephone and email fields. Here’s the PHP code for the Contacts web service (contacts.php):

<?php

# Set the MIME type of the output to be JSON
header('Content-type: application/json');

# Set these to values for your database
$username = 'dbusername'; # Set to your database username here
$password = 'dbpassword'; # Set to your database password here
$database = 'personnel';  # Set to your database name here

# Connect to the database
if ( ! $db = mysqli_connect( "127.0.0.1", $username, $password, $database ) ) {
    echo "Failed to connect to the database";
    exit;
}

# Define the fields of the contacts table to return
$fields = 'first_name, last_name, title';
$path = explode( '/', $_SERVER[ 'PATH_INFO' ] );
if ( count( $path ) > 1 && $path[1] ) {
    $fields .= ', telephone, email';
}

# $personnel will hold our results
$personnel = array();
if ( $result = $db->query( 'SELECT ' . $fields . ' FROM contacts 
                            ORDER BY last_name, first_name' ) ) {
    while( $row = $result->fetch_array( MYSQLI_ASSOC ) ) {
        # Add each record to the array
        $personnel[] = $row;
    }

    # Print out the record in JSON format
    echo json_encode( $personnel );
}

$result->close();
$db->close();
?>

We’ll save this in our /webservices/transform/ web folder which is protected so that only our web server’s IP address can access the web service. The output of this web service looks like this for a non-authenticated user:

[
  {"first_name":"Spencer","last_name":"Antos","title":"Senior Programmer"},
  {"first_name":"Joycelin","last_name":"Crosscombe","title":"Chief Technology Officer"},
  {"first_name":"Sal","last_name":"Devonshire","title":"Administrative Assistant"},
  {"first_name":"Sergio","last_name":"Huniwall","title":"Chief Executive Officer"},
  {"first_name":"Tilda","last_name":"Trundell","title":"Director - Sales and Marketing"}
]

Pulling It Together with Transform

Once we have the database and web service in place for our contacts page we need to use Transform to pull information from the web service and display it using a Dust template. We do this by defining a feed type, display, feed and transform instance.

The first step, creating the feed type, is simply adding information about the feed (in this case we’re calling our feed type a “Personnel Feed”):

The return type here is important as Dust templates can only be applied to JSON feeds. Next, we’ll create a new Transform display that will use Dust to convert our feed to HTML:

The template will loop over an array of objects, displaying the first_name, last_name and title fields as well as the telephone and email fields if those fields have values (which they will for authenticated users). Our feed URL will include the ID of the WordPress user – here’s the feed definition:

Note the “${WP.ID} in the Feed URI field. This will be replaced with the WordPress ID of the user or 0 if the user isn’t authenticated. Remember that our web service looks at this field and includes the telephone and email fields if the ID is not 0.

Finally, we pull it all together by creating an instance that can be dropped on pages and posts:

That’s It!

Now we can edit a page or post and use the “Add a Transform” button to drop our instance on the page:

and update the page/post. Viewing the page will show you the contact list (this is the display for a user who is logged in):