jQWidgets Forums

This topic contains 8 replies, has 2 voices, and was last updated by webwired 11 years, 7 months ago.

Viewing 9 posts - 1 through 9 (of 9 total)

Author

sorting and paging breaks Grid Posts

October 25, 2013 at 10:50 pm sorting and paging breaks Grid #31360

Member

Hello everyone, I got the Grid working, but then I wanted to add sorting and paging, I followed the examples, and when I view the data.php page by itself, it seems to be working fine, but on the Grid page, the Grid doesn’t load at all now…

Here’s my data.php page code…

<?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 database
mysql_select_db($database, $connect);
//Select The database
$bool = mysql_select_db($database, $connect);
if ($bool === False){
	print "can't find $database";
}
// get data and store in a json array
$pagenum = $_GET['pagenum'];
$pagesize = $_GET['pagesize'];
$start = $pagenum * $pagesize;
$query = "SELECT SQL_CALC_FOUND_ROWS * FROM OrderTickets WHERE ((ExaminerID = 0 OR ExaminerID = 2) AND OrderTicketCurrentStatus = 'Completed')";
if (isset($_GET['sortdatafield']))
{
        $sortfield = $_GET['sortdatafield'];
        $sortorder = $_GET['sortorder'];
        $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'];
        if ($sortfield != NULL)
        {
                if ($sortorder == "desc")
                {
                        $query = "SELECT OrderTickets.*, Agencies.AgencyName, Agents.AgentFirstName, Agents.AgentLastName, InsuranceCarriers.InsuranceCarrier
                            FROM OrderTickets
                            LEFT JOIN Agencies ON OrderTickets.AgencyID = Agencies.AgencyID
                            LEFT JOIN Agents ON OrderTickets.AgentID = Agents.AgentID
                            LEFT JOIN InsuranceCarriers ON OrderTickets.InsuranceCarrierID = InsuranceCarriers.InsuranceCarrierID
                            WHERE ((ExaminerID = 0 OR ExaminerID = 2) AND OrderTicketCurrentStatus = 'Completed') ORDER BY" . " " . $sortfield . " DESC LIMIT $start, $pagesize";
                }
                else if ($sortorder == "asc")
                {
                        $query = "SELECT OrderTickets.*, Agencies.AgencyName, Agents.AgentFirstName, Agents.AgentLastName, InsuranceCarriers.InsuranceCarrier
                            FROM OrderTickets
                            LEFT JOIN Agencies ON OrderTickets.AgencyID = Agencies.AgencyID
                            LEFT JOIN Agents ON OrderTickets.AgentID = Agents.AgentID
                            LEFT JOIN InsuranceCarriers ON OrderTickets.InsuranceCarrierID = InsuranceCarriers.InsuranceCarrierID
                            WHERE ((ExaminerID = 0 OR ExaminerID = 2) AND OrderTicketCurrentStatus = 'Completed') ORDER BY" . " " . $sortfield . " ASC LIMIT $start, $pagesize";
                }			
                $result = mysql_query($query) or die("SQL Error 1: " . mysql_error());
        }
}
else
{
        $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(
        'ClientName' => $row['OrderTicketFirstName'] .' '. $row['OrderTicketLastName'],
        'AgencyName' => $row['AgencyName'],
	'AgentName' => $row['AgentFirstName'] .' '. $row['AgentLastName'],
	'InsuranceCarrier' => $row['InsuranceCarrier'],
	'OrderTicketDateReceived' => $row['OrderTicketDateReceived'],
	'OrderTicketDateCompleted' => $row['OrderTicketDateCompleted']
      );
}
$data[] = array(
    'TotalRows' => $total_rows,
    'Rows' => $customers
    );
echo json_encode($data);
?>

Here’s my Grid page code…

<? 
require 'includes/auth.php';
$ExaminerID = $_SESSION['ExaminerID'];
?>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
        <link href="StyleSheet.css" rel="stylesheet" type="text/css" />
        <link rel="shortcut icon" href="images/favicon.ico" />
        
        <link rel="stylesheet" href="../jqwidgets-ver3.0.3/jqwidgets/styles/jqx.base.css" type="text/css" />
        <link rel="stylesheet" href="../jqwidgets-ver3.0.3/jqwidgets/styles/jqx.classic.css" type="text/css" />
        <script type="text/javascript" src="../jqwidgets-ver3.0.3/scripts/jquery-1.10.2.min.js"></script>  
        <script type="text/javascript" src="../jqwidgets-ver3.0.3/jqwidgets/jqxcore.js"></script>
        <script type="text/javascript" src="../jqwidgets-ver3.0.3/jqwidgets/jqxbuttons.js"></script>
        <script type="text/javascript" src="../jqwidgets-ver3.0.3/jqwidgets/jqxscrollbar.js"></script>
        <script type="text/javascript" src="../jqwidgets-ver3.0.3/jqwidgets/jqxmenu.js"></script>
        <script type="text/javascript" src="../jqwidgets-ver3.0.3/jqwidgets/jqxgrid.js"></script>
        <script type="text/javascript" src="../jqwidgets-ver3.0.3/jqwidgets/jqxgrid.pager.js"></script>
        <script type="text/javascript" src="../jqwidgets-ver3.0.3/jqwidgets/jqxgrid.selection.js"></script>
        <script type="text/javascript" src="../jqwidgets-ver3.0.3/jqwidgets/jqxgrid.sort.js"></script>
        <script type="text/javascript" src="../jqwidgets-ver3.0.3/jqwidgets/jqxdata.js"></script>
        
        <script type="text/javascript">
            $(document).ready(function () {
                // prepare the data
                var theme = 'classic';
                
                var source =
                {
                    datatype: "json",
                    datafields: [
                        { name: 'ClientName', type: 'string'},
                        { name: 'AgencyName', type: 'string'},
                        { name: 'AgentName', type: 'string'},
                        { name: 'InsuranceCarrier', type: 'string'},
                        { name: 'OrderTicketDateReceived', type: 'date'},
                        { name: 'OrderTicketDateCompleted', type: 'date'}
                    ],
                    url: 'data.php',
                    cache: false,
                    root: 'Rows',
                    beforeprocessing: function(data)
                    {
                        source.totalrecords = data[0].TotalRows;
                    },
                    sort: function()
                    {
                        $("jqxgrid").jqxGrid('updatebounddata', 'sort');
                    }
                };
                var dataAdapter = new $.jqx.dataAdapter(source);
                $("#jqxgrid").jqxGrid(
                {
                    width: 900,
                    source: dataAdapter,
                    theme: theme,
                    autoheight: true,
                    pageable: true,
                    virtualmode: true,
                    sortable: true,
                    rendergridrows: function()
                    {
                        return dataadapter.records;     
                    },
                    columns: [
                      { text: 'Client Name', datafield: 'ClientName', width: 150},
                      { text: 'Agency', datafield: 'AgencyName', width: 200 },
                      { text: 'Agent', datafield: 'AgentName', width: 150 },
                      { text: 'Carrier', datafield: 'InsuranceCarrier', width: 200 },
                      { text: 'Date Received', datafield: 'OrderTicketDateReceived', width: 100, cellsformat: 'MM-dd-yyyy' },
                      { text: 'Date Completed', datafield: 'OrderTicketDateCompleted', width: 100, cellsformat: 'MM-dd-yyyy' }
                  ]
                });        
            });
        </script>
    
        <title>Order Tickets</title>
    </head>
    <body class='default'>
        <div id="LogoHeader">
            <img src="images/logo.png" />
        </div>
        <div id="Menu">
            <? include 'includes/menu.php'; ?>
        </div>
        <div style="margin-left: auto; margin-right: auto; width: 900px;">
            <div id="jqxWidget">
                <div id="jqxgrid"></div>
            </div>
        </div>
    </body>
</html>

October 25, 2013 at 10:54 pm sorting and paging breaks Grid #31361

Peter Stoev

Keymaster

Hi webwired,

Here’s an online working sample with Server Paging, Sorting and Filtering – http://www.jqwidgets.com/jquery-widgets-demo/demos/php/serverfiltering_paging_and_sorting.htm?arctic. I suggest you to check it out and especially which JavaScript and CSS references you should include in your web page.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com/

October 25, 2013 at 11:03 pm sorting and paging breaks Grid #31362

webwired

Member

Peter, I figured out how to add code to the forum page, does it still look like I’m missing something in regards to references?

October 25, 2013 at 11:07 pm sorting and paging breaks Grid #31364

Peter Stoev

Keymaster

Hi webwired,

As far as I see, not all required files are included. I suggest you to look at the sample I posted and also to check your browser’s console.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com/

October 26, 2013 at 12:02 am sorting and paging breaks Grid #31367

webwired

Member

Peter, thank you… I got the data to display in the Grid, but the sorting and paging is having problems…

The problem that I think the Sorting has, is because I have ‘ClientName’ put together from two different columns in the table, OrderTicketFirstName & OrderTicketLastName … I have the same thing going on for the ‘AgentName’, it is put together from AgentFirstName & AgentLastName … So that when it goes to sort, that column name doesn’t exist… I need some way to be able to fix that, any ideas?

As far as the Paging goes, on the bottom when I click next or previous, it changes the records on the bottom row, but it doesn’t update the Grid with the new rows… Any ideas on that as well would be great?

I left out Filtering from your example because I didn’t want to use it…

Anyway, here is my data.php page code…

<?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 database
mysql_select_db($database, $connect);
//Select The database
$bool = mysql_select_db($database, $connect);
if ($bool === False){
	print "can't find $database";
}
// get data and store in a json array
$pagenum = $_GET['pagenum'];
$pagesize = $_GET['pagesize'];
$start = $pagenum * $pagesize;
$query = "SELECT SQL_CALC_FOUND_ROWS OrderTickets.*, Agencies.AgencyName, Agents.AgentFirstName, Agents.AgentLastName, InsuranceCarriers.InsuranceCarrier 
            FROM OrderTickets 
            LEFT JOIN Agencies ON OrderTickets.AgencyID = Agencies.AgencyID
            LEFT JOIN Agents ON OrderTickets.AgentID = Agents.AgentID
            LEFT JOIN InsuranceCarriers ON OrderTickets.InsuranceCarrierID = InsuranceCarriers.InsuranceCarrierID
            WHERE ((ExaminerID = 0 OR ExaminerID = 2) AND OrderTicketCurrentStatus = 'Completed')";
if (isset($_GET['sortdatafield']))
{
        $sortfield = $_GET['sortdatafield'];
        $sortorder = $_GET['sortorder'];
        $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'];
        if ($sortfield != NULL)
        {
                if ($sortorder == "desc")
                {
                        $query = "SELECT OrderTickets.*, Agencies.AgencyName, Agents.AgentFirstName, Agents.AgentLastName, InsuranceCarriers.InsuranceCarrier
                            FROM OrderTickets
                            LEFT JOIN Agencies ON OrderTickets.AgencyID = Agencies.AgencyID
                            LEFT JOIN Agents ON OrderTickets.AgentID = Agents.AgentID
                            LEFT JOIN InsuranceCarriers ON OrderTickets.InsuranceCarrierID = InsuranceCarriers.InsuranceCarrierID
                            WHERE ((ExaminerID = 0 OR ExaminerID = 2) AND OrderTicketCurrentStatus = 'Completed') ORDER BY" . " " . $sortfield . " DESC LIMIT $start, $pagesize";
                }
                else if ($sortorder == "asc")
                {
                        $query = "SELECT OrderTickets.*, Agencies.AgencyName, Agents.AgentFirstName, Agents.AgentLastName, InsuranceCarriers.InsuranceCarrier
                            FROM OrderTickets
                            LEFT JOIN Agencies ON OrderTickets.AgencyID = Agencies.AgencyID
                            LEFT JOIN Agents ON OrderTickets.AgentID = Agents.AgentID
                            LEFT JOIN InsuranceCarriers ON OrderTickets.InsuranceCarrierID = InsuranceCarriers.InsuranceCarrierID
                            WHERE ((ExaminerID = 0 OR ExaminerID = 2) AND OrderTicketCurrentStatus = 'Completed') ORDER BY" . " " . $sortfield . " ASC LIMIT $start, $pagesize";
                }			
                $result = mysql_query($query) or die("SQL Error 1: " . mysql_error());
        }
}
else
{
        $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(
        'ClientName' => $row['OrderTicketFirstName'] .' '. $row['OrderTicketLastName'],
        'AgencyName' => $row['AgencyName'],
	'AgentName' => $row['AgentFirstName'] .' '. $row['AgentLastName'],
	'InsuranceCarrier' => $row['InsuranceCarrier'],
	'OrderTicketDateReceived' => $row['OrderTicketDateReceived'],
	'OrderTicketDateCompleted' => $row['OrderTicketDateCompleted']
      );
}
$data[] = array(
    'TotalRows' => $total_rows,
    'Rows' => $customers
    );
echo json_encode($data);
?>

And here is my Grid page code…

<? 
require 'includes/auth.php';
$ExaminerID = $_SESSION['ExaminerID'];
?>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
        <link href="StyleSheet.css" rel="stylesheet" type="text/css" />
        <link rel="shortcut icon" href="images/favicon.ico" />
        
        <link rel="stylesheet" href="../jqwidgets-ver3.0.3/jqwidgets/styles/jqx.base.css" type="text/css" />
        <script type="text/javascript" src="../jqwidgets-ver3.0.3/scripts/jquery-1.10.2.min.js"></script>  
        <script type="text/javascript" src="../jqwidgets-ver3.0.3/jqwidgets/jqxcore.js"></script>
        <script type="text/javascript" src="../jqwidgets-ver3.0.3/jqwidgets/jqxbuttons.js"></script>
        <script type="text/javascript" src="../jqwidgets-ver3.0.3/jqwidgets/jqxscrollbar.js"></script>
        <script type="text/javascript" src="../jqwidgets-ver3.0.3/jqwidgets/jqxmenu.js"></script>
        <script type="text/javascript" src="../jqwidgets-ver3.0.3/jqwidgets/jqxgrid.js"></script>
        <script type="text/javascript" src="../jqwidgets-ver3.0.3/jqwidgets/jqxgrid.selection.js"></script>
	<script type="text/javascript" src="../jqwidgets-ver3.0.3/jqwidgets/jqxgrid.filter.js"></script>
        <script type="text/javascript" src="../jqwidgets-ver3.0.3/jqwidgets/jqxgrid.sort.js"></script>
        <script type="text/javascript" src="../jqwidgets-ver3.0.3/jqwidgets/jqxdata.js"></script>
	<script type="text/javascript" src="../jqwidgets-ver3.0.3/jqwidgets/jqxlistbox.js"></script>
        <script type="text/javascript" src="../jqwidgets-ver3.0.3/jqwidgets/jqxgrid.pager.js"></script>	
	<script type="text/javascript" src="../jqwidgets-ver3.0.3/jqwidgets/jqxdropdownlist.js"></script>	
 	<script type="text/javascript" src="../jqwidgets-ver3.0.3/scripts/gettheme.js"></script>
        
        <script type="text/javascript">
            $(document).ready(function () {
                // prepare the data
                var theme = 'classic';
                
                var source =
                {
                    datatype: "json",
                    datafields: [
                        { name: 'ClientName', type: 'string'},
                        { name: 'AgencyName', type: 'string'},
                        { name: 'AgentName', type: 'string'},
                        { name: 'InsuranceCarrier', type: 'string'},
                        { name: 'OrderTicketDateReceived', type: 'date'},
                        { name: 'OrderTicketDateCompleted', type: 'date'}
                    ],
                    url: 'data.php',
                    cache: false,
                    filter: function()
                    {
                        // update the grid and send a request to the server.
                        $("#jqxgrid").jqxGrid('updatebounddata', 'filter');
                    },
                    sort: function()
                    {
                        // update the grid and send a request to the server.
                        $("#jqxgrid").jqxGrid('updatebounddata', 'sort');
                    },
                    root: 'Rows',
                    beforeprocessing: function(data)
                    {
                        if (data != null)
                        {
                            source.totalrecords = data[0].TotalRows;
                        }
                    }
                };
		var dataAdapter = new $.jqx.dataAdapter(source);
                $("#jqxgrid").jqxGrid(
                {
                    width: 900,
                    source: dataAdapter,
                    theme: theme,
                    autoheight: true,
                    pageable: true,
                    virtualmode: true,
                    sortable: true,
                    rendergridrows: function(obj)
                    {
                        return obj.data;   
                    },
                    columns: [
                      { text: 'Client Name', datafield: 'ClientName', width: 150},
                      { text: 'Agency', datafield: 'AgencyName', width: 200 },
                      { text: 'Agent', datafield: 'AgentName', width: 150 },
                      { text: 'Carrier', datafield: 'InsuranceCarrier', width: 200 },
                      { text: 'Date Received', datafield: 'OrderTicketDateReceived', width: 100, cellsformat: 'MM-dd-yyyy' },
                      { text: 'Date Completed', datafield: 'OrderTicketDateCompleted', width: 100, cellsformat: 'MM-dd-yyyy' }
                  ]
                });        
            });
        </script>
    
        <title>Order Tickets</title>
    </head>
    <body class='default'>
        <div id="LogoHeader">
            <img src="images/logo.png" />
        </div>
        <div id="Menu">
            <? include 'includes/menu.php'; ?>
        </div>
        <div style="margin-left: auto; margin-right: auto; width: 900px;">
            <div id="jqxgrid"></div>
        </div>
    </body>
</html>

October 26, 2013 at 12:15 am sorting and paging breaks Grid #31368

webwired

Member

I changed the source a bit… Here’s that new code…

var source =
{
    datatype: "json",
    datafields: [
        { name: 'ClientName', type: 'string'},
        { name: 'AgencyName', type: 'string'},
        { name: 'AgentName', type: 'string'},
        { name: 'InsuranceCarrier', type: 'string'},
        { name: 'OrderTicketDateReceived', type: 'date'},
        { name: 'OrderTicketDateCompleted', type: 'date'}
    ],
    url: 'data.php',
    cache: false,
    root: 'Rows',
    pager: function (pagenum, pagesize, oldpagenum) {
        // callback called when a page or page size is changed.
    },
    sort: function()
    {
        // update the grid and send a request to the server.
        $("#jqxgrid").jqxGrid('updatebounddata', 'sort');
    },
    beforeprocessing: function(data)
    {
        if (data != null)
        {
            source.totalrecords = data[0].TotalRows;
        }
    }
};

October 26, 2013 at 12:27 am sorting and paging breaks Grid #31369

webwired

Member

Ok, I figured out the paging… That’s all cleared up, I just have to figure out something for the sorting…

October 26, 2013 at 12:33 am sorting and paging breaks Grid #31370

webwired

Member

Well, got the sorting figured out as well… just made this change inside of the data.php page code…

        $sortfield = $_GET['sortdatafield'];
        if ($sortfield == "ClientName") {
            $sortfield = "OrderTicketLastName";
        }
        if ($sortfield == "AgentName") {
            $sortfield = "AgentLastName";
        }

October 26, 2013 at 12:39 am sorting and paging breaks Grid #31371

webwired

Member

This post is resolved.

Author

Posts

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic.