PHP code example of kevinpirnie / kpt-datatables
1. Go to this page and download the library: Download kevinpirnie/kpt-datatables library . Choose the download type require .
2. Extract the ZIP file and open the index.php.
3. Add this code to the index.php.
<?php
require_once('vendor/autoload.php');
/* Start to develop here. Best regards https://php-download.com/ */
kevinpirnie / kpt-datatables example snippets
KPT\DataTables;
// Database connection configuration
$dbConfig = [
'server' => 'localhost',
'schema' => 'my_database',
'username' => 'db_user',
'password' => 'db_pass',
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
];
$dt = new DataTables($dbConfig);
// Handle all AJAX requests before any HTML output
if (isset($_POST['action']) || isset($_GET['action'])) {
$dt->table('users')
->handleAjax();
}
// Render CSS and JS assets in your <head> / before </body>
echo DataTables::getCssIncludes('uikit', true, true);
echo DataTables::getJsIncludes('uikit', true, true);
// Render the table
echo $dt
->theme('uikit')
->table('users')
->columns([
'id' => 'ID',
'name' => 'Full Name',
'email' => 'Email',
'created_at' => 'Created',
])
->sortable(['name', 'email', 'created_at'])
->renderDataTableComponent();
// In <head>
echo DataTables::getCssIncludes(string $theme, bool $bool $
echo DataTables::getCssIncludes('uikit', true, true);
// Outputs: UIKit CDN CSS + /vendor/.../uikit.min.css
echo DataTables::getJsIncludes('uikit', true, true);
// Outputs: UIKit CDN JS + UIKit Icons JS + kpt-datatables.min.js
echo DataTables::getCssIncludes('bootstrap', true, true);
// Outputs: Bootstrap CDN CSS + Bootstrap Icons CDN CSS + /vendor/.../bootstrap.min.css
echo DataTables::getJsIncludes('bootstrap', true, true);
// Outputs: Bootstrap Bundle CDN JS + kpt-datatables.min.js
echo DataTables::getCssIncludes('tailwind', false, true);
echo DataTables::getJsIncludes('tailwind', false, true);
$dt = new DataTables($dbConfig);
// Call handleAjax() with the same chain you use for rendering
if (isset($_POST['action']) || isset($_GET['action'])) {
$dt->theme('bootstrap')
->table('orders o')
->primaryKey('o.id')
->join('LEFT', 'customers c', 'o.customer_id = c.id')
->columns([...])
->addForm('Add Order', [...])
->editForm('Edit Order', [...])
->handleAjax();
}
// Then render below
echo $dt->renderDataTableComponent();
$dt->theme(string $theme, bool $
// UIKit with CDN
$dt->theme('uikit');
// Bootstrap without CDN (you load Bootstrap yourself)
$dt->theme('bootstrap', false);
// Tailwind (CDN not applicable)
$dt->theme('tailwind', false);
// Plain
$dt->theme('plain');
->table(string $tableName)
// Simple
->table('users')
// With alias (her s')
->primaryKey(string $column)
->primaryKey('id')
->primaryKey('u.user_id')
->primaryKey('s.id')
->database(array $config)
->database([
'server' => 'localhost',
'schema' => 'my_db',
'username' => 'root',
'password' => 'secret',
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
])
->columns(array $columns)
->columns([
'id' => 'ID',
'u.name' => 'Full Name',
'u.email' => 'Email',
'r.role_name' => 'Role',
])
->columns([
'id' => 'ID',
'u.name' => 'Full Name',
'u.email' => ['label' => 'Email Address', 'type' => 'email'],
'status' => [
'label' => 'Status',
'type' => 'boolean',
],
'category' => [
'label' => 'Category',
'type' => 'select',
'options' => ['1' => 'News', '2' => 'Blog', '3' => 'Event'],
],
'user_id' => [
'label' => 'Assigned User',
'type' => 'select2',
'query' => 'SELECT id AS ID, u_name AS Label FROM users',
'placeholder' => 'Search users...',
'min_search_chars' => 2,
'max_results' => 50,
],
'created_at' => [
'label' => 'Created',
'type' => 'datepicker',
'formatter' => 'MM/DD/YYYY',
],
])
->join(string $type, string $table, string $condition)
->table('orders o')
->join('LEFT', 'customers c', 'o.customer_id = c.id')
->join('LEFT', 'products p', 'o.product_id = p.id')
->join('INNER', 'order_status s', 'o.status_id = s.id')
->where(array $conditions)
->where([
['field' => 'status', 'comparison' => '=', 'value' => 'active'],
['field' => 'deleted_at', 'comparison' => '=', 'value' => null],
['field' => 'created_at', 'comparison' => '>=', 'value' => '2024-01-01'],
])
->where([
['field' => 'role_id', 'comparison' => 'IN', 'value' => [1, 2, 3]],
])
->filter(array $filters)
->filter([
'name' => 'LIKE',
'status' => '=',
'created_at' => 'BETWEEN',
])
->filter([
'o.status' => [
'operator' => '=',
'label' => 'Order Status',
'type' => 'select',
'options' => ['pending' => 'Pending', 'shipped' => 'Shipped', 'delivered' => 'Delivered'],
'placeholder' => '',
],
'c.name' => [
'operator' => 'LIKE',
'label' => 'Customer Name',
'placeholder' => 'Search by name...',
],
'is_active' => [
'operator' => '=',
'label' => 'Active',
'type' => 'boolean',
],
'created_at' => [
'operator' => 'BETWEEN',
'label' => 'Date Range',
'type' => 'date',
],
])
->sortable(array $columns)
->sortable(['name', 'email', 'created_at'])
// With joined/aliased columns
->sortable(['u.name', 'u.email', 'r.role_name', 'd.dept_name'])
->inlineEditable(array $columns)
->inlineEditable(['name', 'email', 'status'])
// Qualified names
->inlineEditable(['u.name', 'u.email', 'u.status'])
->perPage(int $count)
->perPage(25) // default
->perPage(50)
->perPage(100)
->pageSizeOptions(array $options, bool $
->pageSizeOptions([10, 25, 50, 100], true)
->pageSizeOptions([25, 50, 100, 250], false)
->search(bool $enabled = true)
->search(true) // default
->search(false) // hide search completely
->defaultSort(string $column, string $direction = 'ASC')
->defaultSort('created_at', 'DESC')
->defaultSort('u.name', 'ASC')
->groupBy(string $column)
->groupBy('user_id')
->groupBy('o.status')
->actions(string $position = 'end', bool $showEdit = true, bool $showDelete = true, array $customActions = [])
->actions('end', true, true) // Both buttons at end (default)
->actions('start', true, false) // Edit only at start
->actions('end', false, true) // Delete only at end
->actionGroups(array $groups)
->actionGroups([
['edit', 'delete'],
])
->actionGroups([
[
'view' => [
'icon' => 'search',
'title' => 'View Record',
'class' => 'btn-view',
'href' => '/records/{id}',
],
],
['edit', 'delete'],
])
->actionGroups([
[
'export' => [
'icon' => 'download',
'title' => 'Export {name}',
'href' => '/export/{id}?ref={order_ref}',
'class' => 'btn-export',
'attributes' => [
'data-id' => '{id}',
'data-ref' => '{order_ref}',
],
],
],
['edit', 'delete'],
])
->actionGroups([
[
'approve' => [
'icon' => 'check',
'title' => 'Approve',
'class' => 'btn-approve',
'confirm' => 'Approve this record?',
'success_message' => 'Record approved',
'error_message' => 'Approval failed',
'callback' => function($rowId, $rowData, $db, $table) {
return $db->query("UPDATE `{$table}` SET status = 'approved' WHERE id = ?")
->bind([$rowId])
->execute();
},
],
],
['edit', 'delete'],
])
->actionGroups([
[
'html1' => [
'location' => 'before',
'content' => '<span class="divider">|</span>',
],
'view' => ['icon' => 'search', 'href' => '/view/{id}'],
],
['edit', 'delete'],
])
->bulkActions(bool $enabled = true, array $actions = [])
->bulkActions(true)
->bulkActions(true, [
'activate' => [
'label' => 'Activate Selected',
'icon' => 'check',
'confirm' => 'Activate selected records?',
'success_message' => 'Records activated',
'error_message' => 'Activation failed',
'callback' => function($ids, $db, $table) {
$placeholders = implode(',', array_fill(0, count($ids), '?'));
return $db->query("UPDATE `{$table}` SET active = 1 WHERE id IN ({$placeholders})")
->bind($ids)
->execute();
},
],
'archive' => [
'label' => 'Archive Selected',
'icon' => 'folder',
'confirm' => 'Archive selected records?',
'callback' => function($ids, $db, $table) {
// ...
},
],
])
->addForm(string $title, array $fields, bool $ajax = true, string $class = '')
->addForm('Add New User', [
'name' => [
'type' => 'text',
'label' => 'Full Name',
'mail Address',
' 'User'],
],
'status' => [
'type' => 'boolean',
'label' => 'Active',
'value' => '1',
],
])
->editForm(string $title, array $fields, bool $ajax = true, string $class = '')
->editForm('Edit User', [
'name' => ['type' => 'text', 'label' => 'Full Name', 'role_id' => [
'type' => 'select',
'label' => 'Role',
'options' => ['1' => 'Admin', '2' => 'Editor', '3' => 'User'],
],
'status' => ['type' => 'boolean', 'label' => 'Active'],
])
'user_id' => [
'type' => 'select2',
'label' => 'Assigned User',
'query' => 'SELECT id AS ID, CONCAT(first, " ", last) AS Label FROM users WHERE active = 1',
'placeholder' => 'Search users...',
'min_search_chars' => 2,
'max_results' => 25,
'
'city_id' => [
'type' => 'select2',
'query' => 'SELECT id AS ID, city_name AS Label FROM cities WHERE state_id = {state_id}',
]
'birth_date' => [
'type' => 'datepicker',
'label' => 'Date of Birth',
'formatter' => 'MM/DD/YYYY',
]
'created_by' => [
'type' => 'static',
'label' => 'Created By',
'content' => '', // empty = populated from DB on edit
]
->addForm('Add User', [
'name' => ['type' => 'text', 'label' => 'Name'],
'email' => ['type' => 'email', 'label' => 'Email'],
'bio' => ['type' => 'textarea', 'label' => 'Bio', 'tab' => 'Profile'],
'photo' => ['type' => 'image', 'label' => 'Photo', 'tab' => 'Profile'],
'role_id' => ['type' => 'select', 'label' => 'Role', 'tab' => 'Permissions', 'options' => [...]],
'department' => ['type' => 'select', 'label' => 'Dept', 'tab' => 'Permissions', 'options' => [...]],
])
'field_name' => [
'type' => 'text',
'label' => 'Some Field',
'allow_on' => [
'field' => 'status', // Field from the fetched record to evaluate
'operator' => '==', // Comparison operator
'value' => 'approved', // Value to compare against
'action' => [
'set_value' => 'auto-filled', // Force value
'set_attributes' => ['readonly' => 'readonly'], // Add/remove attributes
'set_classes' => ['uk-text-muted'], // Add CSS classes
],
],
]
->calculatedColumn(string $alias, string $label, array $columns, string $operator = '+')
->calculatedColumn('line_total', 'Line Total', ['quantity', 'unit_price'], '*')
->calculatedColumn('profit', 'Profit', ['revenue', 'cost'], '-')
->calculatedColumnRaw(string $alias, string $label, string $expression)
->calculatedColumnRaw('margin_pct', 'Margin %',
'((sell_price - cost_price) / sell_price) * 100')
->calculatedColumnRaw('full_name', 'Full Name',
'CONCAT(u.first_name, " ", u.last_name)')
->footerAggregate(string $column, string $type = 'sum', string $scope = 'both', string $label = '')
->footerAggregate('amount', 'sum', 'both')
->footerAggregate('tax', 'avg', 'all')
->footerAggregate('quantity', 'both', 'page', 'Page Totals')
->footerAggregateColumns(array $columns, string $type = 'sum', string $scope = 'both', string $label = '')
->footerAggregateColumns(['amount', 'tax', 'shipping'], 'sum', 'both')
->calculatedColumn('line_total', 'Line Total', ['quantity', 'unit_price'], '*')
->footerAggregate('line_total', 'sum', 'both')
->tableClass(string $class)
->tableClass('uk-table uk-table-striped uk-table-hover my-custom-table')
->tableClass('table table-dark table-sm')
->rowClass(string $class)
->rowClass('data-row')
// Produces: class="data-row-42 row-select"
->columnClasses(array $classes)
->columnClasses([
'id' => 'uk-table-shrink',
'u.name' => 'uk-text-bold',
'u.email' => 'uk-text-primary',
'status' => 'uk-text-center',
's_stream_uri' => 'txt-truncate',
])
->fileUpload(string $uploadPath = 'uploads/', array $allowedExtensions = [], int $maxFileSize = 10485760)
->fileUpload('uploads/avatars/', ['jpg', 'jpeg', 'png', 'gif', 'webp'], 5242880)
->fileUpload('uploads/documents/', ['pdf', 'doc', 'docx', 'xls', 'xlsx'])
echo $dt->renderDataTableComponent();
DataTables::getCssIncludes(string $theme = 'uikit', bool $
DataTables::getJsIncludes(string $theme = 'uikit', bool $
// Filter accordion panel (omit from renderDataTableComponent() to avoid duplicates)
echo $dt->renderFilterAccordionComponent();
// Search form input + reset button
echo $dt->renderSearchFormComponent();
// Bulk actions toolbar (add button + bulk operation buttons)
echo $dt->renderBulkActionsComponent();
// Per-page selector as dropdown (default)
echo $dt->renderPageSizeSelectorComponent();
// Per-page selector as button group
echo $dt->renderPageSizeSelectorComponent(true);
// Pagination list + record info text
echo $dt->renderPaginationComponent();
KPT\DataTables;
$dbConfig = [
'server' => 'localhost',
'schema' => 'my_app',
'username' => 'db_user',
'password' => 'db_pass',
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
];
$dt = new DataTables($dbConfig);
if (isset($_POST['action']) || isset($_GET['action'])) {
$dt->theme('uikit')
->table('orders o')
->primaryKey('o.id')
->join('LEFT', 'customers c', 'o.customer_id = c.id')
->join('LEFT', 'products p', 'o.product_id = p.id')
->where([
['field' => 'o.deleted_at', 'comparison' => '=', 'value' => null],
])
->addForm('New Order', [
'customer_id' => [
'type' => 'select2',
'label' => 'Customer',
'query' => 'SELECT id AS ID, company_name AS Label FROM customers WHERE active = 1',
'placeholder' => 'Search customers...',
'min_search_chars' => 2,
' 'notes' => ['type' => 'textarea', 'label' => 'Notes', 'tab' => 'Notes'],
])
->handleAjax();
}
echo DataTables::getCssIncludes('uikit', true, true);
echo $dt
->theme('uikit')
->table('orders o')
->primaryKey('o.id')
->join('LEFT', 'customers c', 'o.customer_id = c.id')
->join('LEFT', 'products p', 'o.product_id = p.id')
->where([
['field' => 'o.deleted_at', 'comparison' => '=', 'value' => null],
])
->columns([
'o.id' => 'Order #',
'c.company_name' => 'Customer',
'p.product_name' => 'Product',
'o.quantity' => 'Qty',
'o.unit_price' => 'Unit Price',
'o.status' => ['label' => 'Status', 'type' => 'boolean'],
'o.created_at' => 'Ordered',
])
->calculatedColumn('line_total', 'Line Total', ['o.quantity', 'o.unit_price'], '*')
->footerAggregate('o.quantity', 'sum', 'page')
->footerAggregate('line_total', 'sum', 'both', 'Totals')
->sortable(['c.company_name', 'p.product_name', 'o.quantity', 'o.created_at'])
->inlineEditable(['o.quantity', 'o.status'])
->filter([
'c.company_name' => ['operator' => 'LIKE', 'label' => 'Customer'],
'o.status' => ['operator' => '=', 'label' => 'Status', 'type' => 'boolean'],
'o.created_at' => ['operator' => 'BETWEEN', 'label' => 'Order Date', 'type' => 'date'],
])
->defaultSort('o.created_at', 'DESC')
->perPage(25)
->pageSizeOptions([25, 50, 100], true)
->bulkActions(true, [
'cancel' => [
'label' => 'Cancel Selected',
'icon' => 'close',
'confirm' => 'Cancel selected orders?',
'callback' => function($ids, $db, $table) {
$ph = implode(',', array_fill(0, count($ids), '?'));
return $db->query("UPDATE `{$table}` SET status = 0 WHERE id IN ({$ph})")
->bind($ids)->execute();
},
],
])
->actionGroups([
[
'invoice' => [
'icon' => 'print',
'title' => 'Print Invoice',
'href' => '/invoice/{id}',
'class' => 'btn-invoice',
],
],
['edit', 'delete'],
])
->columnClasses([
'o.id' => 'uk-table-shrink',
'o.status' => 'uk-text-center',
'line_total' => 'uk-text-right',
])
->fileUpload('uploads/attachments/', ['pdf', 'doc', 'docx', 'png', 'jpg'], 10485760)
->renderDataTableComponent();
echo DataTables::getJsIncludes('uikit', true, true);