Simple PHP REST API script for MySQL

With single page applications (or SPAs) becoming very popular very quickly we see a rising need to add APIs for everything. Most companies take a first step by creating a simple data-driven API. I wrote a PHP script that generates a simple and fast REST API from your MySQL tables with full CRUD support. No MySQL? No problem: PostgreSQL and MS SQL Server are also supported. Even pagination and filtering is supported! It is only 450 lines of code, not exactly rocket science, but it may be useful when you need to whip up a Minimum Viable Product (MVP).

Limitations

  • Authentication or authorization is not included
  • Complex queries or transactions are not supported

Features

  • Single PHP file, easy to deploy.
  • Very little code, easy to adapt and maintain
  • Streaming data, low memory footprint
  • Condensed JSON: first row contains field names
  • Blacklist support for tables (and columns, todo)
  • JSONP/CORS support for cross-domain requests
  • Combined requests with support for multiple table names
  • Pagination, sorting and search support
  • Relation detection and filtering on foreign keys
  • Relation “transforms” for PHP and JavaScript

Configuration

This is a single-file application. In the bottom of the file you find the configuration:

$api = new MySQL_CRUD_API(
	"localhost",                        // hostname
	"user",                             // username
	"pass",                             // password
	"db",                               // database
	false,                              // whitelist
	array("users"=>"crudl")             // blacklist
);
$api->executeCommand();

Example output

When you request the URL http://localhost/api.php/cate* you will be matching a single table (“categories”) in the configured database. The (formatted) output would be something like this:

{
    "categories": {
        "columns": [
            "id",
            "name"
        ],
        "records": [
            [
                "1",
                "Internet"
            ],
            [
                "3",
                "Web development"
            ]
        ]
    }
}

As you can see the column names are only at the start of the object and the table name is used as a key on the object allowing for multiple table matches when using a wildcard (star) in the URL.

Get it on Github!

If you want to get it check out my Github page for this little project:

https://github.com/mevdschee/php-crud-api

Contributions, forks and additions are more than welcome.

Share

5 thoughts on “Simple PHP REST API script for MySQL”

  1. Trying to implement it right now on my site http://SocialFeeds.Club/

    The site provides many Social Rss Feeds but more and more people asking to get RestAPI access. Looks good so will give it a try.

    Thanks a lot and enjoy your day!

  2. @Tom: Great Tom, good luck! One word of warning though: before you use it in production for more than reading data I would suggest you add some sort of an API key system.

Leave a Reply

Your email address will not be published. Required fields are marked *