Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

Server-Side Paging with jQuery Grid

, 17 Feb 2012
Rate this:
Please Sign up or sign in to vote.
Assume that you are working on a web page which lists the customers from a database. Assume that you need to display 10 records in a page. The database has 30,000 records consisting all the customers. In the above scenario, … Continue reading →

Assume that you are working on a web page which lists the customers from a database. Assume that you need to display 10 records in a page. The database has 30,000 records consisting all the customers. In the above scenario, it really makes sense to retrieve only the required records. for example, in the first page, retrieve 1 to 10 records. When the user clicks on “next” button, retrieve records 11 to 20 and so on. In this post, we will show you how to populate the jqxGrid on demand when the current page is changed. When a page is changed, the jqxGrid will request records in a specific range from the server. In reply to each request for information that the Grid plug-in makes to the server, it expects to get a well formed JSON object. Let’s see how to achieve that in a few steps.

1. The first step is to create the file we’ll connect with. We will call the file ‘connect.php’

<?php
# FileName="connect.php"
$hostname = "localhost";
$database = "northwind";
$username = "root";
$password = "";
?>

2. The second step is to create the file that will handle the queries. We will call the file data.php. The data.php file connects to the ‘Customers’ table from the Northwind Database and returns the data as JSON. Our goal is to send data to client in small pieces that the client requests, and respond when the page number or page size is changed by the user. In the implementation, we check for the ‘pagenum’ and ‘pagesize’ members which the Grid sends to the server and we use the values of these members to specify the range of records in the query to the database. We also make a query to find the total rows in the ‘Customers’ table. The returned JSON data contains two thinkgs- the total rows in the ‘Customers’ table and the requested records.

<?php
    #Include the connect.php file
    include('connect.php');
    #Connect to the database
    //connection String
    $connect = mysql_connect($hostname, $username, $password)
    or die('Could not connect: ' . mysql_error());
    //Select The database
    $bool = mysql_select_db($database, $connect);
    if ($bool === False){
       print "can't find $database";
    }
    // create the query.
    $pagenum = $_GET['pagenum'];
    $pagesize = $_GET['pagesize'];
    $start = $pagenum * $pagesize;
    $query = "SELECT SQL_CALC_FOUND_ROWS * FROM customers LIMIT $start, $pagesize";

    $result = mysql_query($query) or die("SQL Error 1: " . mysql_error());
    $sql = "SELECT FOUND_ROWS() AS `found_rows`;";
    $rows = mysql_query($sql);
    $rows = mysql_fetch_assoc($rows);
    $total_rows = $rows['found_rows'];
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
        $customers[] = array(
            'CompanyName' => $row['CompanyName'],
            'ContactName' => $row['ContactName'],
            'ContactTitle' => $row['ContactTitle'],
            'Address' => $row['Address'],
            'City' => $row['City'],
            'Country' => $row['Country']
          );
    }
    $data[] = array(
       'TotalRows' => $total_rows,
       'Rows' => $customers
    );
    echo json_encode($data);
?>

3. The final step is to create the Grid and bind it to the ‘Customers’ table. Our goal is to populate the Grid on demand when a page or the page’s size is changed.

Here’s the full source code:

<!DOCTYPE html>
<html lang="en">
<head>
    <link rel="stylesheet" href="styles/jqx.base.css" type="text/css" />
    <link rel="stylesheet" href="styles/jqx.classic.css" type="text/css" />
    <script type="text/javascript" src="jquery-1.7.1.min.js"></script>
    <script type="text/javascript" src="jqxcore.js"></script>
    <script type="text/javascript" src="jqxbuttons.js"></script>
    <script type="text/javascript" src="jqxscrollbar.js"></script>
    <script type="text/javascript" src="jqxmenu.js"></script>
    <script type="text/javascript" src="jqxcheckbox.js"></script>
    <script type="text/javascript" src="jqxlistbox.js"></script>
    <script type="text/javascript" src="jqxdropdownlist.js"></script>
    <script type="text/javascript" src="jqxgrid.js"></script>
    <script type="text/javascript" src="jqxgrid.pager.js"></script>
    <script type="text/javascript" src="jqxgrid.selection.js"></script>
    <script type="text/javascript" src="jqxdata.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            // prepare the data
            var theme = 'classic';

            var source =
            {
                 datatype: "json",
                 datafields: [
                     { name: 'CompanyName'},
                     { name: 'ContactName'},
                     { name: 'ContactTitle'},
                     { name: 'Address'},
                     { name: 'City'},
                     { name: 'Country'}
                ],
                url: 'data.php',
                root: 'Rows',
                beforeprocessing: function(data)
                {
                    source.totalrecords = data[0].TotalRows;
                }
            };        

            var dataadapter = new $.jqx.dataAdapter(source);

            // initialize jqxGrid
            $("#jqxgrid").jqxGrid(
            {
                width: 600,
                source: dataadapter,
                theme: theme,
                autoheight: true,
                pageable: true,
                virtualmode: true,
                rendergridrows: function()
                {
                      return dataadapter.records;
                },
                columns: [
                      { text: 'Company Name', datafield: 'CompanyName', width: 250 },
                      { text: 'Contact Name', datafield: 'ContactName', width: 200 },
                      { text: 'Contact Title', datafield: 'ContactTitle', width: 200 },
                      { text: 'Address', datafield: 'Address', width: 180 },
                      { text: 'City', datafield: 'City', width: 100 },
                      { text: 'Country', datafield: 'Country', width: 140 }
                  ]
            });
        });
    </script>
</head>
<body class='default'>
    <div id='jqxWidget'">
        <div id="jqxgrid"></div>
    </div>
</body>
</html>

Let’s see how the above code works. As we need to populate the Grid on demand, we set its ‘virtualmode’ property to true. This means that the Grid will display only the records returned as array from the ‘rendergridrows’ callback function. The Grid will make requests to the server when the user clicks the ‘Next’ or ‘Previous’ buttons or changes the page’s size. We also set the source object’s totalrecords property to the TotalRows value returned from the server. In the source object’s initialization we set the ‘datatype’ to ‘json’ as the returned data will be JSON data and the ‘root’ member to ‘Rows’ as the records are stored in the ‘Rows’ array.
jquery grid server side paging

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

jqwidgets
jQWidgets
United States United States
jQWidgets specializes in the development of platform independent and cross-browser compatible presentation layer components for building modern web-based applications for PC, Touch and Mobile. Our product provides developers with the essential set of User Interface widgets needed to streamline their development and reduce project costs.
Our goal is to help our customers deliver better web-based applications that can be accessed through any device and are pleasure to use.
Group type: Organisation

13 members

Follow on   Twitter

Comments and Discussions

 
QuestionSource Code Pinmemberbhavyagowda22-Jun-12 21:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140926.1 | Last Updated 17 Feb 2012
Article Copyright 2012 by jqwidgets
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid