untouched
6 Oct 2008, 2:37 PM
Thought it may be helpful to others to view my simple implementation of an editor grid. Have read lots of examples of editors and some are fantastic although most are very complex.
Note: This is a very basic implementation and is used on my intranet here (8 users), as such security was not a concern. I am posting this purely because it would have helped me back when I started learning Ext, so no flamage k, k.
The basic engine for this grid is through simple, Stored Procedures in PHP that are called via AJAX post requests, you set the store of the grid to the Select SP, add a button to call the Add SP, and use the afteredit event to call the Update SP.
The Database
I use mysql gui so no db scripts
there are 2 tables used here:
hrdb1_users:
my main userlist, fields are:
USER_ID: auto incrementing id
USERNAME: login/Search name
PASSWORD: MD5 encrytped password
NAME: escaped full name
EMAIL: email addy
AUTH_LEVEL: student / assessor / adminola_contact_log:
This is the table we will be editing, field are:
CL_ID: auto incrementing id
CL_DATETIME: timestamp
CL_USERNAME: student who this contact is redarding
CL_TYPE: contact method
CL_TYPE_DETAIL: contact detail (phone no etc...)
CL_INIT_BY: person who made the contact
CL_CONTACTED: who was contacted
CL_DETAIL: the actual contact log
PHP Code:
This PHP file handles most if not all sql calls, it does this by using call_user_func_array in PHP, this enables you to name a function and its parameters in your post data request from ext.
/*
* @param function_name
*/
$function_name = $_POST['function_name'];
/*
* @param param_list
* | delimited list of parameters to be
* passed to the called function as an array.
*/
if ($_POST['param_list']!='')
$param_list = explode("|",$_POST['param_list']);
/*
Functions go here
*/
/*-----------------------------------
This is the last line of the PHP file, all functions need to be declared first
*/
call_user_func_array($function_name, array($param_list));Three PHP functions
The Select Function does a simple select based on one parameter
The Add function creates a new row in the db, adding any defaults as required
The Update function is called from the editor grid everytime a cell is updated
function select_contact_log($p){
/*
* $p[0] : user_id
*/
$sql = "
SELECT
*
FROM
ola_contact_log
WHERE
cl_username IN
( SELECT USERNAME FROM hrdb1_users WHERE USER_ID=".$p[0]." )
";
sel_sql($sql);
}
function add_contact_log($p){
/*
* $p[0] : user_id
*/
$sql = "
INSERT INTO ola_contact_log
(cl_username)
SELECT USERNAME FROM hrdb1_users where USER_ID = ".$p[0]."
";
up_sql($sql);
}
function update_contact_log($p){
/*
* $p[0] : contact_log_id
* $p[1] : field name
* $p[2] : new value
*/
$sql = "
UPDATE ola_contact_log
SET ".$p[1]."=".simple_encode($p[2])."
WHERE cl_id = ".$p[0]."
";
up_sql($sql);
}The two sql methods called from above:
sel_sql returns a json recordset
up_sql performs a sql command and returns {success:true} or the error msg as required.
function sel_sql($sq){
$result = mysql_query($sq,get_conn());
$num=mysql_affected_rows();
$objJSON=new mysql2json();
$jsondata = trim($objJSON->getJSON($result,$num));
print(substr($jsondata, 0, $jsondata.length -1 ));
}
function up_sql($sq, $final_q){
mysql_query($sq,get_conn());
if (mysql_error()){
if ($final_q==true)
echo "{success:false, error:'error in function ".$function_name."'}";
else
return false;
} else {
if ($final_q==true)
echo "{success:true}";
else
return true;
}
}javascript:
Some notes on this js:
My Student Selector combo unescapes student names using Ext.data.Record.create and convert as below:
convert:function(v){return unescape(v)}calls to mysql_functions.php are passing the parameters 'sid' and 'rand' these are for security of my app and i have not explained their usage here.
xtype: 'editorgrid',
resizable:true,
store: new Ext.data.JsonStore({
url: 'mysql_functions.php',
baseParams:{
function_name:'contact_log',
rand:logindata.rand,
sid:logindata.sid
},
root: "data",
resizable:true,
fields: ["cl_id", "cl_datetime", "cl_type", "cl_type_detail", "cl_init_by", "cl_contacted", "cl_detail", "cl_username", "cl_time"],
autoLoad: false
}),
columns: [{
header: "Date Time",
width: 150,
sortable: true,
editor: q_editors('date'),
dataIndex: "cl_datetime"
}, {
header: "Contacted",
width: 150,
sortable: true,
editor: q_editors('text'),
dataIndex: "cl_contacted"
}, {
header: "Method",
width: 130,
sortable: true,
editor: q_editors('cl_type'),
dataIndex: "cl_type"
}, {
header: "Method Detail",
width: 200,
sortable: true,
editor: q_editors('text'),
dataIndex: "cl_type_detail"
}, {
header: "Contacted By",
width: 150,
sortable: true,
editor: q_editors('text'),
dataIndex: "cl_init_by"
}, {
header: "Details",
width: 200,
sortable: false,
editor: q_editors('text'),
dataIndex: "STATUS"
}],
sm: new Ext.grid.RowSelectionModel({
singleSelect: true
}),
id: 'contact_log_grid',
loadMask: true,
height:coreHeight()-50,
frame: true,
iconCls: 'icon-grid',
border:false,
listeners:{
afteredit: function(e){
Ext.Ajax.request({
url: 'mysql_functions.php',
params:{
rand:logindata.rand,
sid:logindata.sid,
function_name:'update_contact_log',
param_list:Ext.getCmp('contact_log_grid').getSelectionModel().getSelected().get('cl_id')+'|'+e.field+'|'+e.value
}
})
}
},
tbar:[{
xtype:'tbtext',
text:'Student:'
},{
xtype: 'combo',
fieldLabel: 'Username',
id:'contact_log_student_combo',
store: new Ext.data.Store({
url: 'getquery.php',
baseParams:{
rand:logindata.rand,
sid:logindata.sid,
qName:'student_selector'
},
reader:new Ext.data.JsonReader({
root: "data"
},
Ext.data.Record.create(
[
{name:'USER_ID'},
{name:'uname',
convert:function(v){
return unescape(v)
}
}
]
)
),
autoLoad: false
}),
displayField:'uname',
valueField:'USER_ID',
forceSelection:true,
resizable:true,
triggerAction:'all',
minChars:3,
queryParam:'qParam1',
width:250,
listeners:{
select:function(combo, record, index){
Ext.getCmp('contact_log_grid').getStore().load({
method:'POST',
params:{
param_list:record.get('USER_ID')
}
})
}
}
},'-',{
icon:'im/icon_refresh.png',
text:'',
cls:"x-btn-text-icon",
tooltip:'Refresh',
handler:function(){
Ext.getCmp('contact_log_grid').getStore().reload();
}
},'-',{
text:'New',
handler:function(){
Ext.Ajax.request({
url: 'mysql_functions.php',
method: 'POST',
params:{
rand:logindata.rand,
sid:logindata.sid,
function_name:'add_contact_log',
param_list:studentid=Ext.getCmp('contact_log_student_combo').getValue()
},
success: function(response){
Ext.getCmp('contact_log_grid').getStore().reload();
}
})
Ext.getCmp('contact_log_grid').getStore().reload();
}
}]
}any questions, feel free to ask...
Hope that helps.
Note: This is a very basic implementation and is used on my intranet here (8 users), as such security was not a concern. I am posting this purely because it would have helped me back when I started learning Ext, so no flamage k, k.
The basic engine for this grid is through simple, Stored Procedures in PHP that are called via AJAX post requests, you set the store of the grid to the Select SP, add a button to call the Add SP, and use the afteredit event to call the Update SP.
The Database
I use mysql gui so no db scripts
there are 2 tables used here:
hrdb1_users:
my main userlist, fields are:
USER_ID: auto incrementing id
USERNAME: login/Search name
PASSWORD: MD5 encrytped password
NAME: escaped full name
EMAIL: email addy
AUTH_LEVEL: student / assessor / adminola_contact_log:
This is the table we will be editing, field are:
CL_ID: auto incrementing id
CL_DATETIME: timestamp
CL_USERNAME: student who this contact is redarding
CL_TYPE: contact method
CL_TYPE_DETAIL: contact detail (phone no etc...)
CL_INIT_BY: person who made the contact
CL_CONTACTED: who was contacted
CL_DETAIL: the actual contact log
PHP Code:
This PHP file handles most if not all sql calls, it does this by using call_user_func_array in PHP, this enables you to name a function and its parameters in your post data request from ext.
/*
* @param function_name
*/
$function_name = $_POST['function_name'];
/*
* @param param_list
* | delimited list of parameters to be
* passed to the called function as an array.
*/
if ($_POST['param_list']!='')
$param_list = explode("|",$_POST['param_list']);
/*
Functions go here
*/
/*-----------------------------------
This is the last line of the PHP file, all functions need to be declared first
*/
call_user_func_array($function_name, array($param_list));Three PHP functions
The Select Function does a simple select based on one parameter
The Add function creates a new row in the db, adding any defaults as required
The Update function is called from the editor grid everytime a cell is updated
function select_contact_log($p){
/*
* $p[0] : user_id
*/
$sql = "
SELECT
*
FROM
ola_contact_log
WHERE
cl_username IN
( SELECT USERNAME FROM hrdb1_users WHERE USER_ID=".$p[0]." )
";
sel_sql($sql);
}
function add_contact_log($p){
/*
* $p[0] : user_id
*/
$sql = "
INSERT INTO ola_contact_log
(cl_username)
SELECT USERNAME FROM hrdb1_users where USER_ID = ".$p[0]."
";
up_sql($sql);
}
function update_contact_log($p){
/*
* $p[0] : contact_log_id
* $p[1] : field name
* $p[2] : new value
*/
$sql = "
UPDATE ola_contact_log
SET ".$p[1]."=".simple_encode($p[2])."
WHERE cl_id = ".$p[0]."
";
up_sql($sql);
}The two sql methods called from above:
sel_sql returns a json recordset
up_sql performs a sql command and returns {success:true} or the error msg as required.
function sel_sql($sq){
$result = mysql_query($sq,get_conn());
$num=mysql_affected_rows();
$objJSON=new mysql2json();
$jsondata = trim($objJSON->getJSON($result,$num));
print(substr($jsondata, 0, $jsondata.length -1 ));
}
function up_sql($sq, $final_q){
mysql_query($sq,get_conn());
if (mysql_error()){
if ($final_q==true)
echo "{success:false, error:'error in function ".$function_name."'}";
else
return false;
} else {
if ($final_q==true)
echo "{success:true}";
else
return true;
}
}javascript:
Some notes on this js:
My Student Selector combo unescapes student names using Ext.data.Record.create and convert as below:
convert:function(v){return unescape(v)}calls to mysql_functions.php are passing the parameters 'sid' and 'rand' these are for security of my app and i have not explained their usage here.
xtype: 'editorgrid',
resizable:true,
store: new Ext.data.JsonStore({
url: 'mysql_functions.php',
baseParams:{
function_name:'contact_log',
rand:logindata.rand,
sid:logindata.sid
},
root: "data",
resizable:true,
fields: ["cl_id", "cl_datetime", "cl_type", "cl_type_detail", "cl_init_by", "cl_contacted", "cl_detail", "cl_username", "cl_time"],
autoLoad: false
}),
columns: [{
header: "Date Time",
width: 150,
sortable: true,
editor: q_editors('date'),
dataIndex: "cl_datetime"
}, {
header: "Contacted",
width: 150,
sortable: true,
editor: q_editors('text'),
dataIndex: "cl_contacted"
}, {
header: "Method",
width: 130,
sortable: true,
editor: q_editors('cl_type'),
dataIndex: "cl_type"
}, {
header: "Method Detail",
width: 200,
sortable: true,
editor: q_editors('text'),
dataIndex: "cl_type_detail"
}, {
header: "Contacted By",
width: 150,
sortable: true,
editor: q_editors('text'),
dataIndex: "cl_init_by"
}, {
header: "Details",
width: 200,
sortable: false,
editor: q_editors('text'),
dataIndex: "STATUS"
}],
sm: new Ext.grid.RowSelectionModel({
singleSelect: true
}),
id: 'contact_log_grid',
loadMask: true,
height:coreHeight()-50,
frame: true,
iconCls: 'icon-grid',
border:false,
listeners:{
afteredit: function(e){
Ext.Ajax.request({
url: 'mysql_functions.php',
params:{
rand:logindata.rand,
sid:logindata.sid,
function_name:'update_contact_log',
param_list:Ext.getCmp('contact_log_grid').getSelectionModel().getSelected().get('cl_id')+'|'+e.field+'|'+e.value
}
})
}
},
tbar:[{
xtype:'tbtext',
text:'Student:'
},{
xtype: 'combo',
fieldLabel: 'Username',
id:'contact_log_student_combo',
store: new Ext.data.Store({
url: 'getquery.php',
baseParams:{
rand:logindata.rand,
sid:logindata.sid,
qName:'student_selector'
},
reader:new Ext.data.JsonReader({
root: "data"
},
Ext.data.Record.create(
[
{name:'USER_ID'},
{name:'uname',
convert:function(v){
return unescape(v)
}
}
]
)
),
autoLoad: false
}),
displayField:'uname',
valueField:'USER_ID',
forceSelection:true,
resizable:true,
triggerAction:'all',
minChars:3,
queryParam:'qParam1',
width:250,
listeners:{
select:function(combo, record, index){
Ext.getCmp('contact_log_grid').getStore().load({
method:'POST',
params:{
param_list:record.get('USER_ID')
}
})
}
}
},'-',{
icon:'im/icon_refresh.png',
text:'',
cls:"x-btn-text-icon",
tooltip:'Refresh',
handler:function(){
Ext.getCmp('contact_log_grid').getStore().reload();
}
},'-',{
text:'New',
handler:function(){
Ext.Ajax.request({
url: 'mysql_functions.php',
method: 'POST',
params:{
rand:logindata.rand,
sid:logindata.sid,
function_name:'add_contact_log',
param_list:studentid=Ext.getCmp('contact_log_student_combo').getValue()
},
success: function(response){
Ext.getCmp('contact_log_grid').getStore().reload();
}
})
Ext.getCmp('contact_log_grid').getStore().reload();
}
}]
}any questions, feel free to ask...
Hope that helps.