Click any row to select or deselect it. Click on table header to apply simple column sorting (triple state: ascending, descending, none). Manage selections from button. Use button to apply multi-column sorting. Click and drag to re-arrange sorting rules.
With columns button you can show or hide columns. Click and drag any column to change its position in data table. Easily re-apply default settings. Use button to toggle filters pane.
Easily navigate pages from pagination section. Go directly to any page or set rows per page.
Apply several themes on the fly using [Themes ] from main menu.
Get the code from [Code ] tab.
<!-- It is a good idea to bundle all CSS in one file. The same with JS --> <!-- JQUERY --> <script type="text/javascript" src="/path/to/jquery.min.js"></script> <!-- BOOTSTRAP --> <link rel="stylesheet" type="text/css" href="/path/to/bootstrap.min.css"> <script type="text/javascript" src="/path/to/bootstrap.min.js"></script> <!-- JQUERY-UI (only sortable and datepicker is needed) --> <link rel="stylesheet" type="text/css" href="/path/to/jquery-ui.min.css"> <script type="text/javascript" src="/path/to/jquery-ui.min.js"></script> <!-- if timepicker is used in filters --> <link rel="stylesheet" type="text/css" href="/path/to/jquery-ui-timepicker-addon.min.css"/> <script type="text/javascript" src="/path/to/jquery-ui-timepicker-addon.min.js"></script> <!-- if touch event support is needed (mobile devices) --> <script type="text/javascript" src="/path/to/jquery.ui.touch-punch.min.js"></script> <!-- PAGINATION plugin --> <link rel="stylesheet" type="text/css" href="/path/to/jquery.bs_pagination.min.css"> <script type="text/javascript" src="/path/to/jquery.bs_pagination.min.js"></script> <script type="text/javascript" src="/path/to/bs_pagination/localization/en.min.js"></script> <!-- FILTERS plugin --> <link rel="stylesheet" type="text/css" href="/path/to/jquery.jui_filter_rules.bs.min.css"> <script type="text/javascript" src="/path/to/jquery.jui_filter_rules.min.js"></script> <script type="text/javascript" src="/path/to/jui_filter_rules/localization/en.min.js"></script> <!-- required from filters plugin --> <script type="text/javascript" src="/path/to/moment.min.js"></script> <!-- DATAGRID plugin --> <link rel="stylesheet" type="text/css" href="/path/to/jquery.bs_grid.min.css"> <script type="text/javascript" src="/path/to/jquery.bs_grid.min.js"></script> <script type="text/javascript" src="/path/to/bs_grid/localization/en.min.js"></script>
<!-- Just create a div and give it an ID --> <div id="demo_grid1"></div>
$(function() {
$("#demo_grid1").bs_grid({
ajaxFetchDataURL: "ajax_fetch_page_data.php",
row_primary_key: "customer_id",
columns: [
{field: "customer_id", header: "Code", visible: "no"},
{field: "lastname", header: "Lastname"},
{field: "firstname", header: "Firstname"},
{field: "email", header: "Email", visible: "no", "sortable": "no"},
{field: "gender", header: "Gender"},
{field: "date_updated", header: "Date updated"}
],
sorting: [
{sortingName: "Code", field: "customer_id", order: "none"},
{sortingName: "Lastname", field: "lastname", order: "ascending"},
{sortingName: "Firstname", field: "firstname", order: "ascending"},
{sortingName: "Date updated", field: "date_updated", order: "none"}
],
filterOptions: {
filters: [
{
filterName: "Lastname", "filterType": "text", field: "lastname", filterLabel: "Last name",
excluded_operators: ["in", "not_in"],
filter_interface: [
{
filter_element: "input",
filter_element_attributes: {"type": "text"}
}
]
},
{
filterName: "Gender", "filterType": "number", "numberType": "integer", field: "lk_genders_id", filterLabel: "Gender",
excluded_operators: ["equal", "not_equal", "less", "less_or_equal", "greater", "greater_or_equal"],
filter_interface: [
{
filter_element: "input",
filter_element_attributes: {type: "checkbox"}
}
],
lookup_values: [
{lk_option: "Male", lk_value: "1"},
{lk_option: "Female", lk_value: "2", lk_selected: "yes"}
]
},
{
filterName: "DateUpdated", "filterType": "date", field: "date_updated", filterLabel: "Datetime updated",
excluded_operators: ["in", "not_in"],
filter_interface: [
{
filter_element: "input",
filter_element_attributes: {
type: "text",
title: "Set the date and time using format: dd/mm/yyyy hh:mm:ss"
},
filter_widget: "datetimepicker",
filter_widget_properties: {
dateFormat: "dd/mm/yy",
timeFormat: "HH:mm:ss",
changeMonth: true,
changeYear: true,
showSecond: true
}
}
],
validate_dateformat: ["DD/MM/YYYY HH:mm:ss"],
filter_value_conversion: {
function_name: "local_datetime_to_UTC_timestamp",
args: [
{"filter_value": "yes"},
{"value": "DD/MM/YYYY HH:mm:ss"}
]
}
}
]
}
});
});
/**
* Convert local timezone date string to UTC timestamp
*
* Alternative syntax using jquery (instead of moment.js):
* var date = $.datepicker.parseDateTime(dateformat, timeformat, date_str);
*
* @see https://stackoverflow.com/questions/948532/how-do-you-convert-a-javascript-date-to-utc
* @param {String} date_str
* @param {String} dateformat
* @return {String}
*/
function local_datetime_to_UTC_timestamp(date_str, dateformat) {
// avoid date overflow in user input (moment("14/14/2005", "DD/MM/YYYY") => Tue Feb 14 2006)
if(moment(date_str, dateformat).isValid() == false) {
throw new Error("Invalid date");
}
// parse date string using given dateformat and create a javascript date object
var date = moment(date_str, dateformat).toDate();
// use javascript getUTC* functions to conv ert to UTC
return date.getUTCFullYear() +
PadDigits(date.getUTCMonth() + 1, 2) +
PadDigits(date.getUTCDate(), 2) +
PadDigits(date.getUTCHours(), 2) +
PadDigits(date.getUTCMinutes(), 2) +
PadDigits(date.getUTCSeconds(), 2);
}
/**
* Add leading zeros
* @param {Number} n
* @param {Number} totalDigits
* @return {String}
*/
function PadDigits(n, totalDigits) {
n = n.toString();
var pd = '';
if(totalDigits > n.length) {
for(i = 0; i < (totalDigits - n.length); i++) {
pd += '0';
}
}
return pd + n.toString();
}
<?php
require_once '/path/to/dacapo.class.php'; // simple database wrapper
require_once '/path/to/jui_filter_rules.php';
require_once '/path/to/bs_grid.php';
$db_settings = array(
'rdbms' => 'MYSQLi',
'db_server' => 'SERVER_NAME OR IP',
'db_user' => 'DB USER',
'db_passwd' => 'DB PASS',
'db_name' => 'DB NAME',
'db_port' => '3306',
'charset' => 'utf8',
'use_pst' => true, // use prepared statements
'pst_placeholder' => 'question_mark'
);
$ds = new dacapo($db_settings, null);
$page_settings = array(
"selectCountSQL" => "SELECT count(id) as totalrows FROM customers",
"selectSQL" => "SELECT c.id as customer_id, c.lastname, c.firstname, c.email, g.gender, c.date_updated
FROM customers c INNER JOIN lk_genders g ON (c.lk_genders_id = g.id)",
"page_num" => $_POST['page_num'],
"rows_per_page" => $_POST['rows_per_page'],
"columns" => $_POST['columns'],
"sorting" => isset($_POST['sorting']) ? $_POST['sorting'] : array(),
"filter_rules" => isset($_POST['filter_rules']) ? $_POST['filter_rules'] : array()
);
$jfr = new jui_filter_rules($ds);
$jdg = new bs_grid($ds, $jfr, $page_settings, $_POST['debug_mode'] == "yes" ? true : false);
$data = $jdg->get_page_data();
// data conversions (if necessary)
foreach($data['page_data'] as $key => $row) {
// this will convert Lastname to a link
$data['page_data'][$key]['lastname'] = "<a href=\"/test/{$row['customer_id']}\">{$row['lastname']}</a>";
// this will format date_updated (attention date_convert is a local function)
$data['page_data'][$key]['date_updated'] = date_convert($row['date_updated'], 'UTC', 'YmdHis', 'UTC', 'd/m/Y H:i:s');
}
echo json_encode($data);
SQL of this demo is available here (MySQL).