Creating a simple REST API in PHP

I’m the author of php-crud-api and I want to share the core of the application with you. It includes routing a JSON REST request, converting it into SQL, executing it and giving a meaningful response. I tried to write the application as short as possible and came up with these 65 lines of code:

<?php

// get the HTTP method, path and body of the request
$method = $_SERVER['REQUEST_METHOD'];
$request = explode('/', trim($_SERVER['PATH_INFO'],'/'));
$input = json_decode(file_get_contents('php://input'),true);

// connect to the mysql database
$link = mysqli_connect('localhost', 'user', 'pass', 'dbname');
mysqli_set_charset($link,'utf8');

// retrieve the table and key from the path
$table = preg_replace('/[^a-z0-9_]+/i','',array_shift($request));
$key = array_shift($request)+0;

// escape the columns and values from the input object
$columns = preg_replace('/[^a-z0-9_]+/i','',array_keys($input));
$values = array_map(function ($value) use ($link) {
  if ($value===null) return null;
  return mysqli_real_escape_string($link,(string)$value);
},array_values($input));

// build the SET part of the SQL command
$set = '';
for ($i=0;$i<count($columns);$i++) {
  $set.=($i>0?',':'').'`'.$columns[$i].'`=';
  $set.=($values[$i]===null?'NULL':'"'.$values[$i].'"');
}

// create SQL based on HTTP method
switch ($method) {
  case 'GET':
    $sql = "select * from `$table`".($key?" WHERE id=$key":''); break;
  case 'PUT':
    $sql = "update `$table` set $set where id=$key"; break;
  case 'POST':
    $sql = "insert into `$table` set $set"; break;
  case 'DELETE':
    $sql = "delete `$table` where id=$key"; break;
}

// excecute SQL statement
$result = mysqli_query($link,$sql);

// die if SQL statement failed
if (!$result) {
  http_response_code(404);
  die(mysqli_error());
}

// print results, insert id or affected row count
if ($method == 'GET') {
  if (!$key) echo '[';
  for ($i=0;$i<mysqli_num_rows($result);$i++) {
    echo ($i>0?',':'').json_encode(mysqli_fetch_object($result));
  }
  if (!$key) echo ']';
} elseif ($method == 'POST') {
  echo mysqli_insert_id($link);
} else {
  echo mysqli_affected_rows($link);
}

// close mysql connection
mysqli_close($link);

This code is written to show you how simple it is to make a fully operational REST API in PHP.

Running

Save this file as “api.php” in your (Apache) document root and call it using:

http://localhost/api.php/{$table}/{$id}

Or you can use the PHP built-in webserver from the command line using:

$ php -S localhost:8888 api.php

The URL when ran in from the command line is:

http://localhost:8888/api.php/{$table}/{$id}

NB: Don’t forget to adjust the ‘mysqli_connect’ parameters in the above script!

REST API in a single PHP file

Although the above code is not perfect it actually does do 3 important things:

  1. Support HTTP verbs GET, POST, UPDATE and DELETE
  2. Escape all data properly to avoid SQL injection
  3. Handle null values correctly

One could thus say that the REST API is fully functional. You may run into missing features of the code, such as:

  1. No related data (automatic joins) supported
  2. No condensed JSON output supported
  3. No support for PostgreSQL or SQL Server
  4. No POST parameter support
  5. No JSONP/CORS cross domain support
  6. No base64 binary column support
  7. No permission system
  8. No search/filter support
  9. No pagination or sorting supported
  10. No column selection supported

Don’t worry, all these features are available in php-crud-api, which you can get from Github. On the other hand, now that you have the essence of the application, you may also write your own!

PHP-CRUD-API now supports PostgreSQL 9

For the pasts months I have been building PHP-CRUD-API (formerly MySQL-CRUD-API). It is a single PHP file that provides an instant powerful and consistent REST API for a MySQL, PostgreSQL or MS SQL Server database. The application uses reflection to “detect” the table structure and then provide an API in only a few hundred lines of PHP code. It is quite comparable to the REST functionality of the experimental HTTP plugin in MySQL 5.7.

Production performance < 10 ms

I recently finished the test suites and started using it in production. I’ve added some simple .htaccess based firewalling to ensure that only trusted applications can talk to it. Most REST API calls are handled well under 10 ms, so the performance impact on the consuming web application is acceptable. It manages to keep page loads under 100 ms even when doing several REST API calls.

PostgreSQL support and more

Recently PostgreSQL was added as a supported database. With the addition of this third database backend I also changed the name from MySQL-CRUD-API to PHP-CRUD-API. Other features that were recently added are support for “CORS pre-flight requests” (mainly for AngularJS) and “JSON batch insert”. Feature requests are very welcome on the php-crud-api Github page.

Contributions / Future

If you feel like contributing, then maybe these topics inspire you:

  1. Set up Travis automated tests
  2. Add an API documentation generator
  3. Create a plugin system for authentication, authorization and accounting
  4. Port to NodeJS, Java or C#

If you like the project, please give it a star on Github!

Features of PostgreSQL

Database systems can cost lots of money, this is fairly known. Products like Microsoft SQL Server and Oracle Standard Edition are also billed per CPU (even per core) and may also require client licenses. The costs and issues of licensing may drive people to free (not only as in beer) software. When free database systems are discussed, most people immediately think about MySQL (also owned by Oracle). But there is another, maybe even better, player in the open source market that is less known. Its name is “PostgreSQL”.

An enterprise class database, PostgreSQL boasts sophisticated features such as Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, and write ahead logging for fault tolerance. It supports international character sets, multibyte character encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and formatting. It is highly scalable both in the sheer quantity of data it can manage and in the number of concurrent users it can accommodate. There are active PostgreSQL systems in production environments that manage in excess of 4 terabytes of data. — source: http://www.postgresql.org/about/

This database system is free and very powerful. It supports almost all of the features that the paid (and free) counterparts have. Some of the interesting features are: GIS support, hot standby for high availablity and index-only scans (great for big data). Still not convinced? Check out the impressive Feature Matrix of PostgreSQL yourself. It has excellent support on Linux systems (also OSX and Windows) and integrates well with PHP and the frameworks like Symfony2.

Download & try PostgreSQL (for OSX or Windows) or follow installation instructions (for Ubuntu Linux)

Note: If you want a tool like PHPMyAdmin for PostgreSQL you might consider Adminer.

Adminer: good alternative for phpMyAdmin

NB: This is an outdated post. New Ubuntu versions use Apache 2.4 instead of Apache 2.2. You can find updated instructions that apply to Ubuntu 14.04 in a new post!

It is not a secret that do a lot of Symfony programming at LeaseWeb. Most of the projects are PHP based and use MySQL as a database. This posts describes a gem that many experienced PHP programmers have not yet heard of.

During software development you often need a tool to quickly search the database, add a column or change a value. Most PHP developers know that phpMyAdmin is a good tool that can provide you with easy access, but only a few know that there is really good alternative for this tool called “Adminer“.

In my opinion Adminer has the advantage that it has a better user experience, but this is not the only advantage it has (or may have). According to the Adminer website there is a long list of reasons why Adminer is better than phpMyAdmin.

If you are really fond of Adminer, you might want to install it in the same way phpMyAdmin is installed. Since it is not yet part of the standard Ubuntu repositories you might want to install it manually. Here are the commands you need for installation (on a Debian based system):

$ sudo su
# mkdir /usr/share/adminer
# cd /usr/share/adminer
# wget "http://www.adminer.org/latest.php"
# ln -s latest.php adminer.php
# echo "Alias /adminer.php /usr/share/adminer/adminer.php" > /etc/apache2/conf.d/adminer.conf
# service apache2 restart
# exit
$

Once it is installed and you want to update it (it wil indicate when a new version is available):

$ sudo su
# cd /usr/share/adminer
# wget "http://www.adminer.org/latest.php"
# exit
$

To uninstall it:

$ sudo su
# rm -R /usr/share/adminer
# rm /etc/apache2/conf.d/adminer.conf
# service apache2 restart
# exit
$

Try it out and let me know whether you like it or not.