Datatables php library

add-column.html

<table border="0" class="display" id="example" width="100%">
    <thead>
    <tr>
        <th width="8%">Id</th>
        <th width="50%">Name</th>
        <th width="22%">Unit Price</th>
        <th width="20%">Action</th>
    </tr>
    </thead>
    <tbody>
    <tr>
        <td>loading...</td>
    </tr>
    </tbody>
</table>

add-column.js

$(document).ready(function () {
    $('#example').dataTable({
        "serverSide": true,
        "responsive": true,
        "ajax": "ajax/add-column.php"
    });
});

add-column.php

<?php
//  Php frameworks add the autoloader class for you.
//  If you don't use any php framework, be sure that you have included it yourself. 
//
//  require_once '..path/to../vendor/autoload.php';

use Ozdemir\Datatables\Datatables;
use Ozdemir\Datatables\DB\SQLite;

// using SQLite as DB for the examples. Change here to fit your db.
$path = dirname(__DIR__).'/database/Chinook_Sqlite_AutoIncrementPKs.sqlite';
$dt = new Datatables(new SQLite($path));

$dt->query('Select TrackId as id, Name, UnitPrice from Track');

// add 'action' column
$dt->add('action', function ($data) {
    return '<a href="#edit'.$data['id'].'">#edit </a> '.'/ <a href="#del'.$data['id'].'">#delete </a> ';
});

echo $dt->generate();

basic.html

<table border="0" class="display" id="example" width="100%">
    <thead>
    <tr>
        <th width="8%">Id</th>
        <th width="60%">Name</th>
        <th width="32%">Unit Price</th>
    </tr>
    </thead>
    <tbody>
    <tr>
        <td>loading...</td>
    </tr>
    </tbody>
</table>

basic.js

$(document).ready(function () {
  $('#example').dataTable({
      "serverSide": true,
      "responsive": true,
      "ajax": "ajax/basic.php"
  });
});

basic.php

<?php
//  Php frameworks add the autoloader class for you.
//  If you don't use any php framework, be sure that you have included it yourself. 
//
//  require_once '..path/to../vendor/autoload.php';

use Ozdemir\Datatables\Datatables;
use Ozdemir\Datatables\DB\SQLite;

// using SQLite as DB for the examples. Change here to fit your db.
$path = dirname(__DIR__).'/database/Chinook_Sqlite_AutoIncrementPKs.sqlite';
$dt = new Datatables(new SQLite($path));

$dt->query('Select TrackId, Name, UnitPrice from Track');

echo $dt->generate();

colreorder.html

<div class="box">
    <table border="0" class="display" id="example" width="100%">
        <thead>
        <tr>
            <th>ID</th>
            <th>Track Name</th>
            <th>Album</th>
            <th>MediaType</th>
        </tr>
        </thead>
        <tbody>
        <tr>
            <td>loading...</td>
        </tr>
        <tfoot>
        <tr>
            <th>ID</th>
            <th>Track Name</th>
            <th>Album</th>
            <th>MediaType</th>
        </tr>
        </tfoot>
        </tbody>
    </table>
</div>

colreorder.js

$(document).ready(function () {
    // Setup - add a text input to each footer cell
    $('#example tfoot th').each(function () {
        var title = $(this).text();
        $(this).html( '<input type="text" class="input is-small" placeholder="Search '+title+'" />' );
    });

    // DataTable
    var table = $('#example').DataTable({
        "serverSide": true,
        "responsive": true,
        "colReorder": true,
        "ajax": "ajax/colreorder.php",
        "columns": [
            {"data": "TrackId"},
            {"data": "Name"},
            {"data": "Album"},
            {"data": "MediaType"}
        ]
    });

    // Apply the filter
    $("#example tfoot input").on( 'keyup change', function () {
        table
            .column( $(this).parent().index()+':visible' )
            .search( this.value )
            .draw();
    } );

});

colreorder.php

<?php
//  Php frameworks add the autoloader class for you.
//  If you don't use any php framework, be sure that you have included it yourself. 
//
//  require_once '..path/to../vendor/autoload.php';

use Ozdemir\Datatables\Datatables;
use Ozdemir\Datatables\DB\SQLite;

// using SQLite as DB for the examples. Change here to fit your db.
$path = dirname(__DIR__).'/database/Chinook_Sqlite_AutoIncrementPKs.sqlite';

$dt = new Datatables(new SQLite($path));
$dt->query('Select TrackId, Track.Name, Title as Album, MediaType.Name as MediaType
        from Track 
        JOIN Album ON Album.AlbumId = Track.AlbumId
        JOIN MediaType ON MediaType.MediaTypeId = Track.MediaTypeId');

echo $dt->generate();

custom-filter.html

<table border="0" class="display" id="example" width="100%">
    <thead>
    <tr>
        <th>ID</th>
        <th>Track Name</th>
        <th>Album</th>
        <th>MediaType</th>
    </tr>
    </thead>
    <tbody>
    <tr>
        <td>loading...</td>
    </tr>
    <tfoot>
    <tr>
        <th>ID</th>
        <th>Track Name</th>
        <th>Album</th>
        <th>MediaType</th>
    </tr>
    </tfoot>
    </tbody>
</table>

custom-filter.js

$(document).ready(function() {
    // Setup - add a text input to each footer cell
    $('#example tfoot th').each( function () {
        var title = $(this).text();
            $(this).html( '<input type="text" class="input is-small" placeholder="Search '+title+'" />' );
    } );

    // DataTable
    var table = $('#example').DataTable( {
        "serverSide": true,
        "responsive": true,
        "ajax": "ajax/custom-filter.php"
    } );

    // Apply the filter
    $("#example tfoot input").on( 'keyup change', function () {
        table
            .column( $(this).parent().index()+':visible' )
            .search( this.value )
            .draw();
    } );
} );

custom-filter.php

<?php
//  Php frameworks add the autoloader class for you.
//  If you don't use any php framework, be sure that you have included it yourself. 
//
//  require_once '..path/to../vendor/autoload.php';

use Ozdemir\Datatables\Datatables;
use Ozdemir\Datatables\DB\SQLite;

// using SQLite as DB for the examples. Change here to fit your db.
$path = dirname(__DIR__).'/database/Chinook_Sqlite_AutoIncrementPKs.sqlite';
$dt = new Datatables(new SQLite($path));

$dt->query('Select TrackId, Track.Name, Title as Album, MediaType.Name as MediaType
        from Track 
        JOIN Album ON Album.AlbumId = Track.AlbumId
        JOIN MediaType ON MediaType.MediaTypeId = Track.MediaTypeId');

$dt->filter('TrackId', function () {
    return $this->between(5, 11);
});

echo $dt->generate();

custom-filter2.html

<table border="0" class="display" id="example" width="100%">
    <thead>
    <tr>
        <th>ID</th>
        <th>Track Name</th>
        <th>Genre</th>
        <th>MediaType</th>
    </tr>
    </thead>
    <tbody>
    <tr>
        <td>loading...</td>
    </tr>
    <tfoot>
    <tr>
        <th>ID</th>
        <th>Track Name</th>
        <th>Genre</th>
        <th>MediaType</th>
    </tr>
    </tfoot>
    </tbody>
</table>

custom-filter2.js

$(document).ready(function () {

    // DataTable
    var table = $('#example').DataTable({
        "serverSide": true,
        "responsive" : true,
        "ajax": "ajax/custom-filter2.php"
    });

    table.on('xhr', function () {
        var json = table.ajax.json();

        // yadcf
        yadcf.init(table, [
            {
                column_number: 0,
                filter_type: "range_number",
                filter_delay: 500
            },
            {
                column_number: 1,
                filter_type: "text",
                filter_delay: 500
            },
            {
                column_number: 2,
                filter_type: "select",
                data: json.distinctData.Genre
            },
            {
                column_number: 3,
                filter_type: "multi_select",
                select_type: 'chosen',
                data: json.distinctData.MediaType
            }
        ]);
    });
});

custom-filter2.php

<?php
//  Php frameworks add the autoloader class for you.
//  If you don't use any php framework, be sure that you have included it yourself. 
//
//  require_once '..path/to../vendor/autoload.php';

use Ozdemir\Datatables\Datatables;
use Ozdemir\Datatables\DB\SQLite;

// using SQLite as DB for the examples. Change here to fit your db.
$path = dirname(__DIR__).'/database/Chinook_Sqlite_AutoIncrementPKs.sqlite';
$dt = new Datatables(new SQLite($path));

$dt->query('Select TrackId, Track.Name, Genre.Name as Genre, MediaType.Name as MediaType
        from Track 
        JOIN Genre ON Genre.GenreId = Track.GenreId
        JOIN MediaType ON MediaType.MediaTypeId = Track.MediaTypeId');

$dt->filter('TrackId', function () {
    if ($this->searchValue() === '-yadcf_delim-') {
        return;
    }
    $val = explode('-yadcf_delim-', $this->searchValue());

    return $this->between($val[0], $val[1] ?? null);
});

$dt->filter('MediaType', function () {

    if ($this->searchValue() === '') {
        return;
    }

    $array = explode('|', $this->searchValue());

    return $this->whereIn($array);
});

$dt->setDistinctResponseFrom('Genre');

// alternative to: $dt->setDistinctResponseFrom('MediaType');
$dt->setDistinctResponse([
    'MediaType' => [
        'AAC audio file',
        'MPEG audio file',
        'Protected AAC audio file',
        'Protected MPEG-4 video file',
        'Purchased AAC audio file',
    ],
]);

echo $dt->generate();

edit-column.html

<table border="0" class="display" id="example" width="100%">
    <thead>
    <tr>
        <th width="18%">Id</th>
        <th width="60%">Name</th>
        <th width="22%">Unit Price</th>
    </tr>
    </thead>
    <tbody>
    <tr>
        <td>loading...</td>
    </tr>
    </tbody>
</table>

edit-column.js

$(document).ready(function () {
    $('#example').dataTable({
        "serverSide": true,
        "responsive": true,
        "ajax": "ajax/edit-column.php"
    });
});

edit-column.php

<?php
//  Php frameworks add the autoloader class for you.
//  If you don't use any php framework, be sure that you have included it yourself. 
//
//  require_once '..path/to../vendor/autoload.php';

use Ozdemir\Datatables\Datatables;
use Ozdemir\Datatables\DB\SQLite;

// using SQLite as DB for the examples. Change here to fit your db.
$path = dirname(__DIR__).'/database/Chinook_Sqlite_AutoIncrementPKs.sqlite';
$dt = new Datatables(new SQLite($path));

$dt->query('Select TrackId as id, Name, UnitPrice from Track');

// edit 'id' column
$dt->edit('id', function ($data) {
    return '<a href="#'.$data['id'].'">#edit </a> '.$data['id'];
});

// edit 'UnitPrice' column.
$dt->edit('UnitPrice', function ($data) {
    return '$' . $data['UnitPrice'];
});

echo $dt->generate();

group-by.html

<table border="0" class="display" id="example" width="100%">
    <thead>
    <tr>
        <th>Genre</th>
        <th>Total Length</th>
    </tr>
    </thead>
    <tbody>
    <tr>
        <td>loading...</td>
    </tr>
    </tbody>
</table>

group-by.js

$(document).ready(function () {
    $('#example').dataTable({
        "serverSide": true,
        "responsive": true,
        "ajax": "ajax/group-by.php"
    });
});

group-by.php

<?php
//  Php frameworks add the autoloader class for you.
//  If you don't use any php framework, be sure that you have included it yourself. 
//
//  require_once '..path/to../vendor/autoload.php';

use Ozdemir\Datatables\Datatables;
use Ozdemir\Datatables\DB\SQLite;

// using SQLite as DB for the examples. Change here to fit your db.
$path = dirname(__DIR__).'/database/Chinook_Sqlite_AutoIncrementPKs.sqlite';
$dt = new Datatables(new SQLite($path));

$dt->query('Select Genre.Name as genre_name, sum(Milliseconds)/(1000*60) as total_length 
            from Genre 
            join Track on Track.GenreId = Genre.GenreId
            group by Genre.GenreId');

$dt->edit('total_length', function ($data) {
    return $data['total_length'].' minutes';
});

echo $dt->generate();

hide-column.html

<table border="0" class="display" id="example" width="100%">
    <thead>
    <tr>
        <th width="68%">Name</th>
        <th width="32%">Unit Price</th>
    </tr>
    </thead>
    <tbody>
    <tr>
        <td>loading...</td>
    </tr>
    </tbody>
</table>

hide-column.js

$(document).ready(function () {
    $('#example').dataTable({
        "serverSide": true,
        "responsive": true,
        "ajax": "ajax/hide-column.php"
    });
});

hide-column.php

<?php
//  Php frameworks add the autoloader class for you.
//  If you don't use any php framework, be sure that you have included it yourself. 
//
//  require_once '..path/to../vendor/autoload.php';

use Ozdemir\Datatables\Datatables;
use Ozdemir\Datatables\DB\SQLite;

// using SQLite as DB for the examples. Change here to fit your db.
$path = dirname(__DIR__).'/database/Chinook_Sqlite_AutoIncrementPKs.sqlite';
$dt = new Datatables(new SQLite($path));

$dt->query('Select TrackId as id, Name, UnitPrice from Track');

// hide 'id' column
$dt->hide('id');

// edit 'Name' column.
$dt->edit('Name', function ($data) {
    return $data['Name']." (id: {$data['id']})";
});

echo $dt->generate();

individual-search.html

<table border="0" class="display" id="example" width="100%">
    <thead>
    <tr>
        <th>ID</th>
        <th>Track Name</th>
        <th>Album</th>
        <th>MediaType</th>
    </tr>
    </thead>
    <tbody>
    <tr>
        <td>loading...</td>
    </tr>
    <tfoot>
    <tr>
        <th>ID</th>
        <th>Track Name</th>
        <th>Album</th>
        <th>MediaType</th>
    </tr>
    </tfoot>
    </tbody>
</table>

individual-search.js

$(document).ready(function() {
    // Setup - add a text input to each footer cell
    $('#example tfoot th').each( function () {
        var title = $(this).text();
            $(this).html( '<input type="text" class="input is-small" placeholder="Search '+title+'" />' );
    } );

    // DataTable
    var table = $('#example').DataTable( {
        "serverSide": true,
        "responsive": true,
        "ajax": "ajax/individual-search.php"
    } );


    // Apply the filter
    $("#example tfoot input").on( 'keyup change', function () {
        table
            .column( $(this).parent().index()+':visible' )
            .search( this.value )
            .draw();
    } );

} );

individual-search.php

<?php
//  Php frameworks add the autoloader class for you.
//  If you don't use any php framework, be sure that you have included it yourself. 
//
//  require_once '..path/to../vendor/autoload.php';

use Ozdemir\Datatables\Datatables;
use Ozdemir\Datatables\DB\SQLite;

// using SQLite as DB for the examples. Change here to fit your db.
$path = dirname(__DIR__).'/database/Chinook_Sqlite_AutoIncrementPKs.sqlite';
$dt = new Datatables(new SQLite($path));

$dt->query('Select TrackId, Track.Name, Title as Album, MediaType.Name as MediaType
            from Track 
            JOIN Album ON Album.AlbumId = Track.AlbumId
            JOIN MediaType ON MediaType.MediaTypeId = Track.MediaTypeId');

echo $dt->generate();

join-tables.html

<table border="0" class="display" id="example" width="100%">
    <thead>
    <tr>
        <th>ID</th>
        <th>Track Name</th>
        <th>Album</th>
        <th>MediaType</th>
        <th>UnitPrice</th>
        <th>Milliseconds</th>
        <th>Bytes</th>
    </tr>
    </thead>
    <tbody>
    <tr>
        <td>loading...</td>
    </tr>
    </tbody>
</table>

join-tables.js

$(document).ready(function () {
    $('#example').dataTable({
        "serverSide": true,
        "responsive": true,
        "ajax": "ajax/join-tables.php"
    });
});

join-tables.php

<?php
//  Php frameworks add the autoloader class for you.
//  If you don't use any php framework, be sure that you have included it yourself. 
//
//  require_once '..path/to../vendor/autoload.php';

use Ozdemir\Datatables\Datatables;
use Ozdemir\Datatables\DB\SQLite;

// using SQLite as DB for the examples. Change here to fit your db.
$path = dirname(__DIR__).'/database/Chinook_Sqlite_AutoIncrementPKs.sqlite';
$dt = new Datatables(new SQLite($path));

$dt->query('Select TrackId, Track.Name, Title as Album, MediaType.Name as MediaType, 
        UnitPrice, Milliseconds, Bytes 
        FROM Track 
        JOIN Album ON Album.AlbumId = Track.AlbumId
        JOIN MediaType ON MediaType.MediaTypeId = Track.MediaTypeId');

echo $dt->generate();

laravel.html

<table border="0" class="display" id="example" width="100%">
    <thead>
    <tr>
        <th>ID</th>
        <th>Track Name</th>
        <th>Album</th>
        <th>MediaType</th>
        <th>UnitPrice</th>
        <th>Milliseconds</th>
        <th>Bytes</th>
    </tr>
    </thead>
    <tbody>
    <tr>
        <td>loading...</td>
    </tr>
    </tbody>
</table>

laravel.js

$(document).ready(function () {
    $('#example').dataTable({
        "serverSide": true,
        "responsive": true,
        "ajax": "ajax/laravel"
    });
});

laravel.php

<?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);

    return $dt->generate();
});

object-data-source.html

<table border="0" class="display" id="example" width="100%">
    <thead>
    <tr>
        <th width="8%">Id</th>
        <th width="60%">Name</th>
        <th width="32%">Unit Price</th>
    </tr>
    </thead>
    <tbody>
    <tr>
        <td>loading...</td>
    </tr>
    </tbody>
</table>

object-data-source.js

$(document).ready(function () {
    $('#example').dataTable({
        "serverSide": true,
        "responsive": true,
        "ajax": "ajax/object-data-source.php",
        "columns": [
            {"data": "TrackId"},
            {"data": "Name"},
            {"data": "UnitPrice"}
        ]
    });
});

object-data-source.php

<?php
//  Php frameworks add the autoloader class for you.
//  If you don't use any php framework, be sure that you have included it yourself. 
//
//  require_once '..path/to../vendor/autoload.php';

use Ozdemir\Datatables\Datatables;
use Ozdemir\Datatables\DB\SQLite;

// using SQLite as DB for the examples. Change here to fit your db.
$path = dirname(__DIR__).'/database/Chinook_Sqlite_AutoIncrementPKs.sqlite';
$dt = new Datatables(new SQLite($path));

$dt->query('Select TrackId, Name, UnitPrice from Track');

echo $dt->generate();

post-data.html

<table border="0" class="display" id="example" width="100%">
    <thead>
    <tr>
        <th width="8%">Id</th>
        <th width="60%">Name</th>
        <th width="32%">Unit Price</th>
    </tr>
    </thead>
    <tbody>
    <tr>
        <td>loading...</td>
    </tr>
    </tbody>
</table>

post-data.js

$(document).ready(function () {
  $('#example').dataTable({
    "serverSide": true,
    "responsive": true,
    "ajax": {
      "url": "ajax/post-data.php",
      "type": "POST",
      headers: {
        'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')
      }
    }
  });
  // Note: some php frameworks have csrf protection as default, you must pass your csrf token with your request if you use any.
});

post-data.php

<?php
//  Php frameworks add the autoloader class for you.
//  If you don't use any php framework, be sure that you have included it yourself. 
//
//  require_once '..path/to../vendor/autoload.php';

use Ozdemir\Datatables\Datatables;
use Ozdemir\Datatables\DB\SQLite;

// using SQLite as DB for the examples. Change here to fit your db.
$path = dirname(__DIR__).'/database/Chinook_Sqlite_AutoIncrementPKs.sqlite';
$dt = new Datatables(new SQLite($path));

$dt->query('Select TrackId, Name, UnitPrice from Track');

echo $dt->generate();