One of the biggest problems I faced when developing a good editable grid is the ability to properly populate the dynamic drop-down boxes within the table. ExtJS performs this task much better than most of the packages that I have worked with so far. The following will walk you through and explain an editable grid that has been created using MySQL, PHP and ExtJS. In order to replicate the program you will have to create a table that holds the options, so open MySQL and run the following script (assumes that a database named ‘test’ has been created:
CREATE TABLE IF NOT EXISTS `test`.`some_options` (
`so_pk_id` INT NOT NULL ,
`option_name` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`so_pk_id`) )
ENGINE = MyISAM;
Insert into test.some_options (so_pk_id,option_name) values ('1','Red')
Insert into test.some_options (so_pk_id,option_name) values ('2','Yellow')
Insert into test.some_options (so_pk_id,option_name) values ('3','Green')
Now we need some fake data for out table, so let’s create the following which will be the table that we will be editing via our editable grid.
CREATE TABLE `test`.`light_test` (
`pk_light_id` INT NOT NULL ,
`color` INT NULL ,
`name` VARCHAR(45) NULL ,
`location` VARCHAR(45) NULL );
Insert into test.light_test (pk_light_id,color,name,location) values ('1','2','Some Light','Around the corner')
Ok now that we have the tables set up we will need to create a few pages that will fetch the data from our database and place them into what Ext calls stores, these data stores will contain the original values.
First we will need to connect to the ‘some_options’ table that was created above and get all of the data from the table into a nice JSON string. Fortunately, this is much easier than it sounds when using PHP since there is a handy function called json_encode() that returns the query results in a JSON string, so the code to do this is as follows:
<?php
// The source code of optionsstire.php
$dbhost = '127.0.0.1:3306';
$dbuser = 'test_user';
$dbpass = '';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
$dbname = 'test';
mysql_select_db($dbname);
$query = "select * from some_options";
$result = mysql_query($query);
$num = mysql_numrows($result);
$options = array("results" => $num);
$i = 0;
while ($row = mysql_fetch_assoc($result)) {
$options["data"][$i] = $row;
$i++;
}
echo json_encode($options);
?>
…. and of course we need the same code for the main data grid store, which in reality is as simple as changing the line $query = “select * from some_options”; to $query = “select * from light_test”; ..
Now that the foundation has been put in place let’s move on to those stores that were mentioned above. In order to get data into your drop down box, or data grid you will need to connect the data in the lookup table that is in the database with the drop down or combo box. The code for a simple JSON store that matches the PHP code above is:
var option_dd_store = new Ext.data.JsonStore({
storeId:'option_dd_store',
autoLoad: true,
url:'optionsstore.php',
idProperty:'',
root:'data',
totalProperty: 'results',
fields:[
{ name:'so_pk_id',type:'int' },
{ name:'option_name',type:'string' }
]
});
The url property above contains the location to the PHP page that is querying the database. Autoload will ensure that the data is loaded in the store on page load and not at a later time. The fields must match the names given in the JSON file. We will also need a store that contains the data fro the main grid that this combo box will reside in, this code is :
var stoplight_grid_store = new Ext.data.JsonStore({
storeId:'stoplight_grid_store',
autoLoad:true,
url:'mainGridStore.php',
idProperty:'pk_light_id',
root:'data',
totalProperty: 'results',
fields: [
{name:'pk_light_id',type:'int' },
{name:'color',type:'int' },
{name:'name',type:'string' },
{name:'location',type:'string' }
]
});
Now we need the code that creates the drop down box itself:
var options_dd = new Ext.form.ComboBox({
triggerAction: "all",
store: option_dd_store,
displayField:'option_name',
valueField:'so_pk_id',
hiddenName:'so_pk_id'
});
Notice that we defined the store by passing the store variable, and we also defined a hiddenName, and value field as well as the displayField. The hidden field will become extremely important when we want to start passing the variable back to a page that will save the data to the database when it changes on the client side. Now it is time to place the code for the grid on the page.
var exec_type_rpt_sg_grid = new Ext.grid.EditorGridPanel({
title: 'Stop Light Change Plan',
store: 'stoplight_grid_store',
id:'stoplight_grid',
renderTo:'light_div',
layout:'fit',
height: 300,
columns:[{
xtype: 'gridcolumn',
header: 'Light Color',
sortable: true,
resizable: true,
width: 150,
dataIndex: 'color',
editor: options_dd,
renderer:function(value)
{
var index = option_dd_store.find("so_pk_id",value);
var displayName = option_dd_store.getAt(index).get("option_name");
return displayName;
}
},{
xtype: 'gridcolumn',
header: 'Name',
sortable: true,
resizable: true,
width: 300,
dataIndex: 'name'
},{
xtype: 'gridcolumn',
header: 'Location',
sortable: true,
resizable: true,
width: 500,
dataIndex: 'location'
}
]
});
So here is where the real world part of the example comes into play, when you have a table that contains lookup data in another table the main table (above) usually only has a reference to the primary key of the lookup table, so in order to display the ‘displayValue’ from your combo box in the grid opposed to the numerical value that would be the key you will need to use the renderer above which takes the vale from the grid and references it to the options_dd store and then returns the corresponding key.
Later this week we’ll go over updating the database behind the grid, but for now you have the ability to place a dependent dropdown box in the ExtJS grid and have the display value show opposed to the key.
