CRUD

You can do all CRUD (Create, Read, Update, Delete) operations and one extra List operation. Here is how:


List

List all records of a database table.

```		
GET http://localhost/api.php/categories		
```

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


List + Transform

List all records of a database table and transform them to objects.

```
GET http://localhost/api.php/categories?transform=1
```

Output:
```
{"categories":[{"id":"1","name":"Internet"},{"id":"3","name":"Web development"}]}
```
NB: This transform is CPU and memory intensive and can also be executed client-side (see: [lib](https://github.com/mevdschee/php-crud-api/tree/master/lib)).

List + Filter

Search is implemented with the "filter" parameter. You need to specify the column name, a comma, the match type, another commma and the value you want to filter on. These are supported match types:

```
GET http://localhost/api.php/categories?filter=name,eq,Internet
GET http://localhost/api.php/categories?filter=name,sw,Inter
GET http://localhost/api.php/categories?filter=id,le,1
GET http://localhost/api.php/categories?filter=id,lt,2
```

Output:
```
{"categories":{"columns":["id","name"],"records":[["1","Internet"]]}}
```


List + Filter + Satisfy

Multiple filters can be applied by using "filter[]" instead of "filter" as a parameter name. Then the parameter "satisfy" is used to indicate whether "all" (default) or "any" filter should be satisfied to lead to a match:

```
GET http://localhost/api.php/categories?filter[]=id,eq,1&filter[]=id,eq,3&satisfy=any
GET http://localhost/api.php/categories?filter[]=id,ge,1&filter[]=id,le,3&satisfy=all
GET http://localhost/api.php/categories?filter[]=id,ge,1&filter[]=id,le,3
```

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


List + Column selection

By default all columns are selected. With the "columns" parameter you can select specific columns. Multiple columns should be comma separated. An asterisk ("*") may be used as a wildcard to indicate "all columns":

```
GET http://localhost/api.php/categories?columns=name
GET http://localhost/api.php/categories?columns=categories.name
```

Output:
```
{"categories":{"columns":["name"],"records":[["Web development"],["Internet"]]}}
```
NB: Columns that are used to include related entities are automatically added and cannot be left out of the output.

List + Order

With the "order" parameter you can sort. By default the sort is in ascending order, but by specifying "desc" this can be reversed:

```
GET http://localhost/api.php/categories?order=name,desc
```

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


List + Order + Pagination

The "page" parameter holds the requested page. The default page size is 20, but can be adjusted (e.g. to 50):

```
GET http://localhost/api.php/categories?order=id&page=1
GET http://localhost/api.php/categories?order=id&page=1,50
```

Output:
```
{"categories":{"columns":["id","name"],"records":[["1","Internet"],["3","Web development"]],"results":2}}
```
NB: Pages that are not ordered cannot be paginated.

Create

You can easily add a record using the POST method (x-www-form-urlencoded, see rfc1738). The call returns the "last insert id".

```
POST http://localhost/api.php/categories
id=1&name=Internet
```

Output:
```
1
```


Read

If you want to read a single object you can use:

```
GET http://localhost/api.php/categories/1
```

Output:
```
{"id":"1","name":"Internet"}
```


Create (with JSON)

Alternatively you can send a JSON object in the body. The call returns the "last insert id".

```
POST http://localhost/api.php/categories
{"id":"1","name":"Internet"}
```

Output:
```
1
```
Note that the fields that are not specified in the request get the default value as specified in the database.

Update

Editing a record is done with the PUT method. The call returns the rows affected.

```
PUT http://localhost/api.php/categories/2
id=1&name=Internet+networking
```

Output:
```
1
```


Update (with JSON)

Alternatively you can send a JSON object in the body. The call returns the rows affected.

```
PUT http://localhost/api.php/categories/2
{"id":"1","name":"Internet networking"}
```

Output:
```
1
```
Note that only fields that are specified in the request will be updated.

Delete

The DELETE verb is used to delete a record. The call returns the rows affected.

```
DELETE http://localhost/api.php/categories/2
```

Output:
```
1
```


Relations

The explanation of this feature is based on the data structure from the ```blog.sql``` database file. This database is a very simple blog data structure with corresponding foreign key relations between the tables. These foreign key constraints are required as the relationship detection is based on them, not on column naming.

Pay attention, that if you may use relations on InnoDB database only!

You can get the "post" that has "id" equal to "1" with it's corresponding "categories", "tags" and "comments" using:

```
GET http://localhost/api.php/posts?include=categories,tags,comments&filter=id,eq,1
```

Output:
```
{
    "posts": {
        "columns": [
            "id",
            "user_id",
            "category_id",
            "content"
        ],
        "records": [
            [
                "1",
                "1",
                "1",
                "blog started"
            ]
        ]
    },
    "post_tags": {
        "relations": {
            "post_id": "posts.id"
        },
        "columns": [
            "id",
            "post_id",
            "tag_id"
        ],
        "records": [
            [
                "1",
                "1",
                "1"
            ],
            [
                "2",
                "1",
                "2"
            ]
        ]
    },
    "categories": {
        "relations": {
            "id": "posts.category_id"
        },
        "columns": [
            "id",
            "name"
        ],
        "records": [
            [
                "1",
                "anouncement"
            ]
        ]
    },
    "tags": {
        "relations": {
            "id": "post_tags.tag_id"
        },
        "columns": [
            "id",
            "name"
        ],
        "records": [
            [
                "1",
                "funny"
            ],
            [
                "2",
                "important"
            ]
        ]
    },
    "comments": {
        "relations": {
            "post_id": "posts.id"
        },
        "columns": [
            "id",
            "post_id",
            "message"
        ],
        "records": [
            [
                "1",
                "1",
                "great"
            ],
            [
                "2",
                "1",
                "fantastic"
            ]
        ]
    }
}
```

You can call the ```php_crud_api_tranform()``` function to structure the data hierarchical like this:
```
{
    "posts": [
        {
            "id": "1",
            "post_tags": [
                {
                    "id": "1",
                    "post_id": "1",
                    "tag_id": "1",
                    "tags": [
                        {
                            "id": "1",
                            "name": "funny"
                        }
                    ]
                },
                {
                    "id": "2",
                    "post_id": "1",
                    "tag_id": "2",
                    "tags": [
                        {
                            "id": "2",
                            "name": "important"
                        }
                    ]
                }
            ],
            "comments": [
                {
                    "id": "1",
                    "post_id": "1",
                    "message": "great"
                },
                {
                    "id": "2",
                    "post_id": "1",
                    "message": "fantastic"
                }
            ],
            "user_id": "1",
            "category_id": "1",
            "categories": [
                {
                    "id": "1",
                    "name": "anouncement"
                }
            ],
            "content": "blog started"
        }
    ]
}
```
This transform function is available for PHP and JavaScript in the files ```php_crud_api_tranform.php``` and ```php_crud_api_tranform.js``` in the "lib" folder.

Permissions

By default access permissions are specified for limited number of tables in modules/additional/rest_api/api.class.php. If you want to change this list go to and change this class property: $available_tables = array(...);

protected $available_tables = array(
	'customers'              => array('create' => false, 'read' => true,  'update' => true,  'delete' => true),
	'hotels'                 => array('create' => false, 'read' => true,  'update' => true,  'delete' => false),
	'hotels_description'     => array('create' => false, 'read' => true,  'update' => true,  'delete' => false),
	'rooms'                  => array('create' => true,  'read' => true,  'update' => true,  'delete' => true),
	'rooms_description'      => array('create' => true,  'read' => true,  'update' => true,  'delete' => true),
	'rooms_availabilities'   => array('create' => false, 'read' => true,  'update' => false, 'delete' => true),
	'rooms_prices'           => array('create' => false, 'read' => true,  'update' => false, 'delete' => true),
	'bookings'               => array('create' => false, 'read' => true,  'update' => true,  'delete' => false),
	'bookings_rooms'         => array('create' => false, 'read' => true,  'update' => false, 'delete' => false),
);	


Access for tables

To open (edit) access to the table you need to perform one or two actions.

  1. Add the necessary rights to read, add, edit or delete in file modules/additional/rest_api/api.class.php
    Admin:

    protected $available_tables_admin = array(
    	...
    	[tabel_name] => array('create' => false|true, 'read' => false|true,  'update' => false|true, 'delete' => false|true),
    );	
    
    Hotel Owner:
    protected $available_tables = array(
    	...
    	[tabel_name] => array('create' => false|true, 'read' => false|true,  'update' => false|true, 'delete' => false|true),
    );	
    
    Note: If you only need a limit on the committed actions, further changes do not have to.


  2. If you need to make additional checks or filtering, then do the following
    a.) Create new API class in include/classes/api/[TableName]Api.class.php (See example: include/classes/api/ExampleApi.class.php) and make the desired changes in the classroom
    b.) Add new line in file include/connection.php
    $api_classes = array(
    	...
    	'[TableName]Api',
    );
    


Sanitizing input

By default all input is accepted and sent to the database. If you want to strip (certain) HTML tags before storing you may specify a input_sanitizer function that returns the adjusted value.



Validating input

By default all input is accepted. If you want to validate the input, you may specify a input_validator function that returns a boolean indicating whether or not the value is valid.



Multi-Database

The code also supports multi-database API's. These have URLs where the first segment in the path is the database and not the table name. This can be enabled by NOT specifying a database in the configuration. Also the permissions in the configuration should contain a dot character to seperate the database from the table name. The databases 'mysql', 'information_schema' and 'sys' are automatically blocked.



Binary data

Binary fields are automatically detected and data in those fields is returned using base64 encoding.

```
GET http://localhost/api.php/categories/2
```

Output:

```
{"id":"2","name":"funny","icon":"ZGF0YQ=="}
```

When sending a record that contains a binary field you will also have to send base64 encoded data.
```
PUT http://localhost/api.php/categories/2
icon=ZGF0YQ
```
In the above example you see how binary data is sent. Both "base64url" and standard "base64" are allowed (see rfc4648).

Sending NULL

When using the POST method (x-www-form-urlencoded, see rfc1738) a database NULL value can be set using a parameter with the __is_null suffix:

```
PUT http://localhost/api.php/categories/2
name=Internet&icon__is_null
```

When sending JSON data, then sending a NULL value for a nullable database field is easier as you can use the JSON "null" value (without quotes).

```
PUT http://localhost/api.php/categories/2
{"name":"Internet","icon":null}
```


Errors

The following types of 404 'Not found' errors may be reported: