Note: API Builder is no longer maintained. This codebase should still work but will not receive updates. If you are interested in maintaining the project please let me know (5-23-2016).

PHP API Builder

Easily transform MySQL tables into web accessible JSON APIs with this mini library for PHP.

Getting Started | Customizing your API | Making Requests | Using the Data | Submitting your Data | API Parameter Reference

Getting Started

This PHP API Builder is used to build simple http JSON APIs from MySQL databases. With it you (or anyone if you choose to make the API public) can access data on the web through an easy-to-setup api.php page. Using the API Parameters provided in this mini library users can query a database through that api.php page using GET parameters included in the request's URL and return the results as valid JSON. A full list of available API parameters is located in the API Parameter Reference section of this documentation.

How it works

Setting up the API is easy! To add an API to an existing MySQL table simply place this repository's api_builder_include/ folder and api_template.php file in the directory where you want your api page to be located (If you want your api accessible at yourdomain.com/api.php you should put these files in your root directory). Next update the api_template.php file to reflect your database info and your desired API customization. Then save the updated file as api.php or whatever you want your API page to be called.

Thats it! You can now access the data from your MySQL database using the API Builder URL Parameters. Below is an basic example of how the $api object can be setup.

Download

You can direct download a .zip of API Builder by clicking here. The API Builder mini lib was built and tested using PHP 5.4.4 and results when using earlier versions of PHP are unknown.

Example

Throughout this reference an example database will be used. This example table, named users, holds information about imaginary users that belong to an organization. The api.php for this example is as follows:

<?php

	 //include the API Builder mini lib
	 require_once("api_builder_includes/class.API.inc.php");

	 //set page to output JSON
	 header("Content-Type: application/json; charset=utf-8");
	 
	  //If API parameters were included in the http request via $_GET...
	  if(isset($_GET) && !empty($_GET)){

	  	//specify the columns that will be output by the api
	  	$columns = "id, 
	  				first_name,
	  				last_name,
	  				email,
	  				phone_number,
	  				city,
	  				state,
	  				bio";

	  	//setup the API
	  	//the API constructor takes parameters in this order: host, database, table, username, password
	  	$api = new $API("localhost", "organization", "users", "username", "secret_password");
		$api->setup($columns);
		$api->set_default_order("last_name");
		$api->set_searchable("first_name, last_name, email, city, state, bio");
		$api->set_default_search_order("last_name");
		$api->set_pretty_print(true);

	  	//sanitize the contents of $_GET to insure that 
	  	//malicious strings cannot disrupt your database
	 	$get_array = Database::clean($_GET);

	 	//output the results of the http request
	 	echo $api->get_json_from_assoc($get_array);
	}
?>

Use the api_template.php to create your own api.

Customizing your API

The API Builder mini lib features many more complex API setups than the one demonstrated in the api_template.php file. Some of these features include:

• Making an API Private so that only you can access the data it provides
• Using API keys to track and limit hits-per-day usage to specific users
• And setting API defaults for number of results returned per request, default order to return results, etc…

All API class setup methods (excluding the constructor) begin with the word set. A full list of these setup methods and a brief description can be viewed below. For more information about each method view the class.API.inc.php source.

API Class Setup Methods

Names in bold denote methods that are required to use when building an API. All other methods are optional.

• API::__construct($host, $database, $table, $username, $password) Instantiates the API object and creates a MySQLi database connection.
• API::setup($columns) tells the API object which column values to use when outputting results objects. The $columns parameter is a comma-delimited list of column names that correspond to the column names in your database.
• API::set_default_order($column) sets the default column for the api to order results by if no 'order_by' parameter is specified in the request.
• API::set_default_flow($flow) sets the default flow if none is specified in the request.
• API::set_defualt_output_number($default_output) sets the number of JSON result objects each API request will output if no 'limit' parameter is included in the request.
• API::set_max_output_number(int $max_output) sets the max number of JSON result objects allowed per request.
• API::set_pretty_print($boolean) sets the default JSON output as human readable formatted
• API::set_searchable($columns) enables the API 'search' parameter and specifies which columns can be searched. Again the $columns parameter is a comma-delimited list of column names that correspond to the column names in your database. Only Text columns that have been FULLTEXT indexed may be included in the columns list.
• API::set_default_search_order($column) sets the default columns for the API to order API 'search' parameter results by if the MySQL FULLTEXT Match()…Against()… statement is executed in boolean mode (required only if API::set_searchable() has enabled columns to be searched).
• API::set_exclude_allowed($boolean) enables the [API 'exclude' parameter]. This method's parameter can only be TRUE if your database's table includes an 'id' column (or whatever unique column name is included as this method's optional parameter).
• API::set_key_required($boolean) makes your API require a unique key for each request. For more information on limiting and tracking API users visit the Protecting your API section of this documentation.
• API::set_hit_limit($number_hits_per_day) sets the number of API hits per API key per day.
• API::set_private($private_key) makes the API private (i.e. only you can use it). For more information on this method visit the Protecting your API section of this documentation.
• API::set_no_results_message($message) sets the error message when no results are found in a request.

If the API setup is configured incorrectly the api.php's resulting JSON response object will contain a config_error array of messages describing the errors instead of a data property.

Other Methods

Aside from the API setup methods there are a few other methods in that can be useful to know

• API::get_json_from_assoc($assoc_array) returns the API results as JSON from an associative array of API Builder Parameters. This is how you actually print the API results to the browser.

And from the static Database class:

• Database::init_connection($host, $database, $table, $username, $password) creates a database connection. This static method is called from inside API::__construct() so if you have already initialized an API object you should not need to use this static method unless the database has been closed.
• Database::close_connection() closes the MySQLi database connection.
• Database::execute_sql($mysql_query_string) executes the MySQL statement provided as its parameter and returns a boolean representing it's success.
• Database::get_all_results($mysql_query_string) returns a 2D array of table results from the MySQL query string passed as it's parameter.
• Database::clean($string_or_array) encodes the parameter using htmlspecialchars and mysqli_real_escape_string and returns the cleaned string. Useful for sanitizing input before injecting it into the database.
• Database::execute_from_assoc($assoc_array, $table_name) inserts rows into (or updates existing rows if optional parameters are used) the database from an $assoc_array where all keys in the array are their value's column names in $table_name. The table name can be accessed via the Database::$table static public property.

Note: All Database class methods are static.

For more info on or the mini library itself you can read the source code. More examples are coming soon, especially for how to update your database thought GET or POST using Database::execute_from_assoc($string_or_array)!

Protecting your API

There are two ways of limiting access to your API using the setup methods. The first, and most private, is by setting up your API so that only you (and people who you give your private key to) can access it. The second is to track and limit API usage using API keys that are distributed to your users.

Making your API Private

To make your API private include the following when setting up your api.php page where $private_key is a unique 40 character SHA1:

$private_key = "4e13b0c28e17087366ac4d67801ae0835bf9e9a1";
$api->set_private($private_key);

Then when you make an http request to your API just prepend your private key to the request using the API Private Key Parameter:

http://fakeorganization.com/api.php?last_name=Renolds&private_key=4e13b0c28e17087366ac4d67801ae0835bf9e9a1`

Viola… your own private API.

Lim