/** * 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(); } }