You can create simple or complex queries, using plugin interface: select a Filter and the desired Oparator and apply the Value.
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> <!-- JQUERY-UI (optional) --> <!-- in this example: datepicker, autocompete, slider, spinner are in use in filters --> <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> <!-- 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> <!-- jui_filter_rules --> <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>
<!-- Just create a div and give it an ID --> <div id="demo_rules1"></div>
$(function() {
$("#demo_rules1").jui_filter_rules({
bootstrap_version: "3",
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", "value": "Smith"}
}
]
},
{
filterName: "AgeInYears", "filterType": "number", "numberType": "double", field: "age", filterLabel: "Age (years)",
excluded_operators: ["in", "not_in"]
},
{
filterName: "GroupMembers", "filterType": "number", "numberType": "integer", field: "group_members", filterLabel: "Group Members",
excluded_operators: ["in", "not_in"],
filter_interface: [
{
filter_element: "input",
filter_element_attributes: {
type: "text",
value: "1",
"style": "width: 75px; margin: 0 5px;"
},
filter_widget: "spinner",
filter_widget_properties: {
min: 1,
max: 10
}
}
]
},
{
filterName: "PerCentCompleted", "filterType": "number", "numberType": "integer", field: "percent_completed", filterLabel: "PerCent Completed",
excluded_operators: ["in", "not_in"],
filter_interface: [
{
filter_element: "input",
filter_element_attributes: {
type: "text",
disabled: "disabled",
style: "width: 40px; display: inline-block;"
}
},
{
filter_element: "div",
filter_element_attributes: {
style: "width: 120px; margin-left: 15px; display: inline-block;"
},
filter_widget: "slider",
filter_widget_properties: {
min: 0,
max: 100,
slide: function(event, ui) {
$(this).prev("input").val(ui.value);
}
},
returns_no_value: "yes"
}
]
},
{
filterName: "DateInserted", "filterType": "date", field: "date_inserted", filterLabel: "Date inserted",
excluded_operators: ["in", "not_in"],
filter_interface: [
{
filter_element: "input",
filter_element_attributes: {
type: "text"
},
filter_widget: "datepicker",
filter_widget_properties: {
dateFormat: "dd/mm/yy",
changeMonth: true,
changeYear: true
}
}
],
validate_dateformat: ["DD/MM/YYYY"],
filter_value_conversion_server_side: {
function_name: "date_encode",
args: [
{"filter_value": "yes"},
{"value": "d/m/Y"}
]
}
},
{
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"}
]
}
},
{
filterName: "Category", "filterType": "number", "numberType": "integer", field: "category", filterLabel: "Category (ajax data)",
excluded_operators: ["equal", "not_equal", "less", "less_or_equal", "greater", "greater_or_equal"],
filter_interface: [
{
filter_element: "input",
filter_element_attributes: {type: "checkbox"},
vertical_orientation: "yes"
}
],
lookup_values_ajax_url: "ajax/ajax_categories.php"
},
{
filterName: "Level", "filterType": "number", "numberType": "integer", field: "level", filterLabel: "Level",
excluded_operators: ["in", "not_in", "less", "less_or_equal", "greater", "greater_or_equal"],
filter_interface: [
{
filter_element: "input",
filter_element_attributes: {
type: "radio",
style: "width: auto; margin-top: 0; display: inline-block;"
}
}
],
lookup_values: [
{lk_option: "Level1", lk_value: "1"},
{lk_option: "Level2", lk_value: "2"},
{lk_option: "Level3", lk_value: "3", lk_selected: "yes"}
]
},
{
filterName: "Language", "filterType": "text", field: "language", filterLabel: "Language code (ajax data)",
excluded_operators: ["in", "not_in", "less", "less_or_equal", "greater", "greater_or_equal"],
filter_interface: [
{
filter_element: "select"
}
],
lookup_values_ajax_url: "ajax/ajax_languages.php"
},
{
filterName: "Company", "filterType": "number", "numberType": "integer", field: "company", filterLabel: "Company",
excluded_operators: ["in", "not_in", "less", "less_or_equal", "greater", "greater_or_equal"],
filter_interface: [
{
filter_element: "select"
}
],
lookup_values: [
{lk_option: "Company1", lk_value: "1"},
{lk_option: "Company2", lk_value: "2"},
{lk_option: "Company3", lk_value: "3", lk_selected: "yes"}
]
},
{
filterName: "Country", "filterType": "text", field: "country", filterLabel: "Country code",
excluded_operators: ["in", "not_in", "less", "less_or_equal", "greater", "greater_or_equal"],
filter_interface: [
{
filter_element: "input",
filter_element_attributes: {type: "text", disabled: "disabled", style: "width: 80px; display: inline-block;"}
},
{
filter_element: "input",
filter_element_attributes: {type: "text", style: "width: 120px; margin-left: 5px; display: inline-block;"},
filter_widget: "autocomplete",
filter_widget_properties: {
source: "ajax/ajax_countries.php",
minLength: 1,
select: function(event, ui) {
$(this).prevAll("input").val(ui.item.id);
},
// mustMatch implementation
change: function(event, ui) {
if(ui.item == null) {
$(this).val('');
$(this).prevAll("input").val('');
}
}
},
returns_no_value: "yes"
}
]
}
],
onValidationError: function(event, data) {
alert(data["err_description"] + ' (' + data["err_code"] + ')');
if(data.hasOwnProperty("elem_filter")) {
data.elem_filter.focus();
}
}
});
});
$("#get_rules").click(function() {
var a_rules = $("#demo_rules1").jui_filter_rules("getRules", 0, []);
if(!a_rules) {
show_modal($("#modal_dialog"), $("#modal_dialog_content"), "Rules error...");
return false;
}
if(a_rules.length == 0) {
show_modal($("#modal_dialog"), $("#modal_dialog_content"), "No rules defined...");
return false;
}
html = '<pre id="demo_code">' + JSON.stringify(a_rules, null, ' ') + '</pre>';
show_modal($("#modal_dialog"), $("#modal_dialog_content"), html);
});
$("#clear_rules").click(function() {
$("#demo_rules1").jui_filter_rules("clearAllRules");
});
$("#get_sql li").click(function() {
var a_rules = false,
use_prepared_statements, pst_placeholder;
a_rules = $("#demo_rules1").jui_filter_rules("getRules", 0, []);
if(!a_rules) {
show_modal($("#modal_dialog"), $("#modal_dialog_content"), "Rules error...");
return false;
}
if(a_rules.length == 0) {
show_modal($("#modal_dialog"), $("#modal_dialog_content"), "No rules defined...");
return false;
}
switch ($(this).index()) {
case 0:
use_prepared_statements = "no";
pst_placeholder = null;
break;
case 1:
use_prepared_statements = "yes";
pst_placeholder = 'question_mark';
break;
case 2:
use_prepared_statements = "yes";
pst_placeholder = 'numbered';
break;
}
$.ajax({
type: 'POST',
url: "ajax/ajax_get_sql.php",
data: {
a_rules: a_rules,
use_ps: use_prepared_statements,
pst_placeholder: pst_placeholder
},
dataType: "JSON",
success: function(data) {
var html = '';
if(data.hasOwnProperty("error")) {
$("#demo_rules1")
.jui_filter_rules("markRuleAsError", data["error"]["element_rule_id"], true)
.triggerHandler("onValidationError",
{
err_code: "filter_error_server_side",
err_description: "Server error during filter converion..." + '\n\n' + data["error"]["error_message"]
}
);
} else {
html += '<pre id="demo_code"><ul>';
html += '<li>SQL: \n\n' + data["sql"] + '\n\n</li>';
html += '<li>Bind params: \n\n' + JSON.stringify(data["bind_params"], null, ' ') + '</li>';
html += '</ul></pre>';
show_modal($("#modal_dialog"), $("#modal_dialog_content"), html);
}
}
});
});
<?php
$a_rules = $_POST['a_rules'];
$use_ps = ($_POST['use_ps'] == "yes" ? true : false);
$pst_placeholder = $_POST['pst_placeholder'];
if(count($a_rules) == 0) {
exit;
}
// required --------------------------------------------------------------------
require_once '/path/to/dacapo.class.php'; // simple database wrapper
require_once '/path/to/jui_filter_rules.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' => $use_ps, // use prepared statements
'pst_placeholder' => $pst_placeholder
);
$ds = new dacapo($db_settings, null);
// print result
$jfr = new jui_filter_rules($ds);
$jfr->set_allowed_functions(array('date_encode'));
$result = $jfr->parse_rules($a_rules);
$last_error = $jfr->get_last_error();
if(!is_null($last_error['error_message'])) {
$result['error'] = $last_error;
}
echo json_encode($result);