Hello,
today I downloaded a trial version and I'm testing the grid functionalities.
It Looks great and offers a lot of excellent Features. Smaller grids with "local data" worked really fine.
But I got stuck when playing around with the remote header filtering example.
I adapted the code snippets from the demo and tried to load the data from an mysql database.
What I receive is a perfect grid, but wothout any content! It says "No rows to Display".
What I have done:
I created a table called "ordercustomers" in the db "paramquery".
It includes the columns: ShipCountry, ContactName, OrderID, OrderDate, ShipRegion, paid, ShipVia, RequiredDate, ShippedDate, Freight, ShipName, ShipAddress, ShipCity, ShipPostalCode.
I created a php file to fetch the data "getdata.php" I added the DB_HOSTNAME, DB_NAME, DB_USERNAME and DB_PASSWORD.
I changed the URL code in the example.
Hopefully someone can give me a hint and help me out.
Greetings,
Andre
code grid2.html:
<html>
<head>
<title>ParamQuery Grid Pro Demos</title>
<!--jQuery dependencies-->
<link rel="stylesheet"
href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.2/themes/base/jquery-ui.css" />
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.2/jquery-ui.min.js"></script>
<!--ParamQuery Grid files-->
<link rel="stylesheet" href="pqgrid.min.css" />
<script type="text/javascript" src="pqgrid.min.js" ></script>
<!--Include Touch Punch file to provide support for touch devices-->
<script type="text/javascript" src="touch-punch/touch-punch.js" ></script>
<!--Insert demo script-->
<script type="text/javascript">
$(function () {
//calender dialog
function pqDatePicker(ui) {
var $this = $(this);
$this
.css({ zIndex: 3, position: "relative" })
.datepicker({
yearRange: "-20:+0", //20 years prior to present.
changeYear: true,
changeMonth: true,
showButtonPanel: true,
onClose: function (evt, ui) {
$(this).focus();
}
});
//default From date
$this.filter(".pq-from").datepicker("option", "defaultDate", new Date("01/01/1996"));
//default To date
$this.filter(".pq-to").datepicker("option", "defaultDate", new Date("12/31/1998"));
}
//define colModel
var colM = [
{ title: "ShipCountry", width: 100, dataIndx: "ShipCountry",
filter: { type: 'textbox', condition: 'begin', listeners: ['change'] }
},
{ title: "Customer Name", width: 120, dataIndx: "ContactName",
filter: { type: 'textbox', condition: 'begin', listeners: ['change'] }
},
{ title: "Order ID", minWidth: 130, dataIndx: "OrderID", dataType: "integer",
filter: { type: 'textbox', condition: "between", listeners: ['change'] }
},
{ title: "Order Date", minWidth: "190", dataIndx: "OrderDate", dataType: "date",
filter: { type: 'textbox', condition: "between", init: pqDatePicker, listeners: ['change'] }
},
{ title: "Shipping Region", width: 130, dataIndx: "ShipRegion",
filter: { type: 'select',
//attr: "multiple", //for multiple
//style:"height:120px;",//for multiple
condition: 'equal',
//condition: 'range', //for multiple
valueIndx: "ShipRegion",
labelIndx: "ShipRegion",
groupIndx: "ShipCountry",
prepend: { '': '--Select--' },
listeners: ['change']
}
},
{ title: "Paid", width: 100, dataIndx: "paid", dataType: "bool", align: "center",
filter: { type: "checkbox", subtype: 'triple', condition: "equal", listeners: ['click'] }
},
{ title: "Shipping Via", width: 140, dataIndx: "ShipVia",
filter: { type: "select",
condition: 'equal',
prepend: { '': '--Select--' },
valueIndx: "ShipVia",
labelIndx: "ShipVia",
listeners: ['change']
}
},
{ title: "Required Date", width: 100, dataIndx: "RequiredDate", dataType: "date" },
{ title: "Shipped Date", width: 100, dataIndx: "ShippedDate", dataType: "date" },
{ title: "Freight", width: 100, align: "right", dataIndx: "Freight", dataType: "float" },
{ title: "Shipping Name", width: 150, dataIndx: "ShipName" },
{ title: "Shipping Address", width: 270, dataIndx: "ShipAddress" },
{ title: "Shipping City", width: 100, dataIndx: "ShipCity" },
{ title: "Shipping Postal Code", width: 180, dataIndx: "ShipPostalCode" }
];
//define dataModel
var dataModel = {
location: "remote",
sorting: "local",
dataType: "JSON",
method: "GET",
sortIndx: "OrderID",
sortDir: "up",
//url: "/pro/orders/get",
url: "getdata.php",//for PHP
getData: function (dataJSON) {
return { data: dataJSON.data };
}
}
var obj = { width: 1200, height: 800,
dataModel: dataModel,
colModel: colM,
pageModel: { type: 'local', rPP: 20 },
editable: false,
selectionModel: { type: 'cell' },
filterModel: { on: true, mode: "AND", header: true },
title: "Shipping Orders",
resizable: true,
hwrap:false,
freezeCols: 2
};
var $grid = $("#grid_filter").pqGrid(obj);
//load shipregion and shipvia dropdowns in first load event.
$grid.one("pqgridload", function (evt, ui) {
var column = $grid.pqGrid("getColumn", { dataIndx: "ShipRegion" });
var filter = column.filter;
filter.cache = null;
filter.options = $grid.pqGrid("getData", { dataIndx: ["ShipCountry", "ShipRegion"] });
var column = $grid.pqGrid("getColumn", { dataIndx: "ShipVia" });
var filter = column.filter;
filter.cache = null;
filter.options = $grid.pqGrid("getData", { dataIndx: ["ShipVia"] });
$grid.pqGrid("refreshHeader");
});
});
</script>
</head>
<body>
<div id="grid_filter" style="margin: 5px auto;"></div>
</body>
</html>
Code getdata.php
<?
class ColumnHelper
{
public static function isValidColumn($dataIndx)
{
if (preg_match('/^[a-z,A-Z]*$/', $dataIndx))
{
return true;
}
else
{
return false;
}
}
}
class FilterHelper
{
public static function deSerializeFilter($pq_filter)
{
$filterObj = json_decode($pq_filter);
$mode = $filterObj->mode;
$filters = $filterObj->data;
$fc = array();
$param= array();
foreach ($filters as $filter)
{
$dataIndx = $filter->dataIndx;
if (ColumnHelper::isValidColumn($dataIndx) == false)
{
throw new Exception("Invalid column name");
}
$text = $filter->value;
$condition = $filter->condition;
if ($condition == "contain")
{
$fc[] = $dataIndx . " like CONCAT('%', ?, '%')";
$param[] = $text;
}
else if ($condition == "notcontain")
{
$fc[] = $dataIndx . " not like CONCAT('%', ?, '%')";
$param[] = $text;
}
else if ($condition == "begin")
{
$fc[] = $dataIndx . " like CONCAT( ?, '%')";
$param[] = $text;
}
else if ($condition == "end")
{
$fc[] = $dataIndx . " like CONCAT('%', ?)";
$param[] = $text;
}
else if ($condition == "equal")
{
$fc[] = $dataIndx . " = ?";
$param[] = $text;
}
else if ($condition == "notequal")
{
$fc[] = $dataIndx . " != ?";
$param[] = $text;
}
else if ($condition == "empty")
{
$fc[] = "ifnull(" . $dataIndx . ",'')=''";
}
else if ($condition == "notempty")
{
$fc[] = "ifnull(" . $dataIndx . ",'')!=''";
}
else if ($condition == "less")
{
$fc[] = $dataIndx . " < ?";
$param[] = $text;
}
else if ($condition == "great")
{
$fc[] = $dataIndx . " > ?";
$param[] = $text;
}
}
$query = "";
if (sizeof($filters) > 0)
{
$query = " where " . join(" ".$mode." ", $fc);
}
$ds = new stdClass();
$ds->query = $query;
$ds->param = $param;
return $ds;
}
}//end of class
//orders.php
$filterQuery = "";
$filterParam = array();
if ( isset($_GET["pq_filter"]))
{
$pq_filter = $_GET["pq_filter"];
$dsf = FilterHelper::deSerializeFilter($pq_filter);
$filterQuery = $dsf->query;
$filterParam = $dsf->param;
}
$sql = "Select * from ordercustomers ".$filterQuery;
$DB_HOSTNAME = 'myhostname';
$DB_NAME = 'paramquery';
$DB_USERNAME = 'myusername';
$DB_PASSWORD = 'mypassword';
$dsn = 'mysql:host='.DB_HOSTNAME.';dbname='.DB_NAME;
$options = array(
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
);
$dbh = new PDO($dsn, DB_USERNAME, DB_PASSWORD, $options);
$stmt = $dbh->prepare($sql);
$stmt->execute($filterParam);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "{\"data\":". json_encode( $rows ) ." }" ;
?>
