PDA

View Full Version : Paginator



diegotdai
25 May 2010, 3:10 AM
Hi all, my first post here and its about the paginator....

page to render the grid and paginator

index.php


<script type="text/javascript">
Ext.onReady(function(){
var store = new Ext.data.JsonStore({
url: 'getoptions.php',
root: 'options',
method: 'post',
totalProperty: 'total',
fields: [
{name: 'optionid', type: 'int'},
{name: 'optionname', type: 'string'},
{name: 'type', type: 'int'},
{name: 'supcontid', type: 'int'},
{name: 'supplier', type: 'string'},
{name: 'suprate', type: 'float'},
{name: 'inscontid', type: 'int'},
{name: 'installer', type: 'string'},
{name: 'insrate', type: 'float'}
]
});
store.setDefaultSort('optionname', 'asc');

var grid = new Ext.grid.GridPanel({
width:900,
height:500,
title:'Options',
store: store,
trackMouseOver:true,
disableSelection:true,
loadMask: true,

// grid columns
columns:[{
header: "Option",
dataIndex: 'optionname',
width: 250,
sortable: true
},{
header: "Supplier",
dataIndex: 'supplier',
width: 250,
sortable: true
},{
header: "Rate",
dataIndex: "suprate",
renderer: "usMoney",
width: 50,
align: "center",
sortable: true
},{
header: "Installer",
dataIndex: "installer",
width: 250,
sortable: true
},{
header: "Rate",
dataIndex: "insrate",
renderer: "usMoney",
width: 50,
sortable: true
}],

// paging bar on the bottom
tbar: new Ext.PagingToolbar({
pageSize: 10,
store: store,
displayInfo: true,
displayMsg: 'Displaying options {0} - {1} of {2}',
emptyMsg: "No options for this product"
})
});

// render the grid
grid.render('optgrid');

// trigger the data store load
store.load();
});
</script>
</head>
<body>
<div id="optgrid" style="margin: 50px 0px 0px 25px"></div>
</body>
getoptions.php


<?php

require_once '../Application/models/DbTable/Options.php';
require_once '../library/JSON.php';

$model = new options();
$start = ($_POST["start"] == null)? 0 : $_POST["start"];
$count = ($_POST["limit"] == null)? 20 : $_POST["limit"];
$result = $model->getOptProductRel(149, 1, $start, $count);
$totalRecords = mysql_num_rows($result);

for($i = 0, $numrows = $totalRecords; $i < $numrows; $i++){
$row = mysql_fetch_assoc($result);
$options[$i] = $row;
}

$json = json_encode($options);

echo '({"total":"'.$numrows.'","options":'.$json.'})';
?>
options.php


require_once('../library/DbClass.php');

/**
*
* CRUD Class for options table
*/
class options extends DbClass
{

/**
*
*
* Get all options
*/
function getOptions($start, $limit)
{
$query = "SELECT o.* FROM options o LIMIT $start, $limit";
$result = $this->runSql($query);
return $result;
}
}
Everything is rendered properly, the only thing is the paginator, which doesn't render only 10 records at a time.

I tried passing the start and limit params through store.load

store.load({
params:{
start: 0,
limit: 10
}
});

but it only returns 10 results, and If I set the limit to 100 for instance, it renders 100 rows in the grid, but it does calculate the number of pages needed for the results, 10 in that case, but it show all of them when the grid is loaded, and when I click next page button, it goes to the next page and show only 10 as specified on pageSize, but if the total results in the database are more then 100, it keeps going to the next set of results...something like

showing 1 - 100 of 100 // when the grid is first loaded.

but if you go back to the first page, it shows only 10 results as expected, but all the buttons go away, as if the query limit now was the 10 from pageSize...

Cheers guys

diegotdai
27 May 2010, 2:25 AM
Anyone been through that with the pagingtoolbar???

Condor
27 May 2010, 2:41 AM
I don't know PHP, but it looks like 'total' contains the number of rows in the current resultset (with limit) instead of the total number of rows (without limit).

diegotdai
27 May 2010, 5:12 AM
Hey Condor, Thx for the reply

I tried passing all the results from the table to $total...but in this specific table I got over 35k rows. and the script cashes after sometime. I tried limiting the results to 500, then the script works.

Im running a new query to get all results, something like.
$result = $model->getOptProductRel(149, 1, $start, $count);
$result1 = $model->getOptProdctRel(0, 500);
$totalRecords = mysql_num_rows($result1); // that returns 500 rows from 0

then the script runs, but the paginator shows like

showing 1 - 500 of 500 // When in the first load

then, as you go forward on the paging it keeps incrementing it 20 on both

showing 21 - 521 of 500 //// showing 542 - 542 of 500 and keeps incrementing 20 on the start and limit

CrazyEnigma
27 May 2010, 6:00 AM
One thing you didn't post is your response. This might help.

I forget, but does:


$totalRecords = mysql_num_rows($result);


return the count of the result or the total count of your query without start, limit.

As Condor stated, the total count must be the query count without start and limit.

diegotdai
27 May 2010, 12:57 PM
hey, Crazy

yeah, I explained it above, this specific table has over 35k results, if I run the query with no limit, then extjs stops working. and when I run the query with limit 0, 500 as above, then it works, but the paging is screw up. It increments 20(or whichever is the pageSize), on the start and limit. So when it first loads, it shows something like showing 0 - 500 of 500, then if I click to go next page it shows "showing 21 - 521 - of 500...

CrazyEnigma
27 May 2010, 1:55 PM
Ok, I got the picture. What I meant is your response, what does it look like? Yes, your 35000 records returned would turf any browser.

You don't send all rows, but you need to do perform two queries: one that returns the rows with the limit, and the other that returns the total count of your query without limit.

assuming you have 35000 records, requesting limit = 10 rows, start at record 456, sort: optionid, dir: "ASC"


{
"success":true,
"total": 35000,
"options": [
{"optionid": 456, "optionname": "option 456", ...},
{"optionid": 457, "optionname": "option 457", ...},
{"optionid": 458, "optionname": "option 458", ...},
{"optionid": 459, "optionname": "option 459", ...},
{"optionid": 460, "optionname": "option 460", ...},
{"optionid": 461, "optionname": "option 461", ...},
{"optionid": 462, "optionname": "option 462", ...},
{"optionid": 463, "optionname": "option 463", ...},
{"optionid": 464, "optionname": "option 464", ...},
{"optionid": 465, "optionname": "option 465", ...} // stops after ten records.
]
}


This is what your response should look like.

diegotdai
27 May 2010, 6:20 PM
Guys.....Thank you very much for the help.

when I changed my script to return all the results I also took out the method post, and was still trying to get the post from the page... thats why it was not reading and not working properly :P

Thx for the Help...

Post SOLVED :D

CrazyEnigma
27 May 2010, 6:57 PM
So what was the actual solution?

diegotdai
28 May 2010, 3:30 AM
was just that, when I was using the method post, I wasnt returning the total number of records in the table, then when I realized that, I also changed the method, and forgot to change that on the PHP. so I was still requesting POST on php but was sending GET.