Sencha Inc. | HTML5 Apps

Ext JS Grids with PHP and SQL

Published Mar 25, 2008 | Nicolas Bize | Tutorial | Medium
Last Updated Jul 11, 2011

This Tutorial is most relevant to Ext JS, 2.x, 3.x.

This tutorial is intended for those for whom the words 'ColumnModel' or 'DataStore' sound japanese. It walks through the very basics of building of an Ext grid that has a PHP / SQL backend. There are numerous articles and forum threads on this subject. Yet, after much pain of building my own workable grid, and seeing the amount of newbie threads still addressing this area of Ext, I decided to take some time and give a little contribution to those tutorials.

Setting up your PHP / SQL backend

I assume that you already know a little bit about PHP / SQL. I'll stick with the basics in this tutorial. Ok, let's set up some very exciting SQL tables! We are going to do a little US History, as we'll be dealing with the different presidents of the USA. I have created two tables, PRESIDENTS and PARTIES, and added in all but one president. (we'll add the last one later on in this tutorial).

icon_folder.jpgDownload the SQL file Make sure you run that SQL file before pursuing, we'll need the database to be ready.

  • Creating two tables in "tutorial" database using pdts_usal.sql file :
[me@mysql-server ~]$ mysqlshow
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| tutorial           |
+--------------------+
[me@mysql-server ~]$  mysql tutorial   < pdts_usa.sql
[me@mysql-server ~]$
  • Verification.
[tjyang@ibm ~]$ mysql -u root -p myrootpassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 328
Server version: 5.0.67 Source distribution
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| tutorial           |
+--------------------+
4 rows in set (0.01 sec)
 
mysql> connect tutorial;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Connection id:    329
Current database: tutorial
 
mysql> show tables;
+--------------------+
| Tables_in_tutorial |
+--------------------+
| parties            |
| presidents         |
+--------------------+
2 rows in set (0.00 sec)
 
mysql>

Now we are sure mysql server is up and running with "tutoria" database and has two tables we can operate(query,add,delete,modify) upon.

JSON Format

Before heading right into our PHP file, you need to be aware of what's called the JSON format. I guess I could direct you to wikipedia's definition. JSON is basically a way to format data, just like would XML or CSV... Except that it is usually a lot smaller than XML.

Example of JSON Menu :

{"menu": {
   "id": "file",
   "value": "File",
   "popup": {
     "menuitem": [
       {"value": "New", "onclick": "CreateNewDoc()"},
       {"value": "Open", "onclick": "OpenDoc()"},
       {"value": "Close", "onclick": "CloseDoc()"}
     ]
   }
 }}


The same in XML :

<menu id="file" value="File">
   <popup>
     <menuitem value="New" onclick="CreateNewDoc()" />
     <menuitem value="Open" onclick="OpenDoc()" />
     <menuitem value="Close" onclick="CloseDoc()" />
   </popup>
 </menu>

That saved a lot of space huh? Well, it actually does with big arrays... Anyways, Ext accepts JSON as well as XML. For now, we'll concentrate on JSON, since most examples you'll likely see will use that format. Let's keep in mind that we'll have to send a JSON formatted array back to our ext script. We'll let our PHP file do that through a function called JEncode().

The PHP File

Ok, here comes our server script. Let's walk through it...

First, the server needs to connect to the database. We'll do a very simple connexion:

&lt;?php
////////////////////////////////////////////////////////
// DATABASE.PHP
////////////////////////////////////////////////////////
  mysql_connect("localhost", "username", "pwd") or
  die("Could not connect: " . mysql_error());
  mysql_select_db("db_name");

I'll let you fix the names for the localhost / username / pwd / db_name.

Second, we need to process the requests from Ext. We'll have Ext ask for different tasks through a $_POST var called 'task'. So, imagining that Ext will send us a $_POST['task'], all we need to do is to respond accordingly.

$task = '';
  if ( isset($_POST['task'])){
    $task = $_POST['task'];   // Get this from Ext
  }
  switch($task){
    case "LISTING":              // Give the entire list
      getList();
      break;
    default:
      echo "{failure:true}";  // Simple 1-dim JSON array to tell Ext the request failed.
      break;
  }


Great... Now that that's done, we'll go ahead and go build the getList() function which will simply query our database. The only problem is that we need to supply Ext with a JSON format, and that SQL just returns an array. Well now you just have to figure out for yourself the script that will do the conversion between the two.

Ok, you don't really have to... Actually, on PHP versions 5.2 and higher, that function is already implemented. Yee... Still, we'll pretend like we're running on any version of PHP, so we'll just have to add a file called JSON.php that will do the conversion for us.

Allright, back to the code :

function getList() 
{
	$query = "SELECT * FROM presidents pr, parties pa WHERE pr.IDparty = pa.IDparty";
	$result = mysql_query($query);
	$nbrows = mysql_num_rows($result);	
	if($nbrows>0){
		while($rec = mysql_fetch_array($result)){
                        // render the right date format
			$rec['tookoffice']=codeDate($rec['tookoffice']);
			$rec['leftoffice']=codeDate($rec['leftoffice']); 
			$arr[] = $rec;
		}
		$jsonresult = JEncode($arr);
		echo '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
	} else {
		echo '({"total":"0", "results":""})';
	}
}

As you can see, we put everything into a big array $arr and gave that to our JEncode() function. The result, $jsonresult, is a nice JSON array that is ready to be shipped to Ext. We add the total number of results and we send everything back through the ECHO command.

Note: The ECHO command is what makes this Ext/PHP thing so easy to debug... If you don't know what I'm talking about, please download FIREBUG and use it as you go along with this tutorial.

By the way, we added a little codeDate function that simply translates a mm/dd/YYYY format to a YYYY-mm-dd format. We do this so that we can send it directly to our SQL database :

// Encodes a YYYY-MM-DD into a MM-DD-YYYY string
function codeDate ($date) {
	$tab = explode ("-", $date);
	$r = $tab[1]."/".$tab[2]."/".$tab[0];
	return $r;
}

Ok, we just need to write down the JEncode function and we'll be done!

function JEncode($arr){
    if (version_compare(PHP_VERSION,"5.2","<"))
    {    
        require_once("./JSON.php");   //if php<5.2 need JSON class
        $json = new Services_JSON();  //instantiate new json object
        $data=$json->encode($arr);    //encode the data in json format
    } else
    {
        $data = json_encode($arr);    //encode the data in json format
    }
    return $data;
}
?&gt;

Setting up your Ext Grid

Allright... Time to display our results in a beautiful ext grid... Before any further, you need to be aware about two things : DataStores and ColumnModels

Let's start writing our mainscript.js file :

var PresidentsDataStore;         // this will be our datastore
var PresidentsColumnModel;       // this will be our columnmodel
var PresidentListingEditorGrid;
var PresidentListingWindow;
 
Ext.onReady(function(){
  Ext.QuickTips.init();

DataStores

DataStores are basically arrays that store data. (Could have guessed that by yourself, huh?) The great thing is that they can retrieve their data from any kind of source, from static XML arrays to... dynamic JSON sources!! A grid needs to be attached to a DataStore. If you want to change the content of a grid, you basically have to change the content of the datastore and then call a refresh() method.

We need a DataStore that will need to connect to our file database.php, send the task 'LISTING' and then read the results. We then give corresponding names that are 'mapped' to the data in the array. Here's how we go about building a basic DataStore :

PresidentsDataStore = new Ext.data.Store({
      id: 'PresidentsDataStore',
      proxy: new Ext.data.HttpProxy({
                url: 'database.php',      // File to connect to
                method: 'POST'
            }),
            baseParams:{task: "LISTING"}, // this parameter asks for listing
      reader: new Ext.data.JsonReader({   
                  // we tell the datastore where to get his data from
        root: 'results',
        totalProperty: 'total',
        id: 'id'
      },[ 
        {name: 'IDpresident', type: 'int', mapping: 'IDpresident'},
        {name: 'FirstName', type: 'string', mapping: 'firstname'},
        {name: 'LastName', type: 'string', mapping: 'lastname'},
        {name: 'IDparty', type: 'int', mapping: 'IDparty'},
        {name: 'PartyName', type: 'string', mapping: 'name'},
        {name: 'TookOffice', type: 'date', mapping: 'tookoffice'},
        {name: 'LeftOffice', type: 'date', mapping: 'leftoffice'},
        {name: 'Income', type: 'float', mapping: 'income'}
      ]),
      sortInfo:{field: 'IDpresident', direction: "ASC"}
    });

Ok... Not too difficult, is it?

ColumnModels

Now, we need to define a ColumnModel for our grid. Simply put, a column model is specifying to Ext the way that our columns are defined. Do we display numbers? strings? do we want them to be editable? In fact, ColumnModels can do a lot more things that we can talk of in this tutorial. You can do just about anything with a single cell if you personnalize it enough...

For now, we'll simply display basic columns, as you'll see with the following code:

PresidentsColumnModel = new Ext.grid.ColumnModel(
    [{
        header: '#',
        readOnly: true,
        dataIndex: 'IDpresident', // this is where the mapped name is important!
        width: 50,
        hidden: false
      },{
        header: 'First Name',
        dataIndex: 'FirstName',
        width: 150,
        editor: new Ext.form.TextField({  // rules about editing
            allowBlank: false,
            maxLength: 20,
            maskRe: /([a-zA-Z0-9\s]+)$/   // alphanumeric + spaces allowed
          })
      },{
        header: 'Last Name',
        dataIndex: 'LastName',
        width: 150,
        editor: new Ext.form.TextField({
          allowBlank: false,
          maxLength: 20,
          maskRe: /([a-zA-Z0-9\s]+)$/
          })
      },{
        header: 'ID party',
        readOnly: true,
        dataIndex: 'IDparty',
        width: 50,
        hidden: true                      // we don't necessarily want to see this...
      },{
        header: 'Party',
        dataIndex: 'PartyName',
        width: 150,
        readOnly: true
      },{
        header: "Income",
        dataIndex: 'Income',
        width: 150,
        renderer: function(v){ return '$ ' + v; },   
                           // we tell Ext how to display the number
        editor: new Ext.form.NumberField({
          allowBlank: false,
          decimalSeparator : ',',
          allowDecimals: true,
          allowNegative: false,
          blankText: '0',
          maxLength: 11
          })
      }]
    );
    PresidentsColumnModel.defaultSortable= true;

The Part You've Been Waiting For...

Ok! Now that we've set up a data source from where to read our data, and that we've set up a way to display the data in the grid, we're ready to display the grid!! The next part of code is really a piece of cake. Make sure you don't forget to load() the DataStore (load() will query our database.php file) :

PresidentListingEditorGrid =  new Ext.grid.EditorGridPanel({
      id: 'PresidentListingEditorGrid',
      store: PresidentsDataStore,     // the datastore is defined here
      cm: PresidentsColumnModel,      // the columnmodel is defined here
      enableColLock:false,
      clicksToEdit:1,
      selModel: new Ext.grid.RowSelectionModel({singleSelect:false})
    });
 
  PresidentListingWindow = new Ext.Window({
      id: 'PresidentListingWindow',
      title: 'The Presidents of the USA',
      closable:true,
      width:700,
      height:350,
      plain:true,
      layout: 'fit',
      items: PresidentListingEditorGrid  // We'll just put the grid in for now...
    });
 
  PresidentsDataStore.load();      // Load the data
  PresidentListingWindow.show();   // Display our window
 
});

Editing the Grid

Great, we now have a grid that displays stuff. You can already see all the nice features that ext has to offer. You can resize or move around the columns. You can decide which to show or hide. And best of all, you can make little red triangles appear on cell corners as you edit them. Now that's neat...

Now, you'll ask me. What exactly is happening when I edit the content of a cell? Well, the answer is quite simple... Nothing. Though Ext will do a lot of nice things, it lets us handle all of the data saving, which means... That we'll have to code it ourselves. This is the purpose of this part of the tutorial.

Narrowing down some fields

Before going further, there is one thing that we need to fix. As you could see in the SQL Tables, the politic parties come from a predefined list (6 parties total) and should not be modified outside of those choices. The best way to implement this is to change the textbox by a combobox in the ColumnModel and feed it the different possibilities. There are basically two solutions:

  • You can create a new DataStore that will connect to database.php and ask for a list of parties to display. Simply connect the combobox to that datastore and you're done. This is mostly useful when the data changes during runtime, which is not the case for our program.
  • You can predefine a hidden combobox in the main HTML file and have your combobox rendered according to it. This is fine if you are sure that the data is static. This is done via the command 'transform'.
  • You can create a simplestore. A SimpleStore is a datastore that is defined directly within the script. It is then accessible for modification, but it is not linked to a proxy to request its data.

Now, it would be very good training to do the DataStore method. I'm sure that it actually is the case in which you'll run into most often and I would advise you to do it now. As a small tip, let me give you the ColumnModel code that should be changed :

{ 
  header: 'Party',
  dataIndex: 'Name',    
  width: 120,
  editor: new Ext.form.ComboBox({
    store: PartiesListingDataStore,  // or whatever you've called it
    displayField:'Name',         // we have two indexes, ID and Name
    typeAhead: true,
    mode: 'remote',
    triggerAction: 'all',
    selectOnFocus:true
    }),
  hidden: false
}

You got it? Allright, let's go over the third solution using a SimpleStore then.

We need a store that contains all of our static data. Let's build it now:

header: 'Party',
   dataIndex: 'PartyName',
   width: 150,
   editor: new Ext.form.ComboBox({
         typeAhead: true,
         triggerAction: 'all',
         store:new Ext.data.SimpleStore({
         fields:['partyValue', 'partyName'],
         data: [['1','No Party'],['2','Federalist'],['3','Democratic-Republican'],['4','Democratic'],['5','Whig'],['6','Republican']]
                }),
         mode: 'local',
         displayField: 'partyName',
         valueField: 'partyValue',
         listClass: 'x-combo-list-small'
      })

As you can see, we can define which field to display, and which field to take as a value. Now that's pretty sweet... That means that we can directly send to our PHP script the value of the party (IDparty) and we won't need an additional query to get it.

And we're done! Give it a try, you'll feel happy like if you just ate some good chocolate.

Editing

Here's the heart of this section... Now that you know that we are the ones supposed to do the entire saving, you probably wonder how that is to be done. Well, there are different approaches to that problem...

  • You could save a row everytime some content is changed. That is always nice if you have some interdependant cells. It is not the best solution if you have to save a lot of data though...
  • You could loop through the grid and only save the cells that have been modified at a given point (recurrent autosave or a manual save button).
  • You could simply save the entire grid at a random time.
  • You could also not save the grid and go eat a cheeseburger.

The choice is really yours, but it depends upon the amount of saving that you need. I cannot say that one method is better than another...

We will go along with the first choice and save the president everytime one of his cell changes. We do that by first creating a saveThePresident function. That function will make a call to database.php with the newly called task 'UPDATEPRES' and send all of the row data. It will then wait for an answer from our server PHP script. We'll stay simple : database.php either answers 1 for a successful change or 0 for the rest. If the change was successful, we simply reload our store to see the changes automatically appear on our grid.

function saveThePresident(oGrid_event){
   Ext.Ajax.request({   
      waitMsg: 'Please wait...',
      url: 'database.php',
      params: {
         task: "UPDATEPRES",
         IDpresident: oGrid_event.record.data.IDpresident,
         FirstName: oGrid_event.record.data.FirstName,
         LastName: oGrid_event.record.data.LastName,
         PartyName: oGrid_event.record.data.PartyName,
         TookOffice: oGrid_event.record.data.TookOffice.format('Y-m-d'), // this time we'll format it thanks to ext
         LeftOffice: oGrid_event.record.data.LeftOffice.format('Y-m-d'),
         Income: oGrid_event.record.data.Income
      }, 
      success: function(response){							
         var result=eval(response.responseText);
         switch(result){
         case 1:
            PresidentsDataStore.commitChanges();   // changes successful, get rid of the red triangles
            PresidentsDataStore.reload();          // reload our datastore.
            break;					
         default:
            Ext.MessageBox.alert('Uh uh...','We couldn\'t save him...');
            break;
         }
      },
      failure: function(response){
         var result=response.responseText;
         Ext.MessageBox.alert('error','could not connect to the database. retry later');		
      }									    
   });   
  }

Ok, now that the save function is ready, we need to attach it to the event where the user edits the cell. This is done at the very end of our javascript file :

PresidentListingEditorGrid.on('afteredit', saveThePresident);

Note : The place where this is inserted is irrelevant. I could have put this in a constructor or right after my grid had been instantiated.

Allright, we're done for the client side. We now need to go towards the server and edit our database.php file. We'll first need to recognize the task name, so let's add another choice to our switch entries :

case "UPDATEPRES":
        updatePresident();
        break;

This is pretty obvious. Let's write the updatePresident() function :

function updatePresident()
{
    $IDpresident = $_POST['IDpresident'];
    $FirstName = addslashes($_POST['FirstName']);
    $LastName = addslashes($_POST['LastName']);
    $PartyName = $_POST['PartyName'];
    $TookOffice = $_POST['TookOffice'];
    $LeftOffice = $_POST['LeftOffice'];
    $Income = $_POST['Income'];
 
    // First, find the $IDparty
    $query = "SELECT IDParty FROM parties WHERE Name='".$PartyName."'";
    $result = mysql_query($query);
    if(mysql_num_rows($result)>0){
      $arr = mysql_fetch_array($result);
      $IDparty = $arr['IDParty'];
    } else {
      echo '0';      // failure
    }
 
    // Now update the president
    $query = "UPDATE presidents SET firstname = '$FirstName', lastname = '$LastName', tookoffice = '$TookOffice', leftoffice = '$LeftOffice', IDparty = '$IDparty', income='$Income' WHERE IDpresident=$IDpresident";
    $result = mysql_query($query);
    echo '1';        // success
}

Go ahead and test it. Working? Surprised? Is it the first time that it works? Amazed?

Note : We basically made two queries (this could have easily been done with one query... Can you find how?), one to get the IDparty for the chosen party, and the other query for the modification itself. Note that these queries do not check for errors. We could end up with two presidents having the same name or run at the same time. I'll let you handle all of that on your own!

Adding a New Entry

Allright, we have setup our grid and we are now able to do some modifications to it. They are recorded directly to our SQL table. This part of the tutorial will teach you how to add a new entry to your grid.

There are a lot of ways that we could implement this. One of the ways shown in the doc examples is to insert a blank new entry into our grid and edit it. That is also the way approached by M. LeCompte. What we are going to do is slightly different. We will build a new window that will contain a form for filling out the president data.

Creating the form

Allright, let's look at the form datafields that we'll need :

  • A textfield to contain the first name.
  • A textfield to contain the last name.
  • Two datefields to contain info about entering or leaving office.
  • A numberfield to contain the total income.
  • A combobox to contain the chosen party.

All those will need to be included in a form and contained in a window. Let's define those at the beginning of the script :

var PresidentCreateForm;
  var PresidentCreateWindow;

To make things clear and easier for everybody, we will create each field before adding them to the form. Each field will be a global var (this is definetely not the best way to make things!! in the future, you'll want to create different IDs for each field and have a reference to them.... Yet we'll have global vars here because it is easiesr to understand...) Just remember that you normally wouldn't want 35078 global vars running through your program... Also, the way we are making things now will prevent us from having two Creation Forms at once.

Note : If you are completely clueless about how to layout your app code, you might later want to check out the Application Layout Tutorial done by Jozef Sakalos.

Let's define the fields at the beginning of the script :

var FirstNameField;
  var LastNameField;
  var EnteringOfficeField;
  var LeavingOfficeField;
  var IncomeField;
  var PartyField;

We need to write the code that describes the behavior of those fields: (within the onReady function):

FirstNameField = new Ext.form.TextField({
    id: 'FirstNameField',
    fieldLabel: 'First Name',
    maxLength: 20,
    allowBlank: false,
    anchor : '95%',
    maskRe: /([a-zA-Z0-9\s]+)$/
      });
 
  LastNameField = new Ext.form.TextField({
    id: 'LastNameField',
    fieldLabel: 'Last Name',
    maxLength: 20,
    allowBlank: false,
    anchor : '95%',    
    maskRe: /([a-zA-Z0-9\s]+)$/  
      });
 
  EnteringOfficeField = new Ext.form.DateField({
    id:'EnteringOfficeField',
    fieldLabel: 'Entering Office',
    format : 'm/d/Y',
    allowBlank: false,
    anchor:'95%'
    });
 
  LeavingOfficeField = new Ext.form.DateField({
    id:'LeavingOfficeField',
    fieldLabel: 'Leaving Office',
    allowBlank: false,
    format : 'm/d/Y',
    anchor:'95%'
    });
 
  IncomeField = new Ext.form.NumberField({
    id:'IncomeField',
    fieldLabel: 'Income',
    allowNegative: false,
    allowBlank: false,
    anchor:'95%'
    });

All those were pretty easy to define. Now we have the party field... Love that one, huh? Remember, that field needs to correspond with the data included in the SQL database. In Part 3, we talked about using a regular DataStore. In Part 4, we saw three different ways of filling out our combo-box...

In this part of the tutorial, we will keep using a SimpleStore. You might want to keep in mind that rendering a combobox according to a predefined HTML combobox might create some browser compatibility issues (think about those poor IE users a bit...):

PartyField = new Ext.form.ComboBox({
     id:'PartyField',
     fieldLabel: 'Party',
     store:new Ext.data.SimpleStore({
       fields:['partyValue', 'partyName'],
       data: [['1','No Party'],['2','Federalist'],
       ['3','Democratic-Republican'],['4','Democratic'], 
       ['5','Whig'],['6','Republican']]
       }),
     mode: 'local',
     displayField: 'partyName',
     allowBlank: false,
     valueField: 'partyValue',
     anchor:'95%',
     triggerAction: 'all'
      });

Creating the Form

Now that the fields are all ready and created, we are ready to create our form :

PresidentCreateForm = new Ext.FormPanel({
        labelAlign: 'top',
        bodyStyle:'padding:5px',
        width: 600,        
        items: [{
            layout:'column',
            border:false,
            items:[{
                columnWidth:0.5,
                layout: 'form',
                border:false,
                items: [FirstNameField, LastNameField, PartyField]
            },{
                columnWidth:0.5,
                layout: 'form',
                border:false,
                items: [EnteringOfficeField, LeavingOfficeField, IncomeField]
            }]
        }],
    buttons: [{
      text: 'Save and Close',
      handler: createThePresident
    },{
      text: 'Cancel',
      handler: function(){
        // because of the global vars, we can only 
        // instantiate one window... so let's just hide it.
        PresidentCreateWindow.hide();
      }
    }]
    });
 
  PresidentCreateWindow= new Ext.Window({
      id: 'PresidentCreateWindow',
      title: 'Creating a New President',
      closable:true,
      width: 610,
      height: 250,
      plain:true,
      layout: 'fit',
      items: PresidentCreateForm
    });

Ok... Not too bad is it? One important thing to remember is that each nested element has a 'layout' field. Don't forget them as they are the cause of many forum threads... Well ok, we now have a great form to be displayed. Before we can display it, we need to handle the button events and create functions to save the data. The nice thing about the way we set up our form is that we can directly access our field values by using the methods setValue() or getValue().

Let's first set up some small functions to work with our form.

// reset the Form before opening it
  function resetPresidentForm(){
    FirstNameField.setValue('');
    LastNameField.setValue('');
    EnteringOfficeField.setValue('');
    LeavingOfficeField.setValue('');
    IncomeField.setValue('');
    PartyField.setValue('');    
  }
 
  // check if the form is valid
  function isPresidentFormValid(){
  return(FirstNameField.isValid() && 
  LastNameField.isValid() && 
  EnteringOfficeField.isValid() &&
  LeavingOfficeField.isValid() 
  && IncomeField.isValid() 
   && PartyField.isValid());
  }
 
  // display or bring forth the form
  function displayFormWindow(){
  if(!PresidentCreateWindow.isVisible()){
    resetPresidentForm();
    PresidentCreateWindow.show();
  } else {
    PresidentCreateWindow.toFront();
  }
  }

Creating the President

Now we just need to implement a save method. You will find that it is quite similar to what we did when we edited a grid cell:

function createThePresident(){
     if(isPresidentFormValid()){
      Ext.Ajax.request({   
        waitMsg: 'Please wait...',
        url: 'database.php',
        params: {
          task: "CREATEPRES",
          firstname:      FirstNameField.getValue(),
          lastname:       LastNameField.getValue(),
          enteringoffice: EnteringOfficeField.getValue().format('Y-m-d'),
          leavingoffice:  LeavingOfficeField.getValue().format('Y-m-d'),
          income:      	  IncomeField.getValue(),
          party:          PartyField.getValue()
        }, 
        success: function(response){              
          var result=eval(response.responseText);
          switch(result){
          case 1:
            Ext.MessageBox.alert('Creation OK',
            'The president was created successfully.');
            PresidentsDataStore.reload();
            PresidentCreateWindow.hide();
            break;
          default:
            Ext.MessageBox.alert('Warning',
            'Could not create the president.');
            break;
          }        
        },
        failure: function(response){
          var result=response.responseText;
          Ext.MessageBox.alert('error',
         'could not connect to the database. 
          retry later');          
        }                      
      });
    } else {
      Ext.MessageBox.alert('Warning',
       'Your Form is not valid!');
    }
  }

Allright... That wasn't too bad, was it? Now all that we need is some way to call displayFormWindow(). We can either do that through a contextmenu (right click), which we'll cover later, or we can do that through a toolbar. Let's go ahead and start building that toolbar :

We simply need to add this to our EditorGrid constructor :

tbar: [{
         text: 'Add a president',
	 tooltip: 'Great Tooltip',
         iconCls:'add',    // this is defined in our styles.css
         handler: displayFormWindow
      }]

The Final Touch

Allright, I've never seen you so impatient to try this thing out... Ok, but we're not completely done yet. We need the server side to handle the CREATEPRES request... Let's go ahead and plug that into the database.php file :

First the switch:

case "CREATEPRES":
        createPresident();
        break;

And then the function:

function createPresident(){
 
	$firstname = addslashes($_POST['firstname']);
	$lastname = addslashes($_POST['lastname']);
	$enteringoffice = $_POST['enteringoffice'];
	$leavingoffice = $_POST['leavingoffice'];
	$income = $_POST['income'];
	$party  = $_POST['party'];
 
	// Here we should probably do some database checking, 
        // to make sure that we do not have the same entry 
        // twice for ex... And we would return a different 
        // error code (ECHO '0' or whatever you want...) 
        // For now we'll pretend like the entry is valid. 
	$query = "INSERT INTO presidents (`IDpresident` 
        ,`IDparty` ,`firstname` ,`lastname` ,`tookoffice` 
        ,`leftoffice` ,`income` ) VALUES (NULL , '$party'
        , '$firstname', '$lastname', '$enteringoffice'
        , '$leavingoffice', '$income')";
	$result = mysql_query($query);
	echo '1';
}

There you go, you can now add presidents to your grid!

Deleting an Entry

In the precedent parts, we created an editable grid and implemented a way to add entries to it. Let's take some time to think about something. We haven't implemented much and we've already had to write down a couple of hundreds of lines of code... This really illustrates the importance of structuring your code. There are tutorials and forum threads about how to go about writing Ext code. Make sure that you use Firefox and a debugger (Firebug), that you check out the results on IE also. You can check your .js files with JSLint to make sure that they are correct. It's also very easy to get lost writing Ext code, as most structures are in a Json format. Make sure to indent your code correctly so that you always know where you are. If you are still stuck, check out Mickael's FAQ on grids

This tutorial will help us delete entries in the grid. Now this could be very easy. We could simple choose a row and click on a Delete button, then send that through an Ajax request to be transformed in a SQL query, just like we've been doing until now. You could actually try it out and see how easy it is.

It gets a tiny bit trickier when we select multiple rows. Multiple rows you say?? If you haven't, try selecting different rows using the CTRL or the SHIFT key... See? In order to process all of the selected rows, we are going to loop through all of them and build an array that will contain the IDpresidents of each row. Then we will simply encode that array and send it as our parameter.

Let's see how this is done :

var selections = PresidentListingEditorGrid.selModel.getSelections();
  var prez = [];
  for(i = 0; i< PresidentListingEditorGrid.selModel.getCount(); i++){
    prez.push(selections<i>.json.IDpresident);
  }
  var encoded_array = Ext.encode(prez);

As you can see, that code is pretty straightforward. Let's implement it into our script.

First, we'll add a delete button in our toolbar (we've also added the CSS code), which becomes :

tbar: [
    {
      text: 'Add a President',
      tooltip: 'Great tooltips...',
      iconCls:'add',                      // reference to our css
      handler: displayFormWindow
    }, '-', {
      text: 'Delete selection',
      tooltip: 'Jose, can you seeeee??',
      handler: confirmDeletePresidents,   // Confirm before deleting
      iconCls:'remove'
  }]

Because the people who will use your applications are not always the smartest (myself not included) and may hit the delete key by 'mistake' after having carefully selected different presidents, we are going to ask for confirmation before going to the actual delete function:

function confirmDeletePresidents(){
    if(PresidentListingEditorGrid.selModel.getCount() == 1) // only one president is selected here
    {
      Ext.MessageBox.confirm('Confirmation','Do you not like that president at all?', deletePresidents);
    } else if(PresidentListingEditorGrid.selModel.getCount() > 1){
      Ext.MessageBox.confirm('Confirmation','Delete those presidents?', deletePresidents);
    } else {
      Ext.MessageBox.alert('Uh oh...','You can\'t really delete something you haven\'t selected huh?');
    }
  }

The Entire Delete Function

Allright, here we go.

function deletePresidents(btn){
    if(btn=='yes'){
         var selections = PresidentListingEditorGrid.selModel.getSelections();
         var prez = [];
         for(i = 0; i< PresidentListingEditorGrid.selModel.getCount(); i++){
          prez.push(selections<i>.json.IDpresident);
         }
         var encoded_array = Ext.encode(prez);
         Ext.Ajax.request({  
            waitMsg: 'Please Wait',
            url: 'database.php', 
            params: { 
               task: "DELETEPRES", 
               ids:  encoded_array
              }, 
            success: function(response){
              var result=eval(response.responseText);
              switch(result){
              case 1:  // Success : simply reload
                PresidentsDataStore.reload();
                break;
              default:
                Ext.MessageBox.alert('Warning','Could not delete the entire selection.');
                break;
              }
            },
            failure: function(response){
              var result=response.responseText;
              Ext.MessageBox.alert('error','could not connect to the database. retry later');      
              }
         });
      }  
  }

Well, we now need to catch that on the other side. Let's head over to our friend Mr. ServerScript database.php :

Once again, our switch needs to be modified :

case "DELETEPRES":
        deletePresidents();
        break;

And then we need to implement the deletePresidents() function. There's a little tricky part with making the right query depending upon the selection made... We don't really want to make as many queries as they are IDs to be deleted. Instead, we want to make sure that a single query will delete whatever we ask. (Using Firebug will really help here!! You can see what comes out of that server script and therefore echo all the debug results that you need...) Anyways, the query is done in the following function

function deletePresidents(){
   $ids = $_POST['ids']; // Get our array back and translate it :
   if (version_compare(PHP_VERSION,"5.2","<"))  {    
     require_once("./JSON.php"); 
     $json = new Services_JSON();
     $idpres = $json->decode(stripslashes($ids));
   } else {
     $idpres = json_decode(stripslashes($ids));
   }
 
    // You could do some checkups here and return '0' or other error consts.
 
    // Make a single query to delete all of the presidents at the same time :
    if(sizeof($idpres)<1){
      echo '0';
    } else if (sizeof($idpres) == 1){
      $query = "DELETE FROM presidents WHERE IDpresident = ".$idpres[0];
      mysql_query($query);
    } else {
      $query = "DELETE FROM presidents WHERE ";
      for($i = 0; $i < sizeof($idpres); $i++){
         $query = $query . "IDpresident = ".$idpres[$i];
         if($i<sizeof($idpres)-1){
            $query = $query . " OR ";
         }     
      }
      mysql_query($query);
    }
    // echo $query;  This helps me find out what the heck is going on in Firebug...
    echo '1';
}

Custom CSS Styles

Allright, glad to see that you're still with me! If you've come this far, you've probably experienced the immense joy of editing, adding and deleting entries in a grid. You've probably spent hours playing with the grid and spent some time showing what you did to your kids, your spouse, your parents and maybe your dog. Most likely, they only replied : "That's great!! But is there any way to change the css style of those cells??" Well, enough mysteries... IT IS POSSIBLE!! And... It's really not that hard...

Let's start with the beginning

In every column added to the ColumnModel, we can add a parameter called renderer. That parameter is basically a function that enables us to tell ext how to display the cells within that column.

The renderer function has 6 parameters : (data, cell, record, rowIndex, columnIndex, store). Playing around with the data value, we can set some style accordingly. Actually, this is something that we had already done in our NumberField... (we returned '$ ' followed by the value)

Playing around with colors

We will find the presidents who had the better income. For that, we will change the color of the income according to its value. We need to change the renderer function of the Income column :

renderer: function(value, cell){ 
         var str = '';
         if(value > 1000000){
            str = "<span style='color:#336600;'>$ " + value + "</span>";
         } else if (value > 100000 && value < 1000000){
            str = "<span style='color:#FF9900;'>$ " + value + "</span>";
         } else {
            str = "<span style='color:#CC0000;'>$ " + value + "</span>";
         }
         return str; 
        },

Playing around with css styles

As you had your friends try out your grid, they probably got mad trying to edit the IDs of the presidents (which are set as readonly) and might have killed your mouse left button as if they had been playing Diablo for too long. It is for those friends that we want to edit the css styles of a cell. That is simply done through the cell.css field.

Let's add this code in our style.css :

.readonlycell { 
  background-color:#CCCCCC !important;
}

Now for the IDPresident and the IDparty, let's add a renderer that sets the cell css styles :

renderer: function(value, cell){ 
         cell.css = "readonlycell";
         return value;
        }

Remember, a renderer function must return some data... Otherwise you won't see much...

Any Styles for Any Cell

As you can see, you can change the cell any way you want. In the given example, we changed the background image for the first and last name of the president. For no reason really... I wouldn't advice you to overcharge your grid with too much css, otherwise your eyes will just hurt as your grid appears on the screen.

Searching the Grid

Allright... As you can see, we can format our grid any way we want. Today we are going to learn how to do some search within our grid. It is easy now to find John F. Kennedy, not only because you of course know the dates of his office, but also because there are only about 40 entries to choose from. It would be a different story if we had included the entire political staff from the USA... Hence the importance of being able to give the user some possibilities to narrow down his research.

Creating our Search Form

Last time we created a form, we declared a bunch of fields as global vars and accessed them through their name.setValue() or getValue() methods. Remember that because of this we were only able to have one single creation form. This is not really restrictive in our case, and the purpose was to be clear and to concentrate on the grid system of ext. Yet, in order to illustrate the way that things should be done, we are going to create the next form without global vars. Hence we will be able to have multiple Search Forms... Which is completely useless by the way.

Here's the way it goes. When we click on the Advanced Search button, we'll want a new search window to popup, empty fields, and ready to do research. We'll be able to have multiple advanced search windows.

First, let's add a Search button to our toolbar (in the PresidentListingEditorGrid):

}, '-', {
            text: 'Search',
            tooltip: 'Advanced Search',
            handler: startAdvancedSearch,  // search function
            iconCls:'search'               // we'll need to add this to our css
          }

Let's define our startAdvancedSearch function. Here's the first bit:

function startAdvancedSearch(){
      // local vars
      var PresidentSearchForm;
      var PresidentSearchWindow;
      var SearchFirstNameItem;
      var SearchLastNameItem;
      var SearchPartyItem;
      var SearchEnteringItem;
      var SearchLeavingItem;
 
      SearchFirstNameItem = new Ext.form.TextField({
          fieldLabel: 'First Name',
          maxLength: 20,
          anchor : '95%',
          maskRe: /([a-zA-Z0-9\s]+)$/
            });
 
      SearchLastNameItem = new Ext.form.TextField({
          fieldLabel: 'Last Name',
          maxLength: 20,
          anchor : '95%',    
          maskRe: /([a-zA-Z0-9\s]+)$/  
            });
 
      SearchPartyItem = new Ext.form.ComboBox({
         fieldLabel: 'Party',
         store:new Ext.data.SimpleStore({
          fields:['partyValue', 'partyName'],
          data: [['1','No Party'],['2','Federalist'],['3','Democratic-Republican'],['4','Democratic'],['5','Whig'],['6','Republican']]
          }),
         mode: 'local',
         displayField: 'partyName',
         valueField: 'partyValue',
         anchor:'95%',
         triggerAction: 'all'
            });
 
      SearchEnteringItem = new Ext.form.DateField({
          fieldLabel: 'Entered Office after',
          format : 'm/d/Y',
          anchor:'95%'
          });
 
      SearchLeavingItem = new Ext.form.DateField({
          fieldLabel: 'Left Office before',
          format : 'm/d/Y',
          anchor:'95%'
          });
        }

Looks familiar? It should!! It's almost the same thing as what we did for the creation form... Except those items do not have specific ids attached to them (remember that those being unique, we wouldn't be able to have multiple instances of those items if we had given them an id.). The other huge difference also is that each field, form or window is defined within the function. We won't need to worry about hiding the window, since we can just call another instance once this one is destroyed.

Let's go on :

PresidentSearchForm = new Ext.FormPanel({
       labelAlign: 'top',
       bodyStyle: 'padding: 5px',
       width: 300,
       items: [{
         layout: 'form',
         border: false,
         items: [ SearchFirstNameItem,SearchLastNameItem,SearchPartyItem,SearchEnteringItem, SearchLeavingItem ],
         buttons: [{
               text: 'Search',
               handler: listSearch
             },{
               text: 'Close',
               handler: resetSearch
             }]
         }]
     });
 
     PresidentSearchWindow = new Ext.Window({
         title: 'President Search',
         closable:true,
         width: 200,
         height: 400,
         plain:true,
         layout: 'fit',
         items: PresidentSearchForm
     });

The important things in that code is simply the calls to two functions, listSearch and resetSearch, which will have to be define here locally (see next paragraph) in order for them to access to the local field values. We have a Search and a Close button. The Search button will perform the Search and the Close button, interestingly enough, will also perform a research... Here's why...

function listSearch(){
         // render according to a SQL date format.
         var startDate = "";
         var endDate = "";
         if(SearchEnteringItem.getValue() != "") {
            startDate = SearchEnteringItem.getValue().format('Y-m-d');
         }
         if(SearchLeavingItem.getValue() != "") {
            endDate = SearchLeavingItem.getValue().format('Y-m-d');
         }
 
         // change the store parameters
    	    PresidentsDataStore.baseParams = {
   			task: 'SEARCH',
   			firstname: SearchFirstNameItem.getValue(),
            lastname : SearchLastNameItem.getValue(),
            party : SearchPartyItem.getValue(),
            enteringoffice : startDate,
            leavingoffice : endDate
   		};
         // Cause the datastore to do another query : 
   		PresidentsDataStore.reload();
     }
 
     function resetSearch(){
         // reset the store parameters
    		PresidentsDataStore.baseParams = {
   			task: 'LISTING'
   		};
         // Cause the datastore to do another query : 
   		PresidentsDataStore.reload();
         PresidentSearchWindow.close();
     }
 
     // once all is done, show the search window
     PresidentSearchWindow.show();

See the trick? What we do is take the original datastore and change its base parameters. We then ask it to reload itself. Doing this performs a new query. The results are then displayed just the way they were at the beginning of the tutorial.

This explains the resetSearch function. In order to reset everything, we need to set the original datastore parameters (that is... just the 'listing' task). When then reload our datastore.

A little query exercice

Are you getting used to those Ajax Requests yet? The server script just handles that call like any other... Let's write it down now (database.php):

First the switch :

case "SEARCH":
        searchPresidents();
        break;

and finally our searchPresidents() function :

function searchPresidents()
{
   $query = "SELECT * FROM presidents pr, parties pa WHERE pr.IDparty = pa.IDparty";
 
   $firstname = $_POST['firstname'];
   $lastname = $_POST['lastname'];
   $enteringoffice = $_POST['enteringoffice'];
   $leavingoffice = $_POST['leavingoffice'];
   $party  = $_POST['party'];
 
   if($firstname != ''){
      $query .= " AND firstname LIKE '%".$firstname."%'";
   };
   if($lastname != ''){
      $query .= " AND lastname LIKE '%".$lastname."%'";
   };
   if($party != ''){
      $query .= " AND pr.IDparty = '".$party."'";
   };
   if ($enteringoffice) {
        $query .= " AND tookoffice >= '".$enteringoffice."'";
     };
   if ($leavingoffice) {
        $query .= " AND leftoffice <= '".$leavingoffice."'";
     };
 
   $result = mysql_query($query);
   $nbrows = mysql_num_rows($result);  
   if($nbrows>0){
    while($rec = mysql_fetch_array($result)){
            // render the right date format
      $rec['tookoffice']=codeDate($rec['tookoffice']);
      $rec['leftoffice']=codeDate($rec['leftoffice']);      
      $arr[] = $rec;
    }
    $jsonresult = JEncode($arr);
    echo '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
   } else {
    echo '({"total":"0", "results":""})';
   }
}

We basically look for stuff that is similar to what we were looking for... And it works just fine. You can now have hundreds of entries and find the one you're looking for faster than that other sock this morning...

The Paging Bar

Well it's you again... Yee... Ok let's talk about the paging bars that you see at the bottom. I've always thought they were really nice. The good thing is that they're not too hard to implement either. Let's get it over with, this is going to be a short one!

The PagingToolbar basically needs to know two things : how many entries you want per page and where to send the info about the current paging. (we'll be sending that to a datastore)

Sooo... Just after the toolbar in the EditorGrid, let's add the bottombar :

bbar: new Ext.PagingToolbar({
                pageSize: 15,
                store: PresidentsDataStore,
                displayInfo: true
            })

We'll display 15 presidents at a time. We're almost done! The only thing we need now is to tell our PHP script how we'll be doing our paging. The PagingToolbar uses two parameters, start and limit. Those will be accessible from the server script and we'll just have to narrow down our SQL query.

Let's make sure that we send those parameters everytime we need a list of presidents: Every PresidentsDataStore.reload() or PresidentsDataStore.load() will be transformed into :

PresidentsDataStore.reload({params: {start: 0, limit: 15}});

Make the changes yourself (there are three places in our mainscript.js)

Server Side

This is quite simple. Now that we have a start and a limit var given to us, we just need to have access to it and change the end of our SQL query.

Here's the modification in the getList() function :

$query = "SELECT * FROM presidents pr, parties pa WHERE pr.IDparty = pa.IDparty";
  $result = mysql_query($query);
  $nbrows = mysql_num_rows($result);
  // Add this for the paging bar:
  $start = (integer) (isset($_POST['start']) ? $_POST['start'] : $_GET['start']);
  $end = (integer) (isset($_POST['limit']) ? $_POST['limit'] : $_GET['limit']);
  $limit = $query." LIMIT ".$start.",".$end;		
  $result = mysql_query($limit);

We still want to do the first query in order to get the amount of items (nbrows), but what we will actually send back to Ext is a Json array containing the 15 or less items that we are asked.

Let's not forget to do the same modification in the searchPresidents() function.

The QuickSearch Component

Allright... We're almost through with this tutorial!! You'll soon be able to create your own amazing grids. Let's study the QuickSearch component.

We've explained how to do a regular search on our grid before. It yet required the user to click on the Search button and to fill in the required fields... Ok well you'll find that this can be a VERY complicated task for most users. Hence comes Mr. QuickSearch. The Quicksearch component is basically a cool textbox in which the user enters his text, pushes Enter, and then... Well it depends what we do with it...

Let's go ahead and set it up so that it will look the entire grid for that string.

Allright, before we start we need to add the .js file to our main HTML header:

<script type="text/javascript" src="searchfield.js"></script>

You can find the file in examples/form folder in extjs archive, or you can download it here: http://extjs.com/deploy/dev/examples/form/SearchField.js

First, we add it simply to our grid in our toolbar. We just need to supply him with the basic parameters we've been supplying our datastores :

, '-', new Ext.app.SearchField({
  store: PresidentsDataStore,
  params: {start: 0, limit: 15},
  width: 120
})

Ok, if you're curious enough, you'll just be jumping on your favorite notepad editor to see what that searchfield.js looks like.. And there, you'll see that there is a parameter called 'query' that is sent and then that the store is reloaded. Yeee.. We know what that does! Now... The Presidents DataStore will either be in a 'LISTING' mode or in a 'SEARCHING' mode (based on its baseparams). Well, the Quicksearch button will simply add the string entered in the textbox as an additional parameter.

I am only going to implement this in the case we are simply listing our presidents. I'll let you figure out how to do this on a search (a quicksearch on an existing search).

Let's open up our database.php. Now, when we hit the getList() function, we have to test whether or not we have a 'query' parameter. And if we do, we'll look for some data that looks like it in our grid. Hence, our new getList() function becomes as follows :

$query = "SELECT * FROM presidents pr, parties pa WHERE pr.IDparty = pa.IDparty";
  // Here we check if we have a query parameter :
  if (isset($_POST['query'])){
    $query .= " AND (pr.firstname LIKE '%".addslashes($_POST['query'])."%' OR pr.lastname LIKE '%".addslashes($_POST['query'])."%')";
  }
  $result = mysql_query($query);
  $nbrows = mysql_num_rows($result);
  $start = (integer) (isset($_POST['start']) ? $_POST['start'] : $_GET['start']);
  $end = (integer) (isset($_POST['limit']) ? $_POST['limit'] : $_GET['limit']);
  $limit = $query." LIMIT ".$start.",".$end;		
  $result = mysql_query($limit);  
  [...]

It's that simple! :) Well, now you could take the 'query' parameter, take off all of the empty spaces, explode that to get all of the words in the string and do a better search.. You could also do a search on the years, etc... The implementation on a search that has already been done is completely similar. Just check for a query var before doing your query... And you're done!

Printing your Grid

Allright... I'm only going to say this once before I get spammed with tons of hate mail... The method described here to print out your grid is not the only one, it is not the best one and it is not the most original. This is just a quick example of how one would go about to print a grid.

The way chosen here is to simply create a HTML file and to fill it with an array containing the data displayed in the grid (the entire data, not the first 15 listings)

First, we add the Print button to our toolbar :

,'-', {
            text: 'Print',
	    tooltip: 'Print me!',
            handler: printListing, 
            iconCls:'print'        // defined in our css
          }

Let's now define a printListing() function. This function will call a php function that will create the HTML table file:

function printListing(){
      var searchquery = "";
      var searchfirstname = "";
      var searchlastname = "";
      var searchparty = "";
      var searchenteringoffice = "";
      var searchleavingoffice = "";
      var win;               // our popup window
      // check if we do have some search data...
      if(PresidentsDataStore.baseParams.query!==null){searchquery = PresidentsDataStore.baseParams.query;}
      if(PresidentsDataStore.baseParams.firstname!==null){searchfirstname = PresidentsDataStore.baseParams.firstname;}
      if(PresidentsDataStore.baseParams.lastname!==null){searchlastname = PresidentsDataStore.baseParams.lastname;}
      if(PresidentsDataStore.baseParams.party!==null){searchparty = PresidentsDataStore.baseParams.party;}
      if(PresidentsDataStore.baseParams.enteringoffice!==null){searchenteringoffice = PresidentsDataStore.baseParams.enteringoffice;}
      if(PresidentsDataStore.baseParams.leavingoffice!==null){searchleavingoffice = PresidentsDataStore.baseParams.leavingoffice;}
 
      Ext.Ajax.request({   
        waitMsg: 'Please Wait...',
        url: 'database.php',
        params: {
          task: "PRINT",
               // we have to send all of the search
 
          query: searchquery,                                 // if we are doing a quicksearch, use this
          firstname : searchfirstname,                        // if we are doing advanced search, use this
          lastname : searchlastname,
          party : searchparty,
          enteringoffice : searchenteringoffice,
          leavingoffice : searchleavingoffice,
          currentlisting: PresidentsDataStore.baseParams.task // this tells us if we are searching or not
        }, 
        success: function(response){              
          var result=eval(response.responseText);
          switch(result){
          case 1:
            win = window.open('./presidentslist.html','presidentslist','height=400,width=600,resizable=1,scrollbars=1, menubar=1');
            win.print();
            break;
          default:
            Ext.MessageBox.alert('Uh hu...','Unable to print the grid!');
            break;
          }  
        },
        failure: function(response){
          var result=response.responseText;
          Ext.MessageBox.alert('error','could not connect to the database. retry later');      
        }                      
      });
  }

Ok, you might wonder why we have to put so many parameters in... Well, the thing is... that we want to print out the entire grid, and that our datastore only stores 15 entries. So our PHP file is going to have to do the queries once again, not limiting the search to a few queries. Then our database.php will put all of that into our listpresidents.html file.

Here comes the database.php file : (first the switch...)

case "PRINT":
        printPresidents();
        break;

And then our printing function :

function printPresidents()
{
   // Ok since we limited our queries to 15 entries, we need to do a query again and get everything.
   if($_POST['currentlisting']=='LISTING'){  // we are doing a basic listing or using the quicksearch
        $query = "SELECT * FROM presidents pr, parties pa WHERE pr.IDparty = pa.IDparty";
        // Here we check if we have a query parameter :
        if (isset($_POST['query']) && ($_POST['query'] != "")){
          $query .= " AND (pr.firstname LIKE '%".$_POST['query']."%' OR pr.lastname LIKE '%".$_POST['query']."%')";
        }
        $query .= " ORDER BY tookoffice";
        $result = mysql_query($query);
        $nbrows = mysql_num_rows($result);
   } else if($_POST['currentlisting']=='SEARCH'){
         $query = "SELECT * FROM presidents pr, parties pa WHERE pr.IDparty = pa.IDparty";
         $firstname = $_POST['firstname'];
         $lastname = $_POST['lastname'];
         $enteringoffice = $_POST['enteringoffice'];
         $leavingoffice = $_POST['leavingoffice'];
         $party  = $_POST['party'];
 
         if($firstname != ''){
            $query .= " AND firstname LIKE '%".$firstname."%'";
         };
         if($lastname != ''){
            $query .= " AND lastname LIKE '%".$lastname."%'";
         };
         if($party != ''){
            $query .= " AND pr.IDparty = '".$party."'";
         };
         if ($enteringoffice) {
            $query .= " AND tookoffice >= '".$enteringoffice."'";
         };
         if ($leavingoffice) {
            $query .= " AND leftoffice <= '".$leavingoffice."'";
         };
         $result = mysql_query($query);
         $nbrows = mysql_num_rows($result);
   }
 
   // We now have our array, let's build our HTML file :
   $file = fopen("presidentslist.html",'w');
   fwrite($file, "<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'><html xmlns='http://www.w3.org/1999/xhtml'><head><meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1' /><title>Printing the Grid</title><link rel='stylesheet' type='text/css' href='printstyle.css'/></head>");
   fwrite($file, "<body><table summary='Presidents List'><caption>The Presidents of the United States</caption><thead><tr><th scope='col'>First Name</th><th scope='col'>Last Name</th><th scope='col'>Party</th><th scope='col'>Entering Office</th><th scope='col'>Leaving Office</th><th scope='col'>Income</th></tr></thead><tfoot><tr><th scope='row'>Total</th><td colspan='4'>");
   fwrite($file, $nbrows);
   fwrite($file, " presidents</td></tr></tfoot><tbody>");
   for($i = 0; $i< $nbrows; $i++){
    $data = mysql_fetch_array($result);
    fwrite($file,'<tr');
    if($i%1==0){
      fwrite($file," class='odd'");
    }
    fwrite($file, "><th scope='row' id='r97'>");
    fwrite($file, $data['firstname']);
    fwrite($file,"</th><td>");
    fwrite($file, $data['lastname']);
    fwrite($file, "</td><td>");
    fwrite($file, $data['name']);
    fwrite($file, "</td><td>");
    fwrite($file, codeDate($data['tookoffice']));
    fwrite($file, "</td><td>");
    fwrite($file, codeDate($data['leftoffice']));
    fwrite($file, "</td><td> $");
    fwrite($file, $data['income']);
    fwrite($file, "</td></tr>");
  }
  fwrite($file, "</tbody></table></body></html>");  
  fclose($file);
  echo '1';        // we are done. 
 
}

This is pretty easy actually. We simply pasted the queries without the limit constraints, and kept the arrays to put them into the HTML file. We then returned 1.

How the results will look is really up to you!

Some people have proposed solutions exporting your grid to a PDF file format. You can find all of that information in the forum threads.

Building a Context Menu

This will be the last tutorial of this series!! I know, I know... It's been so much fun reading these! Ok, enough tears! Now we are going to talk about Contextual Menus, or, in a fancier way to say it, 'right-click with your mouse menus'.

First, we'll record the row number where we clicked :

var PresidentListingSelectedRow;

We'll have a basic context menu with three choices : modifying the current chosen president, deleting it and... printing the grid.

Ok, so let's define four functions . The first one will be called everytime we right-click on the grid :

function onPresidentListingEditorGridContextMenu(grid, rowIndex, e) {
    e.stopEvent();
    var coords = e.getXY();
    PresidentListingContextMenu.rowRecord = grid.store.getAt(rowIndex);
    grid.selModel.selectRow(rowIndex);
    PresidentListingSelectedRow=rowIndex;
    PresidentListingContextMenu.showAt([coords[0], coords[1]]);
  }

Ok, as you can see we display the var called 'PresidentListingContextMenu'. We haven't created it yet, we'll do that right after. Let's quickly define the three functions that will be called for each menu item :

function modifyPresidentContextMenu(){
      PresidentListingEditorGrid.startEditing(PresidentListingSelectedRow,1);
  }
 
  function deletePresidentContextMenu(){
      confirmDeletePresidents();
  }
 
  function printListingContextMenu(){
      printListing();
  }

As you can see, those call functions that we have already defined. Ok, let's define that ContextMenu :

PresidentListingContextMenu = new Ext.menu.Menu({
      id: 'PresidentListingEditorGridContextMenu',
      items: [
      { text: 'Modify this President', handler: modifyPresidentContextMenu },
      { text: 'Delete this President', handler: deletePresidentContextMenu },
      '-',
      { text: 'Print this grid', handler: printListingContextMenu }
      ]
   });

The last thing we need is an actionlistener that will call the onPresidentListingEditorGridContextMenu function when we right click. (I tried finding a longer name for that function but...) :

PresidentListingEditorGrid.addListener('rowcontextmenu', onPresidentListingEditorGridContextMenu);

This ends the tutorial. I hope it helped you figure out how to work out the ext grids. You should now be able to understand a lot more code about them. Don't forget to check out the great docs and try playing around with the different methods, properties and events.

You can give me some feedback in this forum thread.

For more advanced features and usage of grids, I want to once again refer you to Michael Lecomte's tutorial.

Happy Griding!

Share this post:
Leave a reply

Written by Nicolas Bize

7 Comments

M

3 years ago

Can you provide SQL file inline as text? The zip is not working ;-(

dee

3 years ago

can you post the sql and the source files thanks great effort

Stefan R

3 years ago

Hello M and dee,

one minute googeling:

CREATE TABLE IF NOT EXISTS `parties` (
  `IDparty` int(11) NOT NULL auto_increment,
  `name` varchar(40) NOT NULL,
  PRIMARY KEY (`IDparty`)
);

INSERT INTO `parties` (`IDparty`, `name`) VALUES
(1, ‘No Party’),
(2, ‘Federalist’),
(3, ‘Democratic-Republican’),
(4, ‘Democratic’),
(5, ‘Whig’),
(6, ‘Republican’);

CREATE TABLE IF NOT EXISTS `presidents` (
  `IDpresident` int(11) NOT NULL auto_increment,
  `IDparty` int(11) NOT NULL,
  `firstname` varchar(20) NOT NULL,
  `lastname` varchar(20)  NOT NULL,
  `tookoffice` date NOT NULL,
  `leftoffice` date NOT NULL,
  `income` decimal(14,2) NOT NULL,
  PRIMARY KEY (`IDpresident`)
);

INSERT INTO `presidents` (`IDpresident`, `IDparty`, `firstname`, `lastname`, `tookoffice`, `leftoffice`, `income`) VALUES
(1, 1, ‘George’, ‘Washington’, ‘1789-04-30’, ‘1797-03-04’, 135246.32),
(2, 2, ‘John’, ‘Adams’, ‘1797-03-04’, ‘1801-03-04’, 236453.34),
(3, 3, ‘Thomas’, ‘Jefferson’, ‘1801-03-04’, ‘1809-03-04’, 468043.25),
(4, 3, ‘James’, ‘Madison’, ‘1809-03-04’, ‘1817-03-04’, 649273.00),
(5, 3, ‘James’, ‘Monroe’, ‘1817-03-04’, ‘1825-03-04’, 374937.23),
(6, 3, ‘John’, ‘Quincy Adams’, ‘1825-03-04’, ‘1829-03-04’, 649824.35),
(7, 4, ‘Andrew’, ‘Jackson’, ‘1829-03-04’, ‘1837-03-04’, 3972753.12),
(8, 4, ‘Martin’, ‘Van Buren’, ‘1837-03-04’, ‘1841-03-04’, 325973.24),
(9, 5, ‘William’, ‘Harrison’, ‘1841-03-04’, ‘1841-04-04’, 25532.08),
(10, 5, ‘John’, ‘Tyler’, ‘1841-04-04’, ‘1845-03-04’, 235542.35),
(11, 4, ‘James’, ‘Polk’, ‘1845-03-04’, ‘1849-03-04’, 3264972.35),
(12, 5, ‘Zachary’, ‘Taylor’, ‘1849-03-04’, ‘1850-07-09’, 35974.35),
(13, 5, ‘Millard’, ‘Fillmore’, ‘1850-07-09’, ‘1853-03-04’, 35792.45),
(14, 4, ‘Franklin’, ‘Pierce’, ‘1853-03-04’, ‘1857-03-04’, 357938.35),
(15, 4, ‘James’, ‘Buchanan’, ‘1857-03-04’, ‘1861-03-04’, 357937.35),
(16, 6, ‘Abraham’, ‘Lincoln’, ‘1861-03-04’, ‘1865-04-15’, 25073.40),
(17, 4, ‘Andrew’, ‘Johnson’, ‘1865-04-15’, ‘1869-03-04’, 356279.35),
(18, 6, ‘Ulysses’, ‘Grant’, ‘1869-03-04’, ‘1877-03-04’, 357938.35),
(19, 6, ‘Rutherford’, ‘Hayes’, ‘1877-03-04’, ‘1881-03-04’, 2359737.35),
(20, 6, ‘James’, ‘Garfield’, ‘1881-03-04’, ‘1881-09-19’, 3579.24),
(21, 6, ‘Chester’, ‘Arthur’, ‘1881-09-19’, ‘1995-03-04’, 357932.35),
(22, 4, ‘Grover’, ‘Cleveland’, ‘1885-03-04’, ‘1889-03-04’, 369723.35),
(23, 6, ‘Benjamin’, ‘Harrison’, ‘1889-03-04’, ‘1893-03-04’, 357392.35),
(24, 4, ‘Grover’, ‘Cleveland’, ‘1893-03-04’, ‘1897-03-04’, 3275935.35),
(25, 6, ‘William’, ‘McKinley’, ‘1897-03-04’, ‘1901-09-14’, 35793.35),
(26, 6, ‘Theodore’, ‘Roosevelt’, ‘1901-09-14’, ‘1909-03-04’, 0.00), 
(27, 6, ‘William’, ‘Taft’, ‘1909-03-04’, ‘1913-03-04’, 239597.35),   
(28, 4, ‘Woodrow’, ‘Wilson’, ‘1913-03-04’, ‘1921-03-04’, 32579743.34),
(29, 6, ‘Warren’, ‘Harding’, ‘1921-03-04’, ‘1923-08-02’, 35793.24), 
(30, 6, ‘Calvin’, ‘Coolidge’, ‘1923-08-02’, ‘1929-03-04’, 296529.24), 
(31, 6, ‘Herbert’, ‘Hoover’, ‘1929-03-04’, ‘1933-03-04’, 3525.25), 
(32, 4, ‘Franklin’, ‘Roosevelt’, ‘1933-03-04’, ‘0000-00-00’, 35293734.35),
(33, 4, ‘Harry’, ‘Truman’, ‘1945-04-12’, ‘1953-01-20’, 23579358.35),
(34, 6, ‘Dwight’, ‘Eisenhower’, ‘1953-01-20’, ‘1961-01-20’, 25973535.35),
(35, 4, ‘John’, ‘Kennedy’, ‘1961-01-20’, ‘1963-11-22’, 46081.24),
(36, 4, ‘Lyndon’, ‘Johnson’, ‘1963-11-22’, ‘1969-01-20’, 2503759.35),
(37, 6, ‘Richard’, ‘Nixon’, ‘1969-01-20’, ‘1974-08-09’, 3259744.53),
(38, 6, ‘Gerald’, ‘Ford’, ‘1974-08-09’, ‘1977-01-20’, 643076.05), 
(39, 4, ‘Jimmy’, ‘Carter’, ‘1977-01-20’, ‘1981-01-20’, 1205735.25),
(40, 6, ‘Ronald’, ‘Reagan’, ‘1981-01-20’, ‘1989-01-20’, 99867297.35),
(41, 6, ‘George H.’, ‘Bush’, ‘1989-01-20’, ‘1993-01-20’, 92048204.24),
(42, 4, ‘Bill’, ‘Clinton’, ‘1993-01-20’, ‘2001-01-20’, 12073975.24);

 

Akhtar

3 years ago

hi,
i have use this code but got error on this line….
PresidentsColumnModel = new Ext.grid.ColumnModel(

and error msg is ’ ColumnModel’ is not undefind. please hlp me..

Akhtar

Akhtar

3 years ago

Hi,

Thank you very much. its working fine.

raghu

3 years ago

can u please give an example with java servlets or jsp as server side code.

User007

3 years ago

Hi, I am a newbie in Ext , I have created an example with jsp creating and returing JSONArray, and populating it in the gird. But some how it seems it does not work

Here is a piece of code I have got:

var store = new Ext.data.Store({
  proxy: new Ext.data.HttpProxy(
  new Ext.data.Connection({
  url:‘sendTwoPaging2.jsp’
  })
  ),
  reader:new Ext.data.JsonReader({
  totalProperty:‘total’,
  root:‘rows’,
  id:‘schedule’,
  fields:[‘organization’,‘my_id’,‘myDate’]
  }),
  remoteSort:true
});


‘sendTwoPaging2.jsp’ : it returns the JSONArray

Grid to display the data:

var columnModel = new Ext.grid.ColumnModel([
  {header:‘organization’, width:120, sortable:true, dataIndex:‘organization’},
  {header:‘my_id’, width:120, sortable:true, dataIndex:‘my_id’},
  {header:‘myDate’, width:120, sortable:true, dataIndex:‘myDate’}
]);
columnModel.defaultSortable = true;

var grid = new Ext.grid.GridPanel({
  renderTo:‘pagingJson-grid’,
  store:store,
  cm:columnModel,
 
  viewConfig:{
  forceFit:true
  },
  title:’ grid title’,
  width:500,
  height:500,
  loadMask:true,
  frame:true,
  bbar:new Ext.PagingToolbar({
  pageSize:10,
  store:store,
  displayInfo:true,
  displayMsg: ‘total{2} {0} - {1}’,
  emptyMsg:‘no data’
  })
});
store.load();
  grid.render();
  store.load({params:{start:0, limit:10}});

});

Leave a comment:

Commenting is not available in this channel entry.