280 lines
7.3 KiB
Markdown
280 lines
7.3 KiB
Markdown
# Datatables library for PHP
|
|
[](https://packagist.org/packages/ozdemir/datatables) [](https://github.com/n1crack/datatables/actions/workflows/tests.yml) [](https://github.com/n1crack/datatables/blob/master/LICENCE)
|
|
|
|
PHP Library to handle server-side processing for Datatables, in a fast and simple way. [Live Demo](https://datatables.ozdemir.be/)
|
|
|
|
## Features
|
|
* Easy to use. Generates json using only a few lines of code.
|
|
* Editable columns with a closure function.
|
|
* Supports custom filters.
|
|
* Can handle most complicated queries.
|
|
* Supports mysql and sqlite for native php.
|
|
* Works with :
|
|
- [Laravel](https://datatables.ozdemir.be/laravel)
|
|
- [CodeIgniter 3](https://datatables.ozdemir.be/codeigniter)
|
|
- [CodeIgniter 4](https://datatables.ozdemir.be/codeigniter4)
|
|
- [Phalcon 3+](https://datatables.ozdemir.be/phalcon)
|
|
- [Prestashop](https://datatables.ozdemir.be/prestashop)
|
|
- [PostgreSql](https://datatables.ozdemir.be/postgresql)
|
|
|
|
## Installation
|
|
|
|
> **NOTE:** version 2.0+ requires php 7.1.3+ ([php supported versions](http://php.net/supported-versions.php))
|
|
|
|
The recommended way to install the library is with [Composer](https://getcomposer.org/)
|
|
|
|
If you haven't started using composer, I highly recommend you to use it.
|
|
|
|
Put a file named `composer.json` at the root of your project, containing this information:
|
|
|
|
{
|
|
"require": {
|
|
"ozdemir/datatables": "2.*"
|
|
}
|
|
}
|
|
|
|
And then run:
|
|
|
|
```
|
|
composer install
|
|
```
|
|
|
|
Or just run :
|
|
|
|
```
|
|
composer require ozdemir/datatables
|
|
```
|
|
|
|
Add the autoloader to your project:
|
|
|
|
```php
|
|
<?php
|
|
|
|
require_once 'vendor/autoload.php';
|
|
```
|
|
|
|
You're now ready to begin using the Datatables php library.
|
|
|
|
```php
|
|
<?php
|
|
require_once 'vendor/autoload.php';
|
|
|
|
use Ozdemir\Datatables\Datatables;
|
|
use Ozdemir\Datatables\DB\MySQL;
|
|
|
|
$config = [ 'host' => 'localhost',
|
|
'port' => '3306',
|
|
'username' => 'homestead',
|
|
'password' => 'secret',
|
|
'database' => 'sakila' ];
|
|
|
|
$dt = new Datatables( new MySQL($config) );
|
|
|
|
$dt->query('Select film_id, title, description from film');
|
|
|
|
echo $dt->generate();
|
|
```
|
|
If you are using a php framework such as codeigniter or laravel, you can use the relevant database adapter.
|
|
|
|
```php
|
|
// Codeigniter 4 Example
|
|
|
|
<?php
|
|
|
|
namespace App\Controllers;
|
|
|
|
use Config\Database;
|
|
use Ozdemir\Datatables\Datatables;
|
|
use Ozdemir\Datatables\DB\Codeigniter4Adapter;
|
|
|
|
class Home extends BaseController
|
|
{
|
|
public function index()
|
|
{
|
|
return view('index');
|
|
}
|
|
|
|
public function ajax()
|
|
{
|
|
// CI 4 builder class
|
|
$db = Database::connect();
|
|
|
|
$builder = $db->table('Track');
|
|
$builder->select('TrackId, Name, UnitPrice');
|
|
|
|
// Datatables Php Library
|
|
$datatables = new Datatables(new Codeigniter4Adapter);
|
|
|
|
// using CI4 Builder
|
|
$datatables->query($builder);
|
|
|
|
// alternatively plain sql
|
|
// $datatables->query('Select TrackId, Name, UnitPrice from Track');
|
|
|
|
return $this->response->setJSON($datatables->generate()->toJson());
|
|
}
|
|
}
|
|
```
|
|
|
|
```php
|
|
// Laravel Example
|
|
|
|
<?php
|
|
// routes/web.php
|
|
|
|
use Ozdemir\Datatables\Datatables;
|
|
use Ozdemir\Datatables\DB\LaravelAdapter;
|
|
|
|
Route::get('/ajax/laravel', function () {
|
|
|
|
$sqlBuilder = Track::select([
|
|
'TrackId',
|
|
'Track.Name',
|
|
'Title as Album',
|
|
'MediaType.Name as MediaType',
|
|
'UnitPrice',
|
|
'Milliseconds',
|
|
'Bytes',
|
|
])
|
|
->join('Album', 'Album.AlbumId', 'Track.AlbumId')
|
|
->join('MediaType', 'MediaType.MediaTypeId', 'Track.MediaTypeId');
|
|
|
|
$dt = new Datatables(new LaravelAdapter);
|
|
$dt->query($sqlBuilder); // same as the previous example, sql statement can be used.
|
|
|
|
return $dt->generate();
|
|
});
|
|
|
|
```
|
|
|
|
## Methods
|
|
This is the list of available public methods.
|
|
|
|
__query($query)__ *required*
|
|
|
|
* sets the sql query
|
|
|
|
__generate()__ *required*
|
|
|
|
* runs the queries and build outputs
|
|
* returns the output as json
|
|
* same as generate()->toJson()
|
|
|
|
__toJson()__
|
|
|
|
* returns the output as json
|
|
* should be called after generate()
|
|
|
|
__toArray()__
|
|
|
|
* returns the output as array
|
|
* should be called after generate()
|
|
|
|
__add($column, function( $row ){})__
|
|
|
|
* adds extra columns for custom usage
|
|
|
|
__edit($column, function($row){})__
|
|
|
|
* allows column editing
|
|
|
|
__filter($column, function(){})__
|
|
|
|
* allows custom filtering
|
|
* it has the methods below
|
|
- escape($value)
|
|
- searchValue()
|
|
- defaultFilter()
|
|
- between($low, $high)
|
|
- whereIn($array)
|
|
- greaterThan($value)
|
|
- lessThan($value)
|
|
|
|
__hide($columns)__
|
|
|
|
* removes the column from output
|
|
* It is useful when you only need to use the data in add() or edit() methods.
|
|
|
|
__setDistinctResponseFrom($column)__
|
|
|
|
* executes the query with the given column name and adds the returned data to the output with the distinctData key.
|
|
|
|
__setDistinctResponse($output)__
|
|
|
|
* adds the given data to the output with the distinctData key.
|
|
|
|
__getColumns()__
|
|
|
|
* returns column names (for dev purpose)
|
|
|
|
__getQuery()__
|
|
|
|
* returns the sql query string that is created by the library (for dev purpose)
|
|
|
|
|
|
## Example
|
|
|
|
```php
|
|
<?php
|
|
require_once 'vendor/autoload.php';
|
|
|
|
use Ozdemir\Datatables\Datatables;
|
|
use Ozdemir\Datatables\DB\SQLite;
|
|
|
|
$path = __DIR__ . '/../path/to/database.db';
|
|
$dt = new Datatables( new SQLite($path) );
|
|
|
|
$dt->query('Select id, name, email, age, address, plevel from users');
|
|
|
|
$dt->edit('id', function($data){
|
|
// return a link.
|
|
return "<a href='user.php?id=" . $data['id'] . "'>edit</a>";
|
|
});
|
|
|
|
$dt->edit('email', function($data){
|
|
// masks email : mail@mail.com => m***@mail.com
|
|
return preg_replace('/(?<=.).(?=.*@)/u','*', $data['email']);
|
|
});
|
|
|
|
$dt->edit('address', function($data){
|
|
// checks user access.
|
|
$current_user_plevel = 4;
|
|
if ($current_user_plevel > 2 && $current_user_plevel > $data['plevel']) {
|
|
return $data['address'];
|
|
}
|
|
|
|
return 'you are not authorized to view this column';
|
|
});
|
|
|
|
$dt->hide('plevel'); // hides 'plevel' column from the output
|
|
|
|
$dt->add('action', function($data){
|
|
// returns a link in a new column
|
|
return "<a href='user.php?id=" . $data['id'] . "'>edit</a>";
|
|
});
|
|
|
|
$dt->filter('age', function (){
|
|
// applies custom filtering.
|
|
return $this->between(15, 30);
|
|
});
|
|
|
|
echo $dt->generate()->toJson(); // same as 'echo $dt->generate()';
|
|
```
|
|
|
|
## Road Map
|
|
* better test suites for each class
|
|
* improve integrations for php frameworks
|
|
|
|
## Requirements
|
|
Composer
|
|
DataTables > 1.10
|
|
PHP > 7.1.3
|
|
|
|
## License
|
|
Copyright (c) 2015 Yusuf ÖZDEMİR, released under [the MIT license](https://github.com/n1crack/Datatables/blob/master/LICENCE)
|
|
|
|
#### If you like the library
|
|
<a href="https://www.buymeacoffee.com/ozdemir" target="_blank"><img src="https://www.buymeacoffee.com/assets/img/custom_images/orange_img.png" alt="Buy Me A Coffee" style="height: 41px !important;width: 174px !important;box-shadow: 0px 3px 2px 0px rgba(190, 190, 190, 0.5) !important;-webkit-box-shadow: 0px 3px 2px 0px rgba(190, 190, 190, 0.5) !important;" ></a>
|
|
|
|
|