PDA

View Full Version : A basic Paging Grid Tutorial



Micha
1 Mar 2007, 6:18 AM
This litte basic Tutorial will show you how to create a Paging Grid with Ext-1.0-alpha2_rev5 and PHP on the Server Side. You need a local Webserver with PHP and MySQL like XAMPP or WAMP. I use PHP 5.2 . If you have a older PHP Version read Wolfgangs topic http://www.yui-ext.com/forum/viewtopic.php?t=2853.

This Tutorial is for local learning without any security stuff to keep it simple !

Ok, lets start. First we need some Data. Go to http://www.benjaminkeen.com/software/data_generator/, type name, country and zipcode in the Table Columns, select the datatypes, select SQL under Result type, add 100 Rows, check on "Include Create Table Query", Database Table Name set to "myTable" and Database to MySQL. Click "Generate!", create a Database "someData" on your Webserver and run the copied SQL Statement.
You have now a Database "someData", a Table "myTable" and the fields "id", "name", "country" and "zipcode" and 100 datasets.

First we create a html page "tutorial.html" like that:


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Paging Grid Example</title>

<link rel="stylesheet" type="text/css" href="../your_path_to/ext-1.0-alpha2/resources/css/ext-all.css" />

<script type="text/javascript" src="../your_path_to/ext-1.0-alpha2/yui-utilities.js"></script>
<script type="text/javascript" src="../your_path_to/ext-1.0-alpha2/ext-yui-adapter.js"></script>
<script type="text/javascript" src="../your_path_to/ext-1.0-alpha2/ext-all.js"></script>
<link rel="stylesheet" type="text/css" href="../your_path_to/ext-1.0-alpha2/resources/css/ytheme-aero.css" />

<script type="text/javascript" src="../your_path_to/tutorial.js"></script>
<style>
body {
margin: 40px 0 0 40px;
}
h1 {
margin-bottom: 20px;
}
</style>
</head>
<body>
<h1>My first Grid</h1>
<div id="tutorial-grid" style="border:1px solid #99bbe8;overflow: hidden; width: 600px; height: 300px; "></div>

</body>
</html>


The div with the id "tutorial-grid" is the placeholder for our Grid.

Now create a blank js file and name it "tutorial.js". The myGrid function create our Grid after the page is loaded.


//tutorial.js
var myGrid = function() {
return {
init: function() {
...
}
};
}();
Ext.EventManager.onDocumentReady(myGrid.init, myGrid, true);

Now we create the Data Store and the Json Reader. The Json Reader maps our Data to the Grid Colums. The root is the root from our Array, totalProperty tells the Reader how much Datarows we have. We need that for the Paging. Name is the Column Header Text, mapping tells the Raťader witch Datafield we want to use and type is the Datatype we need for correct Sorting.
Replace the ... in the myGrid function with the code:


//we enable the QuickTips for the later Pagebar
Ext.QuickTips.init();

// create the Data Store
var ds = new Ext.data.Store({
proxy: new Ext.data.HttpProxy({url: '../your_path_to/tutorial.php'}),
reader: new Ext.data.JsonReader({
root: 'myFriends',
totalProperty: 'totalCount',
id: 'id'
},
[
{name: 'ID', mapping: 'id', type: 'int'},
{name: 'Name', mapping: 'name', type: 'string'},
{name: 'Country', mapping: 'country', type: 'string'},
{name: 'ZipCode', mapping: 'zipcode', type: 'int'}
]),
// turn on remote sorting
remoteSort: true
});
//we set the deafault sort to the id ascending
ds.setDefaultSort('ID', 'asc');

// now we create the Grid Columns
var cm = new Ext.grid.ColumnModel([
{header: "ID", dataIndex: 'ID', width: 70, sortable: true, align: 'right'},
{header: "Name", dataIndex: 'Name', width: 100, sortable: true},
{header: "Country", dataIndex: 'Country', width: 200, sortable: true},
{header: "ZipCode", dataIndex: 'ZipCode', width: 70, sortable: true}
]);
// by default columns are sortable
cm.defaultSortable = true;

// create the grid
var grid = new Ext.grid.Grid('tutorial-grid', {
ds: ds,
cm: cm,
selModel: new Ext.grid.RowSelectionModel({singleSelect:true}),
enableColLock:true
});
// render the Grid
grid.render();

We have now a Grid and we need a Pagebar for the Grid.


var gridFoot = grid.getView().getFooterPanel(true);
// add a paging toolbar to the grid's footer
var pagbar = new Ext.PagingToolbar(gridFoot, ds, {pageSize: 12});

ds.load({params:{start:0, limit:12}});

params:{start:0, limit:12 means, we want to see the first 12 Datarows from our Database.
We build now the serverside Code. Creat a blank php file "tutorial.php".


//tutorial.php
<?php
// connect to the database
$db = mysql_connect("localhost", "user", "pass")
or die("Connection Error: " . mysql_error());

mysql_select_db("someData") or die("bla bla ...");

// count the Data
$result = mysql_query("SELECT COUNT(*) AS count FROM myTable");
$row = mysql_fetch_assoc($result);
$count = $row['count'];

$query = "SELECT id, name, country, zipcode FROM myTable";

$result = mysql_query($query) or die("SQL Error: " . mysql_error());

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$myFriends[] = array(
'id' => $row['id'],
'name' => $row['name'],
'country' => $row['country'],
'zipcode' => $row['zipcode']
);
}

$myData = array('myFriends' => $myFriends, 'totalCount' => $count);

echo json_encode($myData);

mysql_free_result($result);

mysql_close($db);
?>

Start now your tutorial.html.

http://i176.photobucket.com/albums/w172/dynamicinternet/tutorials/gridtable01.jpg

Looks nice, but Pageing is not working. Now we want to Sort the Name Column, but it does nothing. Why?
In Firebug we can see, sort is requesting Name.

http://i176.photobucket.com/albums/w172/dynamicinternet/tutorials/gridtable02.jpg

We have to add a bit more code to our tutorial.php.


$limit = $_POST['limit']; //the pagesize
$start = $_POST['start']; //Offset
$dir = $_POST['dir']; // DESC or ASC
$sort = $_POST['sort'];// the column

switch ($sort) {
case "ID": $orderby = "id"; break;
case "Name": $orderby = "name"; break;
case "Country": $orderby = "country"; break;
case "ZipCode": $orderby = "zipcode"; break;
}
$db = mysql_connect("localhost", ........

and the new SQL Statement is now:


$query = "SELECT id, name, country, zipcode
FROM myTable
ORDER BY $orderby $dir
LIMIT $start, $limit";

Ok. All works fine.
Some Ideas, how to extend the Tutorial? Feel free to add more functions to the Tutorial.

Animal
1 Mar 2007, 7:02 AM
You don't need this:



switch ($sort) {
case "ID": $orderby = "id"; break;
case "Name": $orderby = "name"; break;
case "Country": $orderby = "country"; break;
case "ZipCode": $orderby = "zipcode"; break;


Just set the "name" property to the SQL expression, and all will work.

Obviouslty, the "dataIndex" property in the ColumnModel must refer to that identifier too.

willogee
1 Mar 2007, 7:58 AM
I found this incredibly helpful... thank you!
Will

Bobafart
1 Mar 2007, 8:01 AM
yes me too
more tutorials like his would be awesome

jack.slocum
1 Mar 2007, 10:30 AM
This is great. Can I have your permission to put it on the new site?

Micha
1 Mar 2007, 10:41 AM
Yes Sir ! :D

pradeep_123
2 Mar 2007, 4:21 AM
Hi Micha,

Thanks a lot for that detailed tutorial, it's very helpful for peaple who are finding difficulty in creating a grid.

I did everything as u said, but I am getting a grid without any contents. I checked with firebug which shows that response is getting from the server.(it shows the complete JSOn data). and in the post it shows

dir ASC
limit 12
sort ID
start 0

What would be the problem?

Micha
2 Mar 2007, 4:56 AM
The post params are correct. Did you got a error in firebug? which php/json do you use?

pradeep_123
2 Mar 2007, 10:52 PM
I am using XAMPP version 1.4.15,

which consists of
Apache 2.0.54
MySQL 4.1.13
PHP 5.0.4 + PHP 4.4.0 + PEAR

I am using the same JS and PHP code which u posted in the tutorial(to get a grip) and also the same table structure u specified.

No error is showing in the firebug too.
Micha - can u just give me ur mail id so that I can send the files to u.

BernardChhun
3 Mar 2007, 5:49 AM
Hey Micha,

First of all Thanks for this little tutorial!! I'm used to the grid in the .33-.40 era but this new one is unknown to me so it was very helpful.
I've been playing around with it and I've just found a little mistyping bug here.

in your JsonReader declaration you mention the totalProperty being as count. But your php page returns totalCount in the JSONified array! Since the reader doesn't find the count property, the paging doesn't work at that moment. Changing either of them to be the same makes this tutorial shines!!

some screenshots before I changed both to totalCount:

http://i9.tinypic.com/2i1io3b.png

After:
http://i16.tinypic.com/30sbjn8.png

thanks again.

Bernard

all the files necessary for this tutorial to work are downloadable here : http://www.sendspace.com/file/x06wk4

Micha
3 Mar 2007, 6:26 AM
Yes Bernard, you are right! Sorry for the little bug, i have it correct on my local machine, dunno why the bug was here in the code.

var ds = new Ext.data.Store({
proxy: new Ext.data.HttpProxy({url: '../your_path_to/tutorial.php'}),
reader: new Ext.data.JsonReader({
root: 'myFriends',
totalProperty: 'totalCount',
id: 'id'
},
pradeep_123, maybe that is your answer. If not, see Bernards response picture, your json output to look like that.

Thanks for the replys.
Micha

humpdi
3 Mar 2007, 10:23 AM
very very brilliant!!! should be more of them on the new page, everybody whos starting will learn so much faster if theres a tuto like this! thanks a lot micha, could be proud of yourself :)

pradeep_123
4 Mar 2007, 10:12 PM
Thank u Bernard, Thank u very much,

My grid is working now, It's amazing to see that..
Micha thanks a lot for u too for providing such a nice tutorial......... great job

Frank
17 Mar 2007, 3:44 AM
Hi,Micha:
I'm using MS SQL.it doesn't support "Limit" of SQL statment, is there any idea to slove this "limit"?Thank you!

Animal
17 Mar 2007, 3:54 AM
You'll have to extract the required subset from the query result yourself then.

kilburn
17 Mar 2007, 12:17 PM
Err... Am I the only one seeing an SQL Injection vulnerability here? You should correct this after posting this tutorial anywhere "official" jack ;)

Micha
17 Mar 2007, 2:05 PM
one from my first lines:
This Tutorial is for local learning without any security stuff to keep it simple !
Yes, you are right, i would never use it online without validate the input.
Ive written it for a quick view, how things work together.

Micha

VinylFox
19 Mar 2007, 8:14 AM
Frank

you can use a combination of MS SQL's "top" functionality, and the data_seek functions for whatever server side language your using.

so the SQL would be something like...

SELECT TOP 30 col1, col2, col3 FROM sample_data

so say you wanted 10 results per page and your trying to get the 3rd page, you would use the sql above and use the data_seek functions in your server side language to move to row 20

It gets more inefficent the more you page forward in the dataset, but there are no other options to do this kind of thing in MS SQL that i know of.

Saeven
21 Mar 2007, 10:42 AM
How does one detect that a row on a grid has been clicked, and gather relevant information, perhaps the record associated to that row?

mysticav
22 Mar 2007, 3:05 PM
Thanks. The tutorial is very helpful. Specially when you mention about the POST data.

Anyway, I had some problems displaying the grid. So, I decided to modify and adapt your tutorial based on the example provided in the yui-ext library:
/yui-ext/examples/grid/paging.js

So, the result: a better looking grid with a floating label with display info.

Replace the html file data provided in the tutorial with
/yui-ext/examples/grid/paging.html

Be sure to point the include files in the html to the correct path (css files, library, etc)

Replace the tutorial.js with the code below.

You also need to set the correct php file path:

new Ext.data.HttpProxy({
url: '[yourpath]/tutorial.php'
})

Hope it helps.


/*
* Ext - JS Library 1.0 Alpha 2
* Copyright(c) 2006-2007, Jack Slocum.
*/

Ext.onReady(function(){

// define the "Topic" record, mapping json data to record fields
var Topic = Ext.data.Record.create([
{name: 'Name', mapping: 'name'},
{name: 'Zipcode', mapping: 'zipcode'},
{name: 'Country', mapping: 'country'}
]);

// create reader that reads into Topic records
var reader = new Ext.data.JsonReader({
root: 'myFriends',
totalProperty: 'totalCount',
id: 'id'
}, Topic);

// create the Data Store
var ds = new Ext.data.Store({
// load data file
proxy: new Ext.data.HttpProxy({
url: 'tutorial.php'
}),

// let it know about the reader
reader: reader,
// turn on remote sorting
remoteSort: true
});
ds.setDefaultSort('Name', 'asc');

// the column model has information about grid columns
// dataIndex maps the column to the specific data field in
// the data store
var cm = new Ext.grid.ColumnModel([{
id: 'topic', // id assigned so we can apply custom css (e.g. .x-grid-col-topic b { color:#333 })
header: "Name",
dataIndex: 'Name',
width: 200
},{
header: "Country",
dataIndex: 'Country',
width: 350

},{
header: "Zipcode",
dataIndex: 'Zipcode',
width: 70,
align: 'right'
}]);

// by default columns are sortable
cm.defaultSortable = true;

// create the editor grid
var grid = new Ext.grid.Grid('topic-grid', {
ds: ds,
cm: cm,
selModel: new Ext.grid.RowSelectionModel({singleSelect:true}),
enableColLock:false
});

// make the grid resizable, do before render for better performance
var rz = new Ext.Resizable('topic-grid', {
wrap:true,
minHeight:100,
pinned:true,
handles: 's'
});
rz.on('resize', grid.autoSize, grid);

// render it
grid.render();

var gridFoot = grid.getView().getFooterPanel(true);

// add a paging toolbar to the grid's footer
var paging = new Ext.PagingToolbar(gridFoot, ds, {pageSize: 12});

paging.add('-', {
pressed: true,
enableToggle:true,
text: 'Detailed View',
cls: 'x-btn-text-icon details'
});

// create a floating label with display info
var displayInfo = gridFoot.createChild({cls:'paging-info'});
ds.on('load', function(){
var count = ds.getCount();
var msg = count == 0 ?
"No topics to display" :
String.format(
'Displaying topics {0} - {1} of {2}',
paging.cursor+1, paging.cursor+count, ds.getTotalCount()
);
displayInfo.update(msg);
});

// trigger the data store load
ds.load({params:{start:0, limit:12}});

});

Darcobe
23 Mar 2007, 6:24 AM
Hello,

I am very new to this, so please be patient.

I have followed all the steps in the tutorial (at lead I think I have). I am getting the error:

Ext is not defined
[Break on this error] Ext.EventManager.onDocumentReady(myGrid.init, myGrid, true);

If firebug.

Here is the content of my tutorial.js file.
-----------------------------------------------------------------------------------------------------------


//tutorial.js
var myGrid = function() {
return {
init: function() {
//

//we enable the QuickTips for the later Pagebar
Ext.QuickTips.init();

// create the Data Store
var ds = new Ext.data.Store({
proxy: new Ext.data.HttpProxy({url:'tutorial.php'}),
reader: new Ext.data.JsonReader({
root: 'myFriends',
totalProperty: 'totalCount',
id: 'id'
},
[
{name: 'ID', mapping: 'id', type: 'int'},
{name: 'Name', mapping: 'name', type: 'string'},
{name: 'Country', mapping: 'country', type: 'string'},
{name: 'ZipCode', mapping: 'zipcode', type: 'int'}
]),
// turn on remote sorting
remoteSort: true
});
//we set the deafault sort to the id ascending
ds.setDefaultSort('ID', 'asc');

// now we create the Grid Columns
var cm = new Ext.grid.ColumnModel([
{header: "ID", dataIndex: 'ID', width: 70, sortable: true, align: 'right'},
{header: "Name", dataIndex: 'Name', width: 100, sortable: true},
{header: "Country", dataIndex: 'Country', width: 200, sortable: true},
{header: "ZipCode", dataIndex: 'ZipCode', width: 70, sortable: true}
]);
// by default columns are sortable
cm.defaultSortable = true;

// create the grid
var grid = new Ext.grid.Grid('tutorial-grid', {
ds: ds,
cm: cm,
selModel: new Ext.grid.RowSelectionModel({singleSelect:true}),
enableColLock:true
});
// render the Grid
grid.render();
var gridFoot = grid.getView().getFooterPanel(true);
// add a paging toolbar to the grid's footer
var pagbar = new Ext.PagingToolbar(gridFoot, ds, {pageSize: 12});

ds.load({params:{start:0, limit:12}});
//
}
};
}();
Ext.EventManager.onDocumentReady(myGrid.init, myGrid, true);

Have I placed it in the wrong spot, I have no data coming back.

mysticav
23 Mar 2007, 8:59 AM
Seems that you haven't included the libraries correctly. A simple way to check is with an alert. After you include the libraries :

<script type="text/javascript" src="[yourpath]/yui-utilities.js"></script>
<script type="text/javascript" src="[yourpath]/ext-yui-adapter.js"></script>
<script type="text/javascript" src="[yourpath]/ext-all.js"></script>
try this:

<script type="text/javascript">
alert(Ext);
</script>
you should get :

[object] [object]

If you get undefined, it means that you are not including corrrectly the .js files. Check the paths.

72
23 Apr 2007, 6:01 AM
Thank you for the great work...i just got the basics
=D>

vibez
11 Aug 2007, 10:34 AM
Thank you very much for the tutorial. Any chance of adding a section on updating the database through edits, or even filtering?

Stripeman
17 Sep 2008, 7:26 AM
var gridFoot = grid.getView().getFooterPanel(true);

This is an object... grid.getView().

but grid.getView().getFooterPanel(true); is not an object.

I am using a GridPanel does that make any difference ?

Thanks for any help

Stripeman
19 Sep 2008, 5:01 AM
bump ?

evant
19 Sep 2008, 5:08 AM
It's old code from 1.1. To add a toolbar just use the bbar property.

Condor
19 Sep 2008, 5:17 AM
Example for Ext 2.2:


var grid = new Ext.grid.Grid({
ds: ds,
cm: cm,
selModel: new Ext.grid.RowSelectionModel({singleSelect:true}),
//enableColLock:true -> Also not supported in Ext 2
bbar: new Ext.PagingToolbar({
store: ds,
pageSize: 12
})
});
grid.render('tutorial-grid');
ds.load({params:{start:0, limit:12}});

Stripeman
19 Sep 2008, 5:20 AM
DOH. 1.1 damn. ok.. cool and thanks for the Example Condor!

Stripeman
19 Sep 2008, 5:22 AM
hum... i should create another account and only ask the dumb questions from that account LOL
(if there was only a way to tell which were the dumb questions before the answers were given)

ps .. i might make note that this thread is sitting in the 2.x subform (not that that would've helped sitting in the 1.x subform lol)

sushmak_bs
12 Apr 2012, 10:09 PM
please can u tell where this code has to be pasted in tutorial.js

am able to see the grid but its not displaying any contents just displaying blank grid

please help me..
thanks in advance


Code:
var gridFoot = grid.getView().getFooterPanel(true);
// add a paging toolbar to the grid's footer
var pagbar = new Ext.PagingToolbar(gridFoot, ds, {pageSize: 12});


ds.load({params:{start:0, limit:12}});