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:
- Support HTTP verbs GET, POST, UPDATE and DELETE
- Escape all data properly to avoid SQL injection
- 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:
- No related data (automatic joins) supported
- No condensed JSON output supported
- No support for PostgreSQL or SQL Server
- No POST parameter support
- No JSONP/CORS cross domain support
- No base64 binary column support
- No permission system
- No search/filter support
- No pagination or sorting supported
- 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!
please, read http://www.phptherightway.com/#mysql_extension
SQL Injection and more.
Don’t use this code in prod.
@Nico: Thank you for sharing your concerns. It is great that you are aware of “PHP the right way” (great reference isn’t it?). I am, like you, very much concerned with SQL injection problems and therefor the code is designed to not have any. Still I would be very grateful if you would find a SQL injection bug in the above code. If you do, then please report it, for everybody to see and comment on, in the issue tracker on Github: https://github.com/mevdschee/php-crud-api
Hi, i’m a web developer interested in building a hybrid app; at the moment i’m learning angularjs and im using ionic framework, this tools provide me enough resources to make a front development; my problem is, i really don’t know how to implement a database management for my app, as i want my users to be able to store data in a local database and synchronize it to server, i use php and mysql; do you have any recommendation on wich tools should i use? i’ve been searching but there are lots of them, pouchdb, sqlite, couchdb, etc… wich of them should i use knowing that i use php and mysql? Thank you for your help.
@Manuel: Thank you for you comment. I have no experience building hybrid apps, but pouchdb looks nice to me. Maybe some other reader can advice you on this.
Having read this I believed it was rather enlightening.
I appreciate you taking the time and effort to put
this informative article together. I once again find myself spending way too much time both reading and leaving comments.
But so what, it was still worthwhile!