<commit_before>[![Average time to resolve an issue](http://isitmaintained.com/badge/resolution/mevdschee/php-crud-api.svg)](http://isitmaintained.com/project/mevdschee/php-crud-api "Average time to resolve an issue")
[![Percentage of issues still open](http://isitmaintained.com/badge/open/mevdschee/php-crud-api.svg)](http://isitmaintained.com/project/mevdschee/php-crud-api "Percentage of issues still open")

# PHP-CRUD-API

Single file PHP script that adds a REST API to a MySQL InnoDB database. PostgreSQL 9 and MS SQL Server 2012 are fully supported. There is even limited support for SQLite 3.

Related projects:

  - [PHP-API-AUTH](https://github.com/mevdschee/php-api-auth): Authentication add-on supporting JWT or username/password.
  - [PHP-SP-API](https://github.com/mevdschee/php-sp-api): Single file PHP script that adds a REST API to a SQL database.
  - [PHP-CRUD-UI](https://github.com/mevdschee/php-crud-ui): Single file PHP script that adds a UI to a PHP-CRUD-API project.

## Requirements

  - PHP 5.3 or higher with MySQLi, libpq, SQLSRV or sqlite3 enabled
  - PHP on Windows when connecting to SQL Server 2012
  - PostGIS for spatial features in PostgreSQL

## Installation

This is a single file application! Upload "api.php" somewhere and enjoy!

## Limitations

  - Primary keys should either be auto-increment (from 1) or UUID
  - Composite primary or foreign keys are not supported
  - Complex filters (with both "and" & "or") are not supported
  - Complex writes (transactions) are not supported
  - Complex queries calling functions (like "concat" or "sum") are not supported
  - Binary and spatial/GIS functionality is not supported in SQLite

## Features

  - Single PHP file, easy to deploy.
  - Very little code, easy to adapt and maintain
  - Streaming data, low memory footprint
  - Supports POST variables as input
  - Supports a JSON object as input
  - Supports a JSON array as input (batch insert)
  - Supports file upload from web forms (multipart/form-data)
  - Condensed JSON ouput: first row contains field names
  - Sanitize and validate input using callbacks
  - Permission system for databases, tables, columns and records
  - Multi-tenant database layouts are supported
  - Both JSONP and CORS support for cross-domain requests
  - Combined requests with support for multiple table names
  - Search support on multiple criteria
  - Pagination, sorting and column selection
  - Relation detection and filtering on foreign keys
  - Relation "transforms" for PHP and JavaScript
  - Binary fields supported with base64 encoding
  - Spatial/GIS fields and filters supported with WKT
  - Generate API documentation using Swagger tools
  - Authentication via JWT token or username/password (via [PHP-API-AUTH](https://github.com/mevdschee/php-api-auth))

## Configuration

Edit the following lines in the bottom of the file "api.php":

```
$api = new PHP_CRUD_API(array(
	'username'=>'xxx',
	'password'=>'<PASSWORD>',
	'database'=>'xxx',
));
$api->executeCommand();
```

These are all the configuration options and their default values:

```
$api = new PHP_CRUD_API(array(
	'dbengine'=>'MySQL',
	'username'=>'root',
	'password'=><PASSWORD>,
	'database'=>false,
// for connectivity (defaults to localhost):
	'hostname'=>null,
	'port'=>null,
	'socket'=>null,
	'charset'=>'utf8',
// callbacks with their default behavior
	'table_authorizer'=>function($cmd,$db,$tab) { return true; },
	'record_filter'=>function($cmd,$db,$tab) { return false; },
	'column_authorizer'=>function($cmd,$db,$tab,$col) { return true; },
	'tenancy_function'=>function($cmd,$db,$tab,$col) { return null; },
	'input_sanitizer'=>function($cmd,$db,$tab,$col,$typ,$val) { return $val; },
	'input_validator'=>function($cmd,$db,$tab,$col,$typ,$val,$ctx) { return true; },
// configurable options
	'auto_include'=>true,
    'extensions'=>true,
// dependencies (added for unit testing):
	'db'=>null,
	'method'=>$_SERVER['REQUEST_METHOD'],
	'request'=>$_SERVER['PATH_INFO'],
	'get'=>$_GET,
	'post'=>'php://input',
));
$api->executeCommand();
```

NB: The "socket" option is not supported by MS SQL Server. SQLite expects the filename in the "database" field.

## Documentation

After configuring you can directly benefit from generated API documentation. On the URL below you find the generated API specification in [Swagger](http://swagger.io/) 2.0 format.

    http://localhost/api.php

Try the [editor](http://editor.swagger.io/) to quickly view it! Choose "File" > "Paste JSON..." from the menu.

## Usage

You can do all CRUD (Create, Read, Update, Delete) operations and one extra List operation. Here is how:<commit_msg>bugfix<commit_after>[![Average time to resolve an issue](http://isitmaintained.com/badge/resolution/mevdschee/php-crud-api.svg)](http://isitmaintained.com/project/mevdschee/php-crud-api "Average time to resolve an issue")
[![Percentage of issues still open](http://isitmaintained.com/badge/open/mevdschee/php-crud-api.svg)](http://isitmaintained.com/project/mevdschee/php-crud-api "Percentage of issues still open")

# PHP-CRUD-API

Single file PHP script that adds a REST API to a MySQL InnoDB database. PostgreSQL 9 and MS SQL Server 2012 are fully supported. There is even limited support for SQLite 3.

Related projects:

  - [PHP-API-AUTH](https://github.com/mevdschee/php-api-auth): Authentication add-on supporting JWT or username/password.
  - [PHP-SP-API](https://github.com/mevdschee/php-sp-api): Single file PHP script that adds a REST API to a SQL database.
  - [PHP-CRUD-UI](https://github.com/mevdschee/php-crud-ui): Single file PHP script that adds a UI to a PHP-CRUD-API project.

## Requirements

  - PHP 5.3 or higher with MySQLi, libpq, SQLSRV or sqlite3 enabled
  - PHP on Windows when connecting to SQL Server 2012
  - PostGIS for spatial features in PostgreSQL

## Installation

This is a single file application! Upload "api.php" somewhere and enjoy!

## Limitations

  - Primary keys should either be auto-increment (from 1) or UUID
  - Composite primary or foreign keys are not supported
  - Complex filters (with both "and" & "or") are not supported
  - Complex writes (transactions) are not supported
  - Complex queries calling functions (like "concat" or "sum") are not supported
  - Binary and spatial/GIS functionality is not supported in SQLite

## Features

  - Single PHP file, easy to deploy.
  - Very little code, easy to adapt and maintain
  - Streaming data, low memory footprint
  - Supports POST variables as input
  - Supports a JSON object as input
  - Supports a JSON array as input (batch insert)
  - Supports file upload from web forms (multipart/form-data)
  - Condensed JSON ouput: first row contains field names
  - Sanitize and validate input using callbacks
  - Permission system for databases, tables, columns and records
  - Multi-tenant database layouts are supported
  - Both JSONP and CORS support for cross-domain requests
  - Combined requests with support for multiple table names
  - Search support on multiple criteria
  - Pagination, sorting and column selection
  - Relation detection and filtering on foreign keys
  - Relation "transforms" for PHP and JavaScript
  - Binary fields supported with base64 encoding
  - Spatial/GIS fields and filters supported with WKT
  - Generate API documentation using Swagger tools
  - Authentication via JWT token or username/password (via [PHP-API-AUTH](https://github.com/mevdschee/php-api-auth))

## Configuration

Edit the following lines in the bottom of the file "api.php":

```
$api = new PHP_CRUD_API(array(
	'username'=>'xxx',
	'password'=>'<PASSWORD>',
	'database'=>'xxx',
));
$api->executeCommand();
```

These are all the configuration options and their default values:

```
$api = new PHP_CRUD_API(array(
	'dbengine'=>'MySQL',
	'username'=>'root',
	'password'=><PASSWORD>,
	'database'=>false,
// for connectivity (defaults to localhost):
	'hostname'=>null,
	'port'=>null,
	'socket'=>null,
	'charset'=>'utf8',
// callbacks with their default behavior
	'table_authorizer'=>function($cmd,$db,$tab) { return true; },
	'record_filter'=>function($cmd,$db,$tab) { return false; },
	'column_authorizer'=>function($cmd,$db,$tab,$col) { return true; },
	'tenancy_function'=>function($cmd,$db,$tab,$col) { return null; },
	'input_sanitizer'=>function($cmd,$db,$tab,$col,$typ,$val) { return $val; },
	'input_validator'=>function($cmd,$db,$tab,$col,$typ,$val,$ctx) { return true; },
// configurable options
	'auto_include'=>true,
    'extensions'=>true,
// dependencies (added for unit testing):
	'db'=>null,
	'method'=>$_SERVER['REQUEST_METHOD'],
	'request'=>$_SERVER['PATH_INFO'],
	'get'=>$_GET,
	'post'=>file_get_contents('php://input'),
));
$api->executeCommand();
```

NB: The "socket" option is not supported by MS SQL Server. SQLite expects the filename in the "database" field.

## Documentation

After configuring you can directly benefit from generated API documentation. On the URL below you find the generated API specification in [Swagger](http://swagger.io/) 2.0 format.

    http://localhost/api.php

Try the [editor](http://editor.swagger.io/) to quickly view it! Choose "File" > "Paste JSON..." from the menu.

## Usage

You can do all CRUD (Create, Read, Update, Delete) operations and one extra List operation. Here is how:
{'fraction_non_alphanumeric': 0.1238403451995685, 'fraction_numerical': 0.004099244875943905, 'mean_word_length': 4.548174745661281, 'pattern_counts': {'":': 2, '<': 7, '<?xml version=': 0, '>': 61, 'https://': 8, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3031231', 'n_tokens_mistral': 2842, 'n_tokens_neox': 2740, 'n_words': 967}