/**
* Return the result of the query to jqGrid. Support searching
* @param array $summary - set which columns should be sumarized in order to be displayed to the grid
* By default this parameter uses SQL SUM function: array("colmodelname"=>"sqlname");
* It can be set to use the other one this way
* array("colmodelname"=>array("sqlname"=>"AVG"));
* By default the first field correspond to the name of colModel the second to
* the database name
* @param array $params - parameter values passed to the sql
* @param boolen $echo if set to false return the records as object, otherwiese json encoded or xml string
* depending on the dataType variable
* @return mixed
*/
public function queryGrid(array $summary = null, array $params = null, $echo = true)
{
$sql = null;
$sqlId = $this->_setSQL();
if (!$sqlId) {
return false;
}
$page = $this->GridParams['page'];
$page = (int) jqGridUtils::GetParam($page, '1');
// get the requested page
$limit = $this->GridParams['rows'];
$limit = (int) jqGridUtils::GetParam($limit, '20');
// get how many rows we want to have into the grid
$sidx = $this->GridParams['sort'];
$sidx = jqGridUtils::GetParam($sidx, '');
// get index row - i.e. user click to sort
$sord = $this->GridParams['order'];
$sord = jqGridUtils::GetParam($sord, '');
// get the direction
$search = $this->GridParams['search'];
$search = jqGridUtils::GetParam($search, 'false');
// get the direction
$totalrows = jqGridUtils::GetParam($this->GridParams['totalrows'], '');
$sord = preg_replace("/[^a-zA-Z0-9]/", "", $sord);
$sidx = preg_replace("/[^a-zA-Z0-9. _,]/", "", $sidx);
$performcount = true;
$gridcnt = false;
$gridsrearch = '1';
if ($this->cacheCount) {
$gridcnt = jqGridUtils::GetParam('grid_recs', false);
$gridsrearch = jqGridUtils::GetParam('grid_search', '1');
if ($gridcnt && (int) $gridcnt >= 0) {
$performcount = false;
}
}
if ($search == 'true') {
if ($this->dbtype == 'mongodb') {
$params = jqGridDB::_mongoSearch($params, $this->GridParams, $this->encoding, $this->datearray, $this->mongointegers);
} else {
$sGrid = $this->_buildSearch($params);
if ($this->optimizeSearch === true || $this->dbtype == 'array') {
$whr = "";
if ($sGrid[0]) {
if (preg_match("/WHERE/i", $sqlId)) {
// to be refined
$whr = " AND " . $sGrid[0];
} else {
$whr = " WHERE " . $sGrid[0];
}
}
$sqlId .= $whr;
} else {
$whr = $sGrid[0] ? " WHERE " . $sGrid[0] : "";
$sqlId = "SELECT * FROM (" . $sqlId . ") gridsearch" . $whr;
}
$params = $sGrid[1];
if ($this->cacheCount && $gridsrearch != "-1") {
$tmps = crc32($whr . "data" . implode(" ", $params));
if ($gridsrearch != $tmps) {
$performcount = true;
}
$gridsrearch = $tmps;
}
}
} else {
if ($this->cacheCount && $gridsrearch != "-1") {
if ($gridsrearch != '1') {
$performcount = true;
}
}
}
$performcount = $performcount && $this->performcount;
if ($performcount) {
if ($this->dbtype == 'mongodb') {
$qryData = jqGridDB::_mongocount($sqlId, $params, $summary);
} else {
$qryData = $this->_getcount($sqlId, $params, $summary);
}
if (is_object($qryData)) {
if (!isset($qryData->count)) {
$qryData->count = null;
}
if (!isset($qryData->COUNT)) {
$qryData->COUNT = null;
}
$count = $qryData->COUNT ? $qryData->COUNT : ($qryData->count ? $qryData->count : 0);
} else {
$count = isset($qryData['COUNT']) ? $qryData['COUNT'] : 0;
}
} else {
$count = $gridcnt;
}
if ($count > 0) {
$total_pages = ceil($count / $limit);
} else {
$count = 0;
$total_pages = 0;
$page = 0;
}
if ($page > $total_pages) {
$page = $total_pages;
}
$start = $limit * $page - $limit;
// do not put $limit*($page - 1)
if ($start < 0) {
$start = 0;
}
if ($this->dbtype == 'sqlsrv' || $this->dbtype == 'odbcsqlsrv') {
$difrec = abs($start - $count);
if ($difrec < $limit) {
$limit = $difrec;
}
}
$result = new stdClass();
if (is_array($summary)) {
if (is_array($qryData)) {
unset($qryData['COUNT']);
} else {
unset($qryData->COUNT, $qryData->count);
}
foreach ($qryData as $k => $v) {
if ($v == null) {
$v = 0;
}
$result->userdata[$k] = $v;
}
}
if ($this->cacheCount) {
$result->userdata['grid_recs'] = $count;
$result->userdata['grid_search'] = $gridsrearch;
$result->userdata['outres'] = $performcount;
}
if ($this->userdata) {
if (!isset($result->userdata)) {
$result->userdata = array();
}
$result->userdata = jqGridUtils::array_extend($result->userdata, $this->userdata);
}
$result->records = $count;
$result->page = $page;
$result->total = $total_pages;
$uselimit = true;
if ($totalrows) {
$totalrows = (int) $totalrows;
if (is_int($totalrows)) {
if ($totalrows == -1) {
$uselimit = false;
} else {
if ($totalrows > 0) {
$limit = $totalrows;
}
}
}
}
// build search before order clause
if ($this->dbtype !== 'mongodb') {
if ($sidx) {
$sqlId .= " ORDER BY " . $sidx . " " . $sord;
}
}
$ret = $this->execute($sqlId, $params, $sql, $uselimit, $limit, $start, $sidx, $sord);
if ($ret) {
$result->rows = jqGridDB::fetch_object($sql, true, $this->pdo);
jqGridDB::closeCursor($sql);
if ($this->customClass) {
try {
$result = call_user_func(array($this->customClass, $this->customFunc), $result, $this->pdo);
} catch (Exception $e) {
echo "Can not call the method class - " . $e->getMessage();
}
} else {
if (function_exists($this->customFunc)) {
$result = call_user_func($this->customFunc, $result, $this->pdo);
}
}
if ($echo) {
$this->_gridResponse($result);
} else {
if ($this->debug) {
$this->debugout();
}
return $result;
}
} else {
echo "Could not execute query!!!";
}
if ($this->debug) {
$this->debugout();
}
}
<?php
require_once '../../../jq-config.php';
require_once ABSPATH . "php/jqGridPdo.php";
$country = $_GET['q'];
if ($country) {
try {
$conn = new PDO(DB_DSN, DB_USER, DB_PASSWORD);
$SQL = "SELECT DISTINCT(City) id, City value FROM customers WHERE Country='" . $country . "' ORDER BY City";
$collation = jqGridDB::query($conn, "SET NAMES utf8");
$city = jqGridDB::query($conn, $SQL);
$result = jqGridDB::fetch_object($city, true, $conn);
echo json_encode($result);
} catch (Exception $e) {
echo $e->getMessage();
}
}
public function queryGrid(array $summary = null, array $params = null, $echo = true)
{
$sql = null;
$sqlId = $this->_setSQL();
if (!$sqlId) {
return false;
}
$page = $this->GridParams['page'];
$page = (int) jqGridUtils::GetParam($page, '1');
$limit = $this->GridParams['rows'];
$limit = (int) jqGridUtils::GetParam($limit, '20');
$sidx = $this->GridParams['sort'];
$sidx = jqGridUtils::GetParam($sidx, '');
$sord = $this->GridParams['order'];
$sord = jqGridUtils::GetParam($sord, '');
$search = $this->GridParams['search'];
$search = jqGridUtils::GetParam($search, 'false');
$totalrows = jqGridUtils::GetParam($this->GridParams['totalrows'], '');
$sord = preg_replace("/[^a-zA-Z0-9]/", "", $sord);
$sidx = preg_replace("/[^a-zA-Z0-9. _,]/", "", $sidx);
$performcount = true;
$gridcnt = false;
$gridsrearch = '1';
if ($this->cacheCount) {
$gridcnt = jqGridUtils::GetParam('grid_recs', false);
$gridsrearch = jqGridUtils::GetParam('grid_search', '1');
if ($gridcnt && (int) $gridcnt >= 0) {
$performcount = false;
}
}
if ($search == 'true') {
$sGrid = $this->_buildSearch($sqlId, $params);
if ($this->optimizeSearch === true) {
$whr = "";
if ($sGrid[0]) {
if (preg_match("/WHERE/i", $sqlId)) {
$whr = " AND " . $sGrid[0];
} else {
$whr = " WHERE " . $sGrid[0];
}
}
$sqlId .= $whr;
} else {
$whr = $sGrid[0] ? " WHERE " . $sGrid[0] : "";
$sqlId = "SELECT * FROM (" . $sqlId . ") gridsearch" . $whr;
}
$params = $sGrid[1];
if ($this->cacheCount) {
$tmps = crc32($whr . "data" . implode(" ", $params));
if ($gridsrearch != $tmps) {
$performcount = true;
}
$gridsrearch = $tmps;
}
} else {
if ($this->cacheCount) {
if ($gridsrearch != '1') {
$performcount = true;
}
}
}
if ($performcount) {
$qryData = $this->_getcount($sqlId, $params, $summary);
if (!isset($qryData->count)) {
$qryData->count = null;
}
if (!isset($qryData->COUNT)) {
$qryData->COUNT = null;
}
$count = $qryData->COUNT ? $qryData->COUNT : ($qryData->count ? $qryData->count : 0);
} else {
$count = $gridcnt;
}
if ($count > 0) {
$total_pages = ceil($count / $limit);
} else {
$total_pages = 0;
$page = 0;
}
if ($page > $total_pages) {
$page = $total_pages;
}
$start = $limit * $page - $limit;
if ($start < 0) {
$start = 0;
}
if ($this->dbtype == 'sqlsrv') {
$difrec = abs($start - $count);
if ($difrec < $limit) {
$limit = $difrec;
}
}
if (is_array($summary)) {
unset($qryData->COUNT, $qryData->count);
foreach ($qryData as $k => $v) {
if ($v == null) {
$v = 0;
}
$result->userdata[$k] = $v;
}
}
if ($this->cacheCount) {
$result->userdata['grid_recs'] = $count;
$result->userdata['grid_search'] = $gridsrearch;
$result->userdata['outres'] = $performcount;
}
if ($this->userdata) {
if (!isset($result->userdata)) {
$result->userdata = array();
}
$result->userdata = jqGridUtils::array_extend($result->userdata, $this->userdata);
}
$result->records = $count;
$result->page = $page;
$result->total = $total_pages;
$uselimit = true;
if ($totalrows) {
$totalrows = (int) $totalrows;
if (is_int($totalrows)) {
if ($totalrows == -1) {
$uselimit = false;
} else {
if ($totalrows > 0) {
$limit = $totalrows;
}
}
}
}
if ($sidx) {
$sqlId .= " ORDER BY " . $sidx . " " . $sord;
}
$ret = $this->execute($sqlId, $params, $sql, $uselimit, $limit, $start, $sidx, $sord);
if ($ret === true) {
$result->rows = jqGridDB::fetch_object($sql, true, $this->pdo);
jqGridDB::closeCursor($sql);
if (function_exists($this->customFunc)) {
$result = call_user_func($this->customFunc, $result, $this->pdo);
}
if ($echo) {
$this->_gridResponse($result);
} else {
return $result;
}
} else {
echo "Could not execute query!!!";
}
if ($this->debug) {
$this->debugout();
}
}
