Participants Database REST API

In Partcipants Database 2.5, we added an external API using the WordPress REST API. This means you can externally do things like get record data, add new records, edit records, etc. This is often used to interface with a third-party service such as a scheduler. Also, if you’ve got another website that needs to share access to a single database, that website can send requests to the website with Participants Database and use the same data.

Since we’re using the WordPress REST API, understanding how to use the Participants Database API should start with reading the WordPress REST API documentation.

The short explanation of how this works: an HTTP request is sent to your website from another source. The structure of the URL and data included in the request determines what specific action the request will perform and what information will be provided in return.

Testing the API

If you’re using a third-party service or plugin to access the API, you can most likely test it in that context. If you want to just explore the API and see what it does, or you can’t easily test things otherwise, there are third-party services you can use for that. I like and use Postman, an HTTP request testing platform that is very easy to use and gives you a lot of tools for testing APIs. Another way to test HTTP requests is using a browser extension such as Chrome RestMan.

Enabling the Participants Database API

External API requests to your website must be protected from unauthorized access. There are two settings that relate to this, both found in the Participants Database settings under the Advanced tab. The first is “Enable the Participants Database external API” which turns the whole Participants Database API on or off. It defaults to off, so the API will not be exposed unintentionally. The API does not respond at all unless this is enabled.

The second setting to look at is “Enable Limited Public Access to Plugin API” which, when checked, allows the public (in other words, anonymous requests without an authorization) to use GET requests. Public requests will never be able to alter the database, this setting only allows anonymous requests for information.

Request Authorization

For authorized requests, you must use an “Application Password” which is registered in the user’s WordPress profile. You’ll read about those in the WordPress documentation about using the API. The Participants Database API is expecting an application password with the name “pdbapi”. Application passwords are specific to each user, and when a request has an authorization header, it must include both the username and the app password. If the two match, the request is allowed.

Creating an application password for a user

When an application password/username is used to authorize a request, WordPress treats the request as though it came from the user, so the privileges and access that user has will be applied to the request. This means that although you can give an application password to a user with a low-privilege role such as “contributor” using those credentials will not allow Participants Database API requests (other than public requests) and they will see an access error. To make non-public requests, the user must have a plugin role of Admin or Editor.

The plugin role of the requester will determine which fields they can interact with. A plugin administrator can interact with (see or change) all fields. A plugin Editor will only be able to interact with fields in Public or Private field groups.

API Routes

A REST API request is simply a URL, just like the URLs that are used to open a web page. Instead of opening a web page, though, data is sent to or receieved from the requester. The URI that is used to make a request is called a “route” because it contains the routing information to the specific request. When using the API, you will use the route that performs the action you want to take. Most routes also require additional data, which can take 3 different forms: in the URL, as a GET parameter, or POST data. This will be explained in more detail when we look at the routes that the Participants Database API uses.

A complete request route will start with the domain name, then the string “wp-json” and then the plugin’s route. All Participanst Database API routes begin with “participants-database/v1”, so the “base” route looks like this:

https://{your domain}/wp-json/participants-database/v1/...

Then comes the specific route, which we explain below…

GET Requests

Get requests are used to get data or information from the database. They can only be used to obtain information from Participants Database and cannot be used to change anything in the database.

GET Request Routes

  • record/{id} gets the data from a single record in CSV export format.
  • record/raw/{id} gets the raw data directly from the database.
  • record/html/{id} gets the data as HTML-formatted strings.
  • query/record/{id} answers true/false if a record with the id exists
  • query/list/{filter} replies with the number of records that match the filter
  • list?{arguments} gets all the data from all records that match the filter in CSV export format, supports sorting and field list arguments
  • list/raw?{arguments} gets all the records that match the filter with raw data direct from the db
  • list/html?{arguments} gets the list in HTML format

Getting Data from Individual Records

The basic GET request is to get the data from a specific record, and the route for that is record/{id} so, to make that request, the URI will look something like this:

https://{your domain}/wp-json/participants-database/v1/record/{id}

The {id} part of the request is to be replaced with the record id of the record you want to get, so if you wanted to get record 705, you’d use this:

https://{your domain}/wp-json/participants-database/v1/record/705

And in return, you’ll get a JSON string with the requested information. JSON strings are the standard response that REST APIs expect, so you won’t have any trouble working with it. It is a serialization of a data object, in other words, a data object that has been converted to a string.

There are 3 record routes, the difference between then is in the formatting of the record data. The “plain” record request provides the data as strings, such as would be used in a CSV export. The “raw” route provides a direct dump of the values as they are in the database, and the “html” route provides the record data formatted for display in an HTML document.

Querying the Database

The “query” routes in the list are for getting information about what’s in the database. The first is simply a way to check if a record with a given ID is in the database. The second supplies a count of the records that match {filter}. This filter must be a List Shortcode Filter string describing the result you want.

For example, to see how many people live in Portland or Seattle, you can use a request like this:

https://{your domain}/wp-json/participants-database/v1/query/list/city=portland|city=seattle

Working with Groups of Records

The “list” routes provide a way to get information from groups of records. Filters are used to determine which records are included in the group. With the use of a filter, list routes provide a way to find records that match a one or more pieces of information, and recieve the data from those records.

The ?{arguments} that you see in the list request routes is a placeholder for an optional set of arguments you can include in the request to shape the response. These arguments work exactly the same way as the shortcode attributes by the same name. This includes these 4 arguments:

  • filter – a list shortcode filter
  • fields – a comma-separated list of fields to include
  • orderby – the field or fields to sort the response by
  • order – the sort order or orders to use

Request arguments are added as URL variables (also known as GET parameters), using the ? in the URL to signify the variables after the URI. Examples follow that will make this clear.

The filter argument is the same as the List Shortcode Filter used in the Participants Database list shortcode. Take a look at the linked article for the details. This gives you a lot of control over which records you are getting, and can be used (for example) retrieve the record for a specific person given their name or other identifying info.

For example to get the records for people in the database that live in Chicago, you can use a request like this:

https://{your domain}/wp-json/participants-database/v1/list?filter=city%3Dchicago

The “%3D” in there is the URL-encoded “=” sign, which can’t be used literally in a argument value because it has a special meaning in the arguments string. When the plugin reads the value, it will see it as “city=chicago”.

If you are using a request client, you usually don’t need to url-encode the argument data, the client will do that for you. In php, you can use a function such as http_build_query to add the arguments to the request wihout having to think about the encoding.

Field Inclusion and Sorting the List Request

If you want to limit which fields are included in a list request, use the “fields” argument. Same goes for sorting the response, but you need to use both the “orderby” and “order” arguments. Here is an example of that, combining all the arugments into the request:

https://{your domain}/wp-json/participants-database/v1/list?filter=city%3Dseattle&fields=first_name,last_name,email&orderby=last_name&order=asc

If you want to get all records, sorted by last name then first name, use a request like this, omitting the filter:

https://{your domain}/wp-json/participants-database/v1/list?fields=first_name,last_name,email&orderby=last_name,first_name&order=asc,asc

Post Requests

Post requests are used to change things in the database. There is no public access to post requests, they can only be made by an authorized user.

Most Post requests include a set of data in the post body, which would be used to update an existing record or create a new record.

Note about POST requests: always work with a backup of the main database! I suggest you install a backup plugin, making sure it’s backing up all tables in the WP database (most of them do). If a mistake is made and the database is altered unintentionally, you will be able to restore the Participants Database data by restoring the plugin’s main table. A backup plugin makes this easy: don’t rely on your hosting provider backups, that should be for emergencies only.

Post Request Routes

  • /record/update/{$id} is used to update the data of an individual record with the post body
  • /record/delete/{$id}?{delete_files} used to delete a record. Answers true if the record was found and deleted.
  • /record/add is used to create a record using the data in the post body and returns the id of the new record

Updating a Record

To update a record with fresh data, use the record/update route. You will need to know the ID of the record…here is an example request that updates a record:

https://{your domain}/wp-json/participants-database/v1/record/update/703

This will update the record with an ID of 703. The data for the update must be found in the post body, it will be an associative array indexed by the field name. Validation is not performed on the request, it will be up to the requester to provide all the needed data in the correct format.

Deleting a Record

Deleting a record adds an optional parameter to delete all the files associated with the record at the same time. Here is an example of a delete request that also deletes the uploaded files associated with the record:

Adding a New Record

Adding a record is similar to updating, but omits the record ID. The plugin will assign the ID when the new record is created. As with the record update, the data is not validated, the requester must make sure the post body data set is complete and valid. Here is an example record/add request:

The request will be answered with the ID of the new record.