README

MySql Crud Api is a Plug and Play PHP Library created to Auto-magically build CRUD capabilities for your MySql Database using RESTful API calls. This library is dependent to Mysql Crud Library which is another Plug and Play PHP Library similar to Query Builder and Eloquent ORM but on Steroids.

Easy Peasy Summary

C-reate  POST http://your-website.com/{route}/{database-table-name}
R-ead    GET http://your-website.com/{route}/{database-table-name}/{id}/{related-table-name}/{id}?{parameter}={value}
U-pdate  PUT|PATCH http://your-website.com/{route}/{database-table-name}/{id}
D-elete  DELETE http://your-website.com/{route}/{database-table-name}/{id}

Description	HTTP Verb	URL
All Records	GET	http://your-website.com/crud/products
Record ID	GET	http://your-website.com/crud/products/1
Filtered Records	GET	http://your-website.com/crud/products?status=active
Search Records	GET	http://your-website.com/crud/products?search=iphone
Pagination	GET	http://your-website.com/crud/products?limit=100&offset=100
Sort and Order	GET	http://your-website.com/crud/products?sort=price&order=asc
Child Records	GET	http://your-website.com/crud/categories/1/products
Related Records	GET	http://your-website.com/crud/categories?with=products
Add Record/s	POST	http://your-website.com/crud/products
Add Related Records	POST	http://your-website.com/crud/categories?with=product
Update a Record	PUT or PATCH	http://your-website.com/crud/products/1
Update Records	PUT or PATCH	http://your-website.com/crud/products
Delete a Record	DELETE	http://your-website.com/crud/products/1
Delete Records	DELETE	http://your-website.com/crud/products

What is MySql CRUD API
Easy Peasy Summary
Installation
Retrieving Records
Parameters
- API Key
- Limit and Offset
- Sort and Order
- Envelop
- Case
- Format
Creating Records
- Create Single Record
- Create Multiple Records
Updating Records
- Update Single Record
- Update Multiple Records
Deleting Records
- Delete Multiple Records
Configuration File

Installation

To install this project, you have two options:

Install via Composer

To install using Composer, run the following command:

composer require mg3lo/mysql-crud-api

Manual Installation

To install manually, follow these steps:

Download the library from developer tools.
Unzip the downloaded file to your extensions directory.

System requirements

PHP 7.0 or higher
MySQL 5.0 or higher

Note: Tested on the following versions but might work on older versions as well.

PHP Users

Download the sample installation or Install via composer
```
composer require mg3lo/mysql-crud-api
```

Load the library on php file

<?php
// Load library installed via composer
require_once './vendor/autoload.php'; 

// Or load library installed manually
require_once './Mg3lo/vendor/autoload.php'; 

use Mg3lo\MySqlCrudApi;

Let the library handle all requests

<?php

require_once './vendor/autoload.php'; 

use Mg3lo\MySqlCrudApi;

// connect to your database
$api = new MySqlCrudApi([
  'username' => 'root',
  'password' => '',
  'database' => 'my_database'
]);

// manage all calls
$api->manage();

Enjoy!

Codeigniter Users

Unzip the sample library for Codeigniter 3 or Codeigniter 4 or Install via composer
```
composer require mg3lo/mysql-crud-api
```

Create routes to catch all requests going to your crud url e.g. http://your-website/crud/{anything-goes}

$route['crud/(:any)'] = 'crud';
$route['crud/(:any)/(:any)'] = 'crud';
$route['crud/(:any)/(:any)/(:any)'] = 'crud';
$route['crud/(:any)/(:any)/(:any)/(:any)'] = 'crud';

Load the library on your controller

<?php 
  // Load library installed via composer
  require_once FCPATH . 'vendor/autoload.php'; 

  // Or load library installed manually
  require_once APPPATH . 'third_party/Mg3lo/vendor/autoload.php';
	
  use Mg3lo\MySqlCrudApi;

Let the library handle all requests

<?php 
  require_once APPPATH . 'third_party/Mg3lo/vendor/autoload.php';
	
  use Mg3lo\MySqlCrudApi;
	
  class Crud extends CI_Controller {

	public function index()
	{
	  // connect to your mysql database
	  $api = new MySqlCrudApi([
		'username' => 'root',
		'password' => '',
		'database' => 'my_database'
	  ]);
	  
	  // let the library manage all api calls
	  $api->manage(); 
	}
  }

Enjoy!

Laravel Users

Install via composer or unzip the library according to folder structure
```
composer require mg3lo/mysql-crud-api
```

Create routes to catch all requests going to your crud url e.g. http://your-website/crud/{anything-goes}

Route::any('{crud}', function ($any) {
  // Catches all routes from crud, may vary depending on your laravel version
})->where('crud', '.*crud.*');

Load the library on your route or controller

// load the library if you did not install it via composer
require_once app_path('Mg3lo/vendor/autoload.php');
use Mg3lo\MySqlCrudApi;

Let the library handle all requests

use Mg3lo\MySqlCrudApi;

// connect to your mysql database
$api = new MySqlCrudApi([
  'host' => env(DB_HOST),
  'username' => env(DB_USERNAME),
  'password' => env(DB_PASSWORD),
  'database' => env(DB_DATABASE),
]);

Route::any('{crud}', function ($any) use ($api) {

  // let the library manage all api calls
  $api->manage(); 
  
})->where('crud', '.*crud.*');

Enjoy!

Other PHP Frameworks

Download the sample installation or Install via composer
```
composer require mg3lo/mysql-crud-api
```

Load the library

<?php
// Load library installed via composer
require_once './vendor/autoload.php'; 

// Or load library installed manually
require_once '.Your_Directory/Mg3lo/vendor/autoload.php'; 

use Mg3lo\MySqlCrudApi;

Let the library handle all requests

<?php

require_once './vendor/autoload.php'; 

use Mg3lo\MySqlCrudApi;

// connect to your database
$api = new MySqlCrudApi([
  'username' => 'root',
  'password' => '',
  'database' => 'my_database'
]);

// manage all calls
$api->manage();

Enjoy!

Retrieving Records

Retrieving records generally follow this URL format.

GET http://your-website.com/{route}/{database-table-name}/{id}/{related-table-name}/{id}?{parameter}={value}

All Records

Retrieves all records from products table

http://your-website.com/crud/products

Single Record

Retrieves the record with the id of 1 from products table

http://your-website.com/crud/products/1

Filtered Records

Retrieves products that are currently active

http://your-website.com/crud/products?status=active

Child Records

Retrieves products that belong to category 1

http://your-website.com/crud/categories/1/products

Related Records

Retrieves categories and their respective products

http://your-website.com/crud/categories?with=products

Searching Records

We can search records and search specific columns

http://your-website.com/crud/products?search=iphone
http://your-website.com/crud/products?search=iphone&search-fields=name,description
http://your-website.com/crud/products?search=iphone&search-fields[0]=name&search-fields[1]=description

Parameters

Using query parameters can further make our filters better.

API Key

Use API keys to protect your data.

http://your-website.com/crud/products?api-key=YourApiKey

Limit and Offset

Use limit and offset to limit the records retrieved and create paginations

Limit

Retrieves the first 100 records

http://your-website.com/crud/products?limit=100

Offset

Retrieves records 101 to 200

http://your-website.com/crud/products?limit=100&offset=100

Sort and Order

Sort and order records according to your liking

Sort

Sort records from a databse column name

http://your-website.com/crud/products?sort=price

Order

Order records ascending or descending

http://your-website.com/crud/products?sort=price&order=asc

Envelop

By default records are returned with an envelope you can turn it off by passing true or false

http://your-website.com/crud/products?envelop=false

Case

By default records are returned using underscore case. You can change them by passing any of the ff: (underscore, pascal, camel, dash)

http://your-website.com/crud/products?case=camel

Format

By default the library returns JSON as response, you can change it to (csv, xml, json or jsonp)

http://your-website.com/crud/products?format=xml

Creating Records

Creating records generally follow this URL format.

POST http://your-website.com/{route}/{database-table-name}

Create Single Record

We can add records by posting data to the url

http://your-website.com/crud/products

POST Data

[
  'name' => 'iPhone',
  'description' => 'Cellphone',
  'price' => '999',
]

Create Multiple Records

We can also add multiple records from the same url

http://your-website.com/crud/products

POST Data

[
  [
    'name' => 'iPhone',
    'description' => 'Cellphone',
    'price' => '999',
  ],
  [
    'name' => 'iPhone',
    'description' => 'Cellphone',
    'price' => '999',
  ]
]

Updating Records

Updating records generally follow this URL format.

PUT|PATCH http://your-website.com/{route}/{database-table-name}/{id}

Update Single Record

We can update a record from the ID url

PUT|PATCH http://your-website.com/crud/products/1

PUT or PATCH Data

[
  'name' => 'iPhone',
  'description' => 'New Description',
  'price' => '799',
]

Update Multiple Records

We can update multiple records

PUT|PATCH http://your-website.com/crud/products

PUT or PATCH Data

[
  [
	'id' => 1,
    'name' => 'iPhone',
    'description' => 'Cellphone',
    'price' => '999',
  ],
  [
	'id' => 2,
    'name' => 'iPhone',
    'description' => 'Cellphone',
    'price' => '999',
  ]
]

Note: We can also use POST for Updating records just pass a method override on your post _method

POST http://your-website.com/crud/products/1

POST Data

[
  'name' => 'iPhone',
  'description' => 'New Description',
  'price' => '799',
  _method => "put" // methhod ovverride
]

Deleting Records

Updating records generally follow this URL format.

DELETE http://your-website.com/{route}/{database-table-name}/{id}

Delete Multiple Records

To delete multiple files we pass their ids

DELETE http://your-website.com/{route}/{database-table-name}/{id}

DELETE Data

[
	ids = [1,2,3]
]

Configuration File

Your config file is located at Mg3lo\src\config\MySqlCrudApi.php make sure all the configurations are correct.

<?php

/*
|--------------------------------------------------------------------------
| Timezone
|--------------------------------------------------------------------------
|
| PHP 5 and lower requires timezone to be set
|
*/
$config['timezone'] = 'UTC';

/*
|--------------------------------------------------------------------------
| API Secret Key
|--------------------------------------------------------------------------
|
| Although you can set an api key to secure your API
| We suggest implementing your own security on top of it
|
*/
$config['api_key'] = '';

/*
|--------------------------------------------------------------------------
| URL Segment
|--------------------------------------------------------------------------
|
| In which url segment do tables be retrieved
| http://yourwebsite/api/table
|
| 0 = yourwebsite
| 1 = api
| 2 = table
|
*/
$config['url_segment'] = 2;

/*
|--------------------------------------------------------------------------
| API Default Case
|--------------------------------------------------------------------------
|
| Default API response will be of case underscore or snake.
| Values can be camel, dash, pascal, snake or underscore
|
*/
$config['case'] = 'underscore';

/*
|--------------------------------------------------------------------------
| Database tables
|--------------------------------------------------------------------------
|
| Database table names are named plural or singular
|
*/
$config['db_table_names'] = 'plural';

/*
|--------------------------------------------------------------------------
| API Envelop
|--------------------------------------------------------------------------
|
| If set to true API's response will contain status, code, message, date and count
| Otherwise if set to false response will contain pure data only
|
*/
$config['envelop'] = TRUE;

/*
|--------------------------------------------------------------------------
| API Wrapper
|--------------------------------------------------------------------------
|
| If supplied data will be wrapped around the value
|
*/
$config['wrapper'] = '';

/*
|--------------------------------------------------------------------------
| API Default Delimeter
|--------------------------------------------------------------------------
|
| Default delimiter for csv response
|
*/
$config['delimiter'] = ',';

/*
|--------------------------------------------------------------------------
| API Force Array
|--------------------------------------------------------------------------
|
| Forces the response to be array for single records
|
*/
$config['force_array'] = FALSE;

/*
|--------------------------------------------------------------------------
| API Default Response Format
|--------------------------------------------------------------------------
|
| API's default response format
|
*/
$config['format'] = 'json';

/*
|--------------------------------------------------------------------------
| Uploads Directory
|--------------------------------------------------------------------------
|
| Uploads root directory
|
*/
$config['uploads'] = 'uploads';

/*
|--------------------------------------------------------------------------
| Uploads folder name
|--------------------------------------------------------------------------
|
| uploads/directory
| sub folder for api uploads
|
*/
$config['api_uploads'] = 'mysqlcrudapi';

/*
|--------------------------------------------------------------------------
| Allowed file upload types
|--------------------------------------------------------------------------
|
| To enable all use *
| For specific types use | to separate values e.g. jpg|doc|txt
|
*/
$config['allowed_files'] = '*';


/*
|--------------------------------------------------------------------------
| Language File
|--------------------------------------------------------------------------
|
| Overwrite texts here 
| or pass it as second parameter on the constructor
| $api = new MysqlCrudApi($config, $language);
|
*/
$config['language'] = [
   'success' => 'Success',
   'failed' => 'Failed',
   'error' => 'error',
   
   'status' => 'status',
   'code' => 'code',
   'message' => 'message',
   'fetch_date' => 'fetch_date',
   'errors' => 'errors',
   'response' => 'Response',
   
   'add_successful' => 'Add successful',
   'edit_successful' => 'Edit successful',
   'delete_successful' => 'Delete successful',
   
   'count_' => 'count_',
   'total_' => 'total_',
   
   'file_too_large' => 'File too large',
   'invalid_api_key' => 'Invalid api key',
   'not_found' => 'Not found',
   'invalid_parameters' => 'Invalid parameters',
   'unsupported_file' => 'Unsupported file type',

   'no_update_parameter' => 'Please send at least one parameter to update',
   'no_delete_parameter' => 'No parameter for delete',
   'unable_csv' => 'Unable to convert nested multi-dimensional array to csv'
];

return $config;

?>

mg3lo / mysql-crud-api

Maintainers

Details

README

Easy Peasy Summary

Table of Contents

Installation

Install via Composer

Manual Installation

System requirements

PHP Users

Codeigniter Users

Laravel Users

Other PHP Frameworks

Retrieving Records

All Records

Single Record

Filtered Records

Child Records

Related Records

Searching Records

Parameters

API Key

Limit and Offset

Limit

Offset

Sort and Order

Sort

Order

Envelop

Case

Format

Creating Records

Create Single Record

Create Multiple Records

Updating Records

Update Single Record

Update Multiple Records

Deleting Records

Delete Multiple Records

Configuration File