PHP Classes
elePHPant
Icontem

PHP API Database: Provide access to database records via REST API

Recommend this page to a friend!
  Info   View files Example   View files View files (42)   DownloadInstall with Composer Download .zip   Reputation   Support forum (2)   Blog    
Last Updated Ratings Unique User Downloads Download Rankings
2018-12-06 (3 days ago) RSS 2.0 feedNot enough user ratingsTotal: 214 This week: 48All time: 8,030 This week: 7Up
Version License PHP version Categories
database-web-api 0.4.65Free for non-comm...4PHP 5, Databases, Web services
Description Author

This package can provide access to database records via REST API.

It can handle HTTP requests sent using REST to perform queries on database table records based on the HTTP request parameters.

The package can take options that define which tables may be queried.

It supports many types of databases via the PDO extension.

The request response may be outputted in JSON, XML and HTML formats.

  Performance   Level  
Name: Marco Cesarato is available for providing paid consulting. Contact Marco Cesarato .
Classes: 5 packages by
Country: Italy Italy
Age: 22
All time rank: 2997117 in Italy Italy
Week rank: 11 Up1 in Italy Italy Up
Innovation award
Innovation award
Nominee: 1x

Details

PHP Database Web API

![](cover.png)

Version: 0.4.65 beta

Github: https://github.com/marcocesarato/Database-Web-API

Author: Marco Cesarato

Description

Dynamically generate RESTful APIs from the contents of a database table. Provides JSON, XML, and HTML. Supports most popular databases.

What problem this solves

Creating an API to access information within existing database tables is laborious task, when done as a bespoke task. This is often dealt with by exporting the contents of the database as CSV files, and providing downloads of them as a ?good enough? solution.

How this solves it

Database Web API acts as a filter, sitting between a database and the browser, allowing users to interact with that database as if it was a native API. The column names function as the key names. This obviates the need for custom code for each database layer.

When Alternative PHP Cache (APC) is installed, parsed data is stored within APC, which accelerates its functionality substantially. While APC is not required, it is recommended highly.

Databases supported

  • 4D
  • CUBRID
  • Firebird/Interbase
  • IBM
  • Informix
  • MS SQL Server
  • MySQL
  • ODBC and DB2
  • Oracle
  • PostgreSQL
  • SQLite

Requirements

  • PHP
  • Database
  • APC (optional)

Installation

  • Set the configuration on config.php (Follow the below example to register a new dataset in config.php
  • If you want config an auth system you must compile on the config the constant \_\_AUTH\_\_ as on the example below
  • If you want enable the auth system rename .htaccess_auth to .htaccess
  • Document the API

Configuration

Edit config.php to include a single instance of the following for each dataset (including as many instances as you have datasets):

EXAMPLE with explanation

define("__API_NAME__", "Database Web API"); // API Name
define("__BASE_DIR__", ""); // Dir relative to the http root where is located

define("__AUTH__",  serialize(array( // Set null for disable authentication
	'database' => 'dataset',
	'users' => array(
		'table' => 'users', // Table where users are stored
		'columns' => array(
			'id' => 'user_id',
			'password' => 'password',
			'dmin' => array('is_admin' => 'on') // Admin bypass all black/whitelists. Set NULL for disable
		),
		'search' => array('user_id', 'email', 'username'), // Search user by these fields
		'check' => array('active' => 1) // Some validation checks. In this case if the column 'active' with value '1'. Set NULL for disable
	),
	'callbacks' => array( // Functions stored in includes/callbacks.php that you can customize. Set NULL for disable (readonly)
		'sql_restriction' => 'callback_sql_restriction',
		'can_read' => 'callback_can_read',
		'can_write' => 'callback_can_write',
		'can_edit' => 'callback_can_edit',
		'can_delete' => 'callback_can_delete',
	),
)));

define("__DATASETS__", serialize(array(
	'dataset' => array(
		'name' => 'database_name', // Database name
		'username' => 'user', // root is default
		'password' => 'passwd', // root is default
		'server' => 'localhost',  // localhost default
		'port' => 5432, // 3306 is default
		'type' => 'pgsql', // mysql is default
		'table_list' => array( // Tables's whitelist (Allow only the tables in this list, if empty allow all)
			'users'
		),
		'table_blacklist' => array( // Tables's blacklist
			'passwords'
		),
		'table_free' => array(), // Tables's with free access (no permissions needed)
		'table_readonly' => array(), // Tables with readonly permissions (usually when no permissions needed)
		'column_list' => array( // Columns's whitelist (Allow only the columns in this list, if empty allow all)
			'users' => array(
				'username',
				'name',
				'surname'
			)
		),
		'column_blacklist' => array( // Columns's blacklist
			'users' => array(
				'password',
			)
		),
	),
)));

_Note: All fields of \_\_DATASETS\_\_ (except the name of database) are optional and will default to the above._

Default dataset values:

array(
	'name' => null,
	'username' => 'root',
	'password' => 'root',
	'server' => 'localhost',
	'port' => 3306,
	'type' => 'mysql',
	'table_blacklist' => array(),
	'table_list' => array(),
	'column_blacklist' => array(),
	'column_list' => array(),
	'ttl' => 3600,
);

Callbacks

Callbacks availables (Prepared versions on includes/callbacks.php):

function callback_sql_restriction($table, $permission)
function callback_can_read($table)
function callback_can_write($table){
function callback_can_edit($table)
function callback_can_delete($table)

You can use this code fo have a database instance and the current user authenticated row:

$user = Auth::getUser(); // User row
$db = API::getDatabase('dataset'); // You can specify dataset. Return PDO Object

Note: All callbacks if return NULL will use default values with readonly permissions.

List

  • sql_restriction

    Description: Return a string to append in where condition

    Parameters: \$table, \$permission

    Options of $permission:

    `php case 'READ': case 'WRITE': case 'EDIT': case 'DELETE': ` Return `php // All denied $sql = "'1' = '0'"; // All allowed $sql = "'1' = '1'"; ` Examples: `php // Only Created $sql = 'created_by = '.$user['id']; // Only Team $sql = 'created_by IN ('.implode(',',$teams_ids).')'; `

  • can_read

    Description: Return if can GET/SELECT

    Parameters: \$table

    Return: Boolean

  • can_write

    Description: Return if can POST/INSERT

    Parameters: \$table

    Return: Boolean

  • can_edit

    Description: Return if can PUT/UPDATE

    Parameters: \$table

    Return: Boolean

  • can_delete
  • Description: Return if can DELETE

    Parameters: \$table

    Return: Boolean

Configuration

For implement the callbacks you need to add the callbacks array to the \_\_AUTH\_\_ constant:

'callbacks' => array( // Set NULL for disable (readonly)
	 'sql_restriction' => 'callback_sql_restriction',
	 'can_read' => 'callback_can_read',
	 'can_write' => 'callback_can_write',
	 'can_edit' => 'callback_can_edit',
	 'can_delete' => 'callback_can_delete',
 ),

API Structure

Format availables:

  • JSON
  • XML
  • HTML (for debug)

Generic URL format for all kind of request:

  • Fetch all: /[token]/[database]/[table].[format]
  • Fetch all with limit: /[token]/[database]/[limit]/[table].[format]
  • Fetch: /[token]/[database]/[table]/[ID].[format]
  • Fetch search by coolumn: /[token]/[database]/[table]/[column]/[value].[format]

Advanced search:

Note: These examples are valid only for GET and PUT requests

Search single value

where[column]              = 1    // column = 1
where[column][=]           = 1    // column = 1
where[column][!]           = 1    // column != 1
where[column][>]           = 1    // column > 1
where[column][<]           = 1    // column < 1
where[column][%]           = "%1" // column LIKE "%1"

Search multiple values

where[column]              = array(1,5,7)     // IN (...) (IN can be equal to an OR)
where[column][=]           = array(1,5,7)     // IN (...) 
where[column][!]           = array(1,5,7)     // NOT IN (...)
where[column][>]           = array(1,2)       // column > 1 AND column > 2
where[column][<]           = array(1,2)       // column < 1 AND column < 2
where[column][%]           = array("%1","%2") // column LIKE "%1" AND column LIKE "%2"

Specify column's table

where['table.column'][=] = array(1,5,7)

Compare between two different table columns

where['table_a.column_a'] = 'table_b.column_b'

Compare between different columns of main table

where['column_a'] = 'table_a.column_b'
// OR
where['table_a.column_a'] = 'table_a.column_b'
	
// WRONG
where['column_a'] = 'column_b'

Additional parameters

  • order_by: column_name

    Can be and array or a string

    `php order_by = 'username, name, surname' // OR order_by = array('username', 'name', 'surname') `

    for more specific order direction

    `php order_by['users.username'] = 'DESC' `

  • direction: ASC or DESC (default ASC)
  • limit: max elements to retrieve

ex: /[database]/[tabel]/[colomn]/[value].[format]?order_by=[column]&direction=[direction]

Authentication

Authentication needed for browse the database.

The authentication permit to managed the privilege of the users (read, write, modify, delete)

  • Authentication: /auth/[password]/[id].[format]

Request example:

GET /auth/password/1265.json HTTP/1.1
Host: localhost

Response example:

[{"token": "b279fb1d0708ed81e7a194e0c5d928b6"}]

Example of token usage on GET, POST, PUT and DELETE requests:

GET /bfee499dfa1387648ec8ce9d621db120/database/users.json` HTTP/1.1
Host: localhost

GET Request

Retrieve data from dataset

  • Fetch all: /[token]/[database]/[table].[format]
  • Fetch all with limit: /[token]/[database]/[limit]/[table].[format]
  • Fetch: /[token]/[database]/[table]/[ID].[format]
  • Fetch search by column: /[token]/[database]/[table]/[column]/[value].[format]
  • Fetch all joining table:

    `js join[table] = array( 'on' => <column_id>, // Column of the table joined 'value' => <value>, // Column of main table or value 'method' => (left|inner|right) // Optional ) `

    Example with value:

    `js join[users]['on'] = id join[users]['value'] = 1 join[users]['method'] = 'INNER' `

    Example with column:

    `js join[users]['on'] = id // Column of the table joined join[users]['value'] = user_id // Column of the main table (no users) join[users]['method'] = 'INNER' `

  • Additional parameters

ex: /[database]/[table]/[column]/[value].[format]?order_by=[column]&direction=[direction]

Examples of GET requests:

GET /dataset/users.json HTTP/1.1
Host: localhost

GET /dataset/10/users.json HTTP/1.1
Host: localhost

GET /dataset/users/1.json HTTP/1.1
Host: localhost

GET /dataset/users/is_active/1.json?order_by=username&direction=desc HTTP/1.1
Host: localhost

POST Request

Insert data

Single insert:

  • Select the table on URL: /[database]/[table].[format]
  • Insert parameter: insert[<column>] = <value>

Multiple insert:

  • Select dataset on URL: /[database].[format]
  • Insert parameter: insert[<table>][] = <value>

Multiple insert on the same table:

  • Select dataset on URL: /[database].[format]
  • Insert parameter: insert[<table>][<$i>][<column>] = <value>

Examples of POST requests:

Single insert:

POST /dataset/users.json HTTP/1.1
Host: localhost
insert[username]=Marco&insert[email]=cesarato.developer@gmail.com&insert[password]=3vwjehvdfjhefejjvw&insert[is_active]=1

Multiple insert:

POST /dataset.json HTTP/1.1
Host: localhost
insert[users][id]=1000&insert[users][username]=Marco&insert[users][email]=cesarato.developer@gmail.com&insert[users][password]=3vwjehvdfjhefejjvw&insert[users][is_active]=1&
insert[admin][user_id]=1000

Multiple insert on the same table:

POST /dataset.json HTTP/1.1
Host: localhost
insert[users][0][username]=Marco&insert[users][0][email]=cesarato.developer@gmail.com&insert[users][0][password]=3vwjehvdfjhefejjvw&insert[users][0][is_active]=1&insert[users][1][username]=Brad&insert[users][1][email]=brad@gmail.com&insert[users][1][password]=erwerwerffweeqewrf&insert[users][1][is_active]=1

PUT Request

Update data

Single update:

  • Select the row on URL: /[database]/[table]/[id].[format]
  • Update parameter: update[<column>] = <value>

Multiple update:

  • Select the dataset on URL: /[database].[format]
  • Update parameter: update[<table>][values][<column>] = <value>
  • Multiple update parameter conditions: update[<table>][where][<column>] = <value>

Note: At the moment you can update only one row for table

Examples of PUT Requests:

Single Update:

PUT /dataset/users/1.json HTTP/1.1
Host: localhost
update['username']=Marco&update['email']=cesarato.developer@gmail.com&update['password']=3vwjehvdfjhefejjvw&update['is_active']=1

Multiple Update:

PUT /dataset.json HTTP/1.1
Host: localhost
update[users][values][username]=Marco&update[users][values][email]=cesarato.developer@gmail.com&update[users][where][id]=1&update[cities][values][name]=Padova&update[cities][where][id]=1

DELETE Request

Delete data

  • Select the row on table: /[database]/[table]/[id].[format]

Examples of DELETE Requests:

DELETE /dataset/users/1.json HTTP/1.1
Host: localhost

API Client

PHP API Client

Filename: apiclient.class.php

Class name: APIClient

| Method | Params | Return | Description | | ------------- | ---------------------------------------------- | ------ | ---------------------------------------------- | | getInstance | - | Void | Returns static reference to the class instance | | fetch | \$table, \$format = 'json', \$params = array() | Object | Fetch data | | searchElement | \$key, \$value, \$array | Object | Search object in array | | filterBy | \$key, \$value, \$array, \$limit = null | Array | Filter results array by single key | | filter | \$value, \$array, $limit = null | Array | Filter results array by multiple values |

Usage

$api_client = APIClient::getInstance();

$api_client->DEBUG = true;
$api_client->URL = 'http://localhost';
$api_client->ACCESS_TOKEN = '4gw7j8erfgerf6werf8fwerf8erfwfer';
$api_client->DATASET = 'dataset';

$params = array(
	'where' => array(
		'type' => array('C', 'O', 'L'),
		'accounts_addresses.address' => array(
			'!' => '', // NOT NULL
		),
	),
	'join' => array(
		'accounts_addresses' => array(
			'on' => 'parent_id',
			'value' => 'id',
			'method' => 'LEFT'
		),
		'accounts_agents' => array(
			'on' => 'parent_id',
			'value' => 'id'
		),
	),
	'order_by' => array(
		'address' => array(
			'table' => 'accounts_addresses',
			'direction' => 'DESC'
		),
		'type' => array(
			'table' => 'accounts_addresses',
			'direction' => 'ASC'
		)
	),
);
$records = $api_client->fetch('accounts', 'json', $params);

Credits

https://github.com/project-open-data/db-to-api

  Files folder image Files  
File Role Description
Files folder imageclients (1 file)
Files folder imageincludes (4 files, 2 directories)
Accessible without login Plain text file .htaccess Data Auxiliary data
Accessible without login Plain text file .htaccess_auth Data Auxiliary data
Accessible without login Plain text file config.php Aux. Auxiliary script
Accessible without login Image file cover.png Data Auxiliary data
Accessible without login Image file cover.png Data Auxiliary data
Accessible without login Plain text file index.php Example Example script
Accessible without login Plain text file LICENSE Lic. License text
Accessible without login Plain text file README.md Doc. Documentation

  Files folder image Files  /  clients  
File Role Description
  Plain text file apiclient.class.php Class Class source

  Files folder image Files  /  includes  
File Role Description
Files folder imageclasses (4 files, 1 directory)
Files folder imagetemplate (2 files, 3 directories)
  Accessible without login Plain text file callbacks.php Example Example script
  Accessible without login Plain text file compatibility.php Aux. Auxiliary script
  Accessible without login Plain text file functions.php Example Example script
  Accessible without login Plain text file loader.php Example Example script

  Files folder image Files  /  includes  /  classes  
File Role Description
Files folder imagePDO (7 files)
  Plain text file api.class.php Class Class source
  Plain text file auth.class.php Class Class source
  Plain text file db_errorparser.class.php Class Class source
  Plain text file request.class.php Class Class source

  Files folder image Files  /  includes  /  classes  /  PDO  
File Role Description
  Plain text file PDO.class.php Class Class source
  Plain text file PDOStatement_mysql.class.php Class Class source
  Plain text file PDOStatement_pgsql.class.php Class Class source
  Plain text file PDOStatement_sqlite.class.php Class Class source
  Plain text file PDO_mysql.class.php Class Class source
  Plain text file PDO_pgsql.class.php Class Class source
  Plain text file PDO_sqlite.class.php Class Class source

  Files folder image Files  /  includes  /  template  
File Role Description
Files folder imagecss (7 files)
Files folder imageimg (2 files)
Files folder imagejs (1 file, 1 directory)
  Accessible without login Plain text file footer.php Aux. Auxiliary script
  Accessible without login Plain text file header.php Aux. Auxiliary script

  Files folder image Files  /  includes  /  template  /  css  
File Role Description
  Accessible without login Plain text file bootstrap-grid.css Data Auxiliary data
  Accessible without login Plain text file bootstrap-grid.min.css Data Auxiliary data
  Accessible without login Plain text file bootstrap-reboot.css Data Auxiliary data
  Accessible without login Plain text file bootstrap-reboot.min.css Data Auxiliary data
  Accessible without login Plain text file bootstrap.css Data Auxiliary data
  Accessible without login Plain text file bootstrap.min.css Data Auxiliary data
  Accessible without login Plain text file main.css Data Auxiliary data

  Files folder image Files  /  includes  /  template  /  img  
File Role Description
  Accessible without login Image file glyphicons-halflings-white.png Icon Icon image
  Accessible without login Image file glyphicons-halflings.png Icon Icon image

  Files folder image Files  /  includes  /  template  /  js  
File Role Description
Files folder imagevendor (6 files)
  Accessible without login Plain text file main.js Data Auxiliary data

  Files folder image Files  /  includes  /  template  /  js  /  vendor  
File Role Description
  Accessible without login Plain text file bootstrap.bundle.js Data Auxiliary data
  Accessible without login Plain text file bootstrap.bundle.min.js Data Auxiliary data
  Accessible without login Plain text file bootstrap.js Data Auxiliary data
  Accessible without login Plain text file bootstrap.min.js Data Auxiliary data
  Accessible without login Plain text file jquery-3.2.1.min.js Data Auxiliary data
  Accessible without login Plain text file modernizr-2.6.1-respond-1.1.0.min.js Data Auxiliary data

 Version Control Unique User Downloads Download Rankings  
 100%
Total:214
This week:48
All time:8,030
This week:7Up
User Comments (1)
Excellent thanks very much Marco.
5 months ago (Mike MacDonald)
80%StarStarStarStarStar