CodeIgniter JQXWIDGETS

Hello Community,

Has anyone tried using jqwidgets grid?

I am using this for a server side paging. I am retrieving a large data from database so I need to user paging.

I have followed the tutorials on their website, and able to make it work. But only the pagesize. When i change the pagesize, it refreshes and pull out data from the database. Let say I selected 100, the grid will refresh and fetch 100 records from the database.

But the problem is in the pagenum. When I click the next or previous button, it seems that is not passing anything to the server.

Would you help me with this? I appreciate it so much. Let me show you my code.

// Controller:

PHP Code:

    public function fetchCustomers() {
            $pagenum =$this->input->post('pagenum');
            $pagesize =$this->input->post('pagesize');

            $start = $pagenum * $pagesize;
            $result = $this->indexmodel->fetchCustomers($pagenum, $pagesize);
            foreach ($result as $row) {
                $customers[] = [
                    'customerCode' => $row['customerCode'],
                    'customerName' => $row['customerName']
                ];
            }
            $data[] = [
                'TotalRows' => count($result),
                'Rows' => $customers
            ];
            echo json_encode($data);
    } 


// MODEL

PHP Code:

    public function fetchCustomers($pagenum, $pagesize) {
        $result = $this->db->query("fetchCustomers '$pagenum', '$pagesize'");
        return $result->result_array();
    } 


// JAVASCRIPT

Code:

$(document).ready(function() {
 // prepare the data
 var source =
 {
   type: 'POST',
   datatype: "json",
   datafields:
   [
     { name: 'customerCode', type: 'number' },
     { name: 'customerName', type: 'string' }
   ],
   url: baseUrl,
   root: 'Rows',
   beforeprocessing: function(data)
   {
     source.totalrecords = data[0].TotalRows;
   }
 };
 var dataadapter = new $.jqx.dataAdapter(source);
 console.log(dataadapter);
 $("#customers").jqxGrid({
       width: 600,
       source: dataadapter,
       autoheight: true,
       pageable: true,
       virtualmode: true,
       pagesizeoptions: ['50', '100', '250', '1000', '10000'],
       rendergridrows: function()
       {
           return dataadapter.records;
       },
     columns: [
       { text: 'Customer Code', datafield: 'customerCode', width: 250 },
       { text: 'Customer Name', datafield: 'customerName', width: 250 },
     ]
 });
});

// SQL QUERY

Code:

ALTER procedure [dbo].[fetchCustomers]
    @offset int,
    @limit int
as
begin

SELECT p.CustCode as customerCode,
      p.CustNm as customerName
FROM BigEMasterData.dbo.tbl_Customers as p
ORDER BY p.CustNm

OFFSET @offset ROWS
FETCH NEXT @limit ROWS ONLY

Sending thanks from Philippines
- DAN

As far I can see you never include pagesize or pagenum in your Ajax.

Look at the Network tab in your Browsers Dev tools. And look at the data you actually sends to your server every time you press a number.

As far I can see you never include pagesize or pagenum in your Ajax.

Look at the Network tab in your Browsers Dev tools. And look at the data you actually sends to your server every time you press a number.

Hi jreklund, yes i have already did that I checked the data passed on network tabs. What I know is that jqwidgets passes pagenum for me. I mean i dont have to define it in my code. Am not sure though. When I change the pagesize it is retrieving records from the server. But if i clicked the next button nothing is happening. Thankyou..

I think you need a handler in your javascript to respond to the next and previous buttons clicks i.e

Code:

$("#customers").on("pagechanged", function (event) {

See this jsFiddle for an example.

Hi,


Thank you for your suggestions, it makes me see what I might be doing wrong.


The problem is on my SQL query. It is not getting all the results. It just gets the results with the limit and offset applied.

Here is my new query. Hope it helps someone:

Code:

SELECT
 CustCode as customerCode,
 CustNm as customerName,
 overall_count = COUNT(*) OVER()
FROM BigEMasterData.dbo.tbl_Customers
ORDER BY CustCode
 OFFSET @PageNum ROWS
 FETCH NEXT @PageSize ROWS ONLY;

Where you put your SQL Query in codeigniter?

@danangeloalcanar,

By any chance do you test your SQL in MySQL Workbench (or any other MySQL tool)? This would help you to determine how effective your query is.