ETC
jqWidgets의 jqxGrid : server side sorting and paging
투정이
2017. 10. 28. 14:25
jqWidgets의 jqxGrid paging
postgresql을 database로 사용
connect.php
<?php
# FileName="connect.php"
# for postgresql
$conn_string = "host=localhost dbname=northwind user=postgres password=qwerty"
?>
data_02.php
<?php
#Include the connect.php file
include ('../connect.php');
// Connect to the database
$dbconn = pg_connect($conn_string) or die('Could not connect: ' . pg_last_error());
// Initialize pagenum and pagesize
$pagenum = $_POST['pagenum'];
$pagesize = $_POST['pagesize'];
//$pagenum = 1;
//$pagesize = 10;
$start = $pagenum * $pagesize;
// prepare a query
if (isset($_POST['sortdatafield']))
{
$sortfield = $_POST['sortdatafield'];
$sortorder = $_POST['sortorder'];
if ($sortorder != '')
{
if ($sortorder == "desc")
{
$query = "SELECT CompanyName, ContactName, ContactTitle, Address, City, Country
FROM Customers ORDER BY " . $sortfield . " DESC OFFSET " . $start . " LIMIT " . $pagesize;
}
else if ($sortorder == "asc")
{
$query = "SELECT CompanyName, ContactName, ContactTitle, Address, City, Country
FROM Customers ORDER BY " . $sortfield . " ASC OFFSET " . $start . " LIMIT " . $pagesize;
}
}
else
{
$query = "SELECT SQL_CALC_FOUND_ROWS CompanyName, ContactName, ContactTitle, Address, City, Country
FROM Customers OFFSET " . $start . " LIMIT " . $pagesize;
}
}
else
{
$query = "SELECT CompanyName, ContactName, ContactTitle, Address, City, Country
FROM Customers OFFSET " . $start . " LIMIT " . $pagesize;
}
// Performing SQL query
$result = pg_query($query) or die('Query failed: ' . pg_last_error());
while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
//echo var_dump($line);
$customers[] = array(
'CompanyName' => $line["companyname"],
'ContactName' => $line["contactname"],
'ContactTitle' => $line["contacttitle"],
'Address' => $line["address"],
'City' => $line["city"],
'Country' => $line["country"]
);
}
// get the total rows.
$query = "SELECT count(*) AS total_rows FROM customers";
$result = pg_query($query) or die('Query failed: ' . pg_last_error());
$line = pg_fetch_array($result, null, PGSQL_ASSOC);
// create array
$data[] = array(
'TotalRows' => $line["total_rows"],
'Rows' => $customers
);
//echo var_dump($data);
echo json_encode($data);
// Free resultset
pg_free_result($result);
// Closing connection
pg_close($dbconn);
?>
jqxgrid_02.html
<!DOCTYPE html>
<html lang = "ko">
<head>
<title>jqWidgets jqxGrid 02</title>
<meta charset="UTF-8">
<!-- add one of the jQWidgets styles -->
<link rel="stylesheet" href="../jqwidgets-ver5.3.2/jqwidgets/styles/jqx.base.css" type="text/css" />
<link rel="stylesheet" href="../jqwidgets-ver5.3.2/jqwidgets/styles/jqx.darkblue.css" type="text/css" />
<link rel="stylesheet" href="../jqwidgets-ver5.3.2/jqwidgets/styles/jqx.classic.css" type="text/css" />
<!-- add the jQuery script -->
<script type="text/javascript" src="../jqwidgets-ver5.3.2/scripts/jquery-1.11.1.min.js"></script>
<!-- add the jQWidgets framework -->
<script type="text/javascript" src="/jqwidgets-ver5.3.2/jqwidgets/jqxcore.js"></script>
<!-- add one or more widgets -->
<script type="text/javascript" src="../jqwidgets-ver5.3.2/jqwidgets/jqxbuttons.js"></script>
<script type="text/javascript" src="../jqwidgets-ver5.3.2/jqwidgets/jqxscrollbar.js"></script>
<script type="text/javascript" src="../jqwidgets-ver5.3.2/jqwidgets/jqxmenu.js"></script>
<script type="text/javascript" src="../jqwidgets-ver5.3.2/jqwidgets/jqxcheckbox.js"></script>
<script type="text/javascript" src="../jqwidgets-ver5.3.2/jqwidgets/jqxlistbox.js"></script>
<script type="text/javascript" src="../jqwidgets-ver5.3.2/jqwidgets/jqxdropdownlist.js"></script>
<script type="text/javascript" src="../jqwidgets-ver5.3.2/jqwidgets/jqxdata.js"></script>
<script type="text/javascript" src="../jqwidgets-ver5.3.2/jqwidgets/jqxgrid.js"></script>
<script type="text/javascript" src="../jqwidgets-ver5.3.2/jqwidgets/jqxgrid.selection.js"></script>
<script type="text/javascript" src="../jqwidgets-ver5.3.2/jqwidgets/jqxgrid.pager.js"></script>
<script type="text/javascript" src="../jqwidgets-ver5.3.2/jqwidgets/jqxgrid.sort.js"></script>
<script type="text/javascript">
$(document).ready(function () {
// prepare the data
var theme = 'classic';
var source =
{
datatype: "json",
datafields:[
{ name: 'CompanyName', type: 'string'},
{ name: 'ContactName', type: 'string'},
{ name: 'ContactTitle', type: 'string'},
{ name: 'Address', type: 'string'},
{ name: 'City', type: 'string'},
{ name: 'Country', type: 'string'}
],
cache: false,
url: 'data_02.php',
type: "POST",
root: 'Rows',
beforeprocessing: function(data)
{
source.totalrecords = data[0].TotalRows;
},
sort: function()
{
// update the grid and send a request to the server.
$("#jqxgrid").jqxGrid('updatebounddata', 'sort');
}
};
var dataadapter = new $.jqx.dataAdapter(source);
// initialize jqxGrid
$("#jqxgrid").jqxGrid(
{
width: 800,
source: dataadapter,
theme: theme,
autoheight: true,
pageable: true,
virtualmode: true,
sortable: true,
rendergridrows: function(params)
{
return params.data;
},
columns: [
{ text: '회사명', datafield: 'CompanyName', width: 250 },
{ text: '연락처 이름', datafield: 'ContactName', width: 200 },
{ text: '직위', datafield: 'ContactTitle', width: 200 },
{ text: '주소', datafield: 'Address', width: 180 },
{ text: '도시', datafield: 'City', width: 100 },
{ text: '국가', datafield: 'Country', width: 140 }
]
});
});
</script>
</head>
<body class='default'>
<div id='jqxWidget'">
<div id="jqxgrid"></div>
</div>
</body>
</html>
결과 화면
data_02.php
jqxgrid_02.html