Updated post:
I made an update for this example. To make sure all new readers see this instead of the post before, I edit it here.
The new WORKING example is as followed:
The js file:
PHP Code:
var GridUI = function() {
//enable Quicktips
Ext.QuickTips.init();
//set vars
var ds; //datastore for grid
var cm; //columnModel
var grid; //component
var fm = Ext.form, Ed = Ext.grid.GridEditor; // shorthand alias
//create the datastores
function setupDataSource() {
//create the Data Store for the grid
ds = new Ext.data.Store({
//set the http-proxy (link to the php document)
proxy: new Ext.data.ScriptTagProxy({
url: 'sample.php?ac=showData' //a function in your php-script must be activated when ac = showdata
}),
//set up the JsonReader
reader: new Ext.data.JsonReader({
root: 'results',
totalProperty: 'total',
id: 'id'
},[
{name: 'name', type: 'string', mapping: 'name'} //columnmapping
])
});
//load datastores
ds.load();
}
//create the columnmodel
function getColumnModel() {
cm = new Ext.grid.ColumnModel([{
header: "Names",
dataIndex: 'name',
width: 150,
editor: new Ed(new fm.TextField({
allowBlank: true,
maxLength: 50
}))
}]);
//set the default for sorting the columns
cm.defaultSortable = false;
return cm;
}
//create the grid
function buildGrid() {
//create the form
var gridForm = new Ext.form.Form({});
//create the grid
var grid = new Ext.grid.EditorGrid('editor-grid', {
ds: ds,
cm: getColumnModel(),
enableColLock:false,
selModel: new Ext.grid.RowSelectionModel({singleSelect:true})
});
//set the layout for the grid
var layout = Ext.BorderLayout.create({
center: {
margins:{left:3,top:3,right:3,bottom:3},
panels: [new Ext.GridPanel(grid)]
}
}, 'grid-panel');
//render the grid
grid.render();
//set the header
var gridHead = grid.getView().getHeaderPanel(true);
var tb = new Ext.Toolbar(gridHead, [{}]);
//activate function updateDB when a cell is edited
grid.on('afteredit', updateDB);
function updateDB(oGrid_event){
gridForm.submit(
{
//waitMsg: 'Saving changes, please wait...',
url:'sample.php?ac=saveData&field='+oGrid_event.field+'&row='+oGrid_event.row+'&value='+oGrid_event.value, //php function that saves the data
success:function(form, action) {
alert('Congrats! Your changes were saved!!!!');
},
failure: function(form, action) {
alert('Oops the delete did not work out too well!');
}
}
);
}
//render form
gridForm.render('editGrid');
}
return {
//the init
init : function() {
setupDataSource();
buildGrid();
},
getDataSource: function() {
return ds;
}
}
}();
Ext.onReady(GridUI.init, GridUI, true);
The php-file:
PHP Code:
<?php
//make database connection
mysql_connect("localhost", "YOUR USERNAME", "YOUR PASSWORD") or
die("Could not connect: " . mysql_error());
mysql_select_db("YOUR DATABASE");
if(isset($_GET['ac']) && $_GET['ac']=='showData')
showData();
elseif(isset($_GET['ac']) && $_GET['ac']=='saveData')
saveData($_GET['field'], $_GET['row'], $_GET['value']);
function showData()
{
//query with data
$result = mysql_query("SELECT id, name FROM names");
$rows = mysql_num_rows($result);
while($rec = mysql_fetch_array($result))
$arr[] = $rec;
echo $_GET['callback'].'({"total":"'.$rows.'","results":'.json_encode($arr).'})';
}
function saveData($field, $row, $value)
{
/*
* field: the dataIndex which is being updated
* $row: the row that is being updated
* $value: the new value
*/
//look which record is being updated
$result = mysql_query("SELECT id FROM names");
while($rec = mysql_fetch_array($result))
$id[] = $rec['id'];
//NOTE: this isn't the best way to save your data!
//What I do is look in the database and put all the ID's in an array.
//Then I know that $row is the right array index.
//But when you change the order of the records for example, this isn't enough to know the row
//However, this example works :)
//save data to database
if($field=='name') //the field name is being updated
$result = mysql_query("UPDATE `names` SET `name` = '$value' WHERE `id`='$id[$row]';");
}
?>
The html-file:
PHP Code:
<html>
<head>
<title>Sample of a grid</title>
<script language="JavaScript" type="text/javascript" src="adapter/ext/ext-base.js"></script>
<script language="JavaScript" type="text/javascript" src="ext-all.js"></script>
<script language="JavaScript" type="text/javascript" src="sample.js"></script>
<link rel="stylesheet" type="text/css" href="resources/css/ext-all.css" media="screen" />
</head>
<body>
<div id="grid-panel" style="width:1024px;height:668px;">
<div id="editor-grid"></div>
<div id="editGrid">
</div>
</div>
</body>
</html>
The database:
PHP Code:
--
-- Table structure for table `names`
--
CREATE TABLE `names` (
`id` int(8) unsigned NOT NULL auto_increment,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- Dumping data for table `names`
--
INSERT INTO `names` (`id`, `name`) VALUES
(1, 'Testname'),
(2, 'Testname2'),
(3, 'Testname3'),
(4, 'Testname4');
You acces the grid by going to the sample.html page!
NOTE: Don't forget to set your database username and password in the php-file and include ext-base.js and ext-all.js!!
First post:
In my search on this forum to read a DataStore, display the data in a grid, edit the cells in a grid and then update the database using php and mysql I couldn't find a full working sample. So, now I found out how to do it, I want to share it with you all.
I made the example as easy as possible. Because I'm a new EXT user, there are probably better known ways to update your database, but the example I made must work after a little editting (you have to make your queries for selecting data yourself!) and will hopefully help all the others I saw looking for help on this.
With help of other posts from users (thanks all) I came to the following code.
Create your grid (to make it easy, I made one column): sample.js
PHP Code:
var GridUI = function() {
//enable Quicktips
Ext.QuickTips.init();
//set vars
var ds; //datastore for grid
var cm; //columnModel
var grid; //component
var fm = Ext.form, Ed = Ext.grid.GridEditor; // shorthand alias
//create the datastores
function setupDataSource() {
//create the Data Store for the grid
ds = new Ext.data.Store({
//set the http-proxy (link to the php document)
proxy: new Ext.data.ScriptTagProxy({
url: 'index.php?ac=showdata' //a function in your php-script must be activated when ac = showdata
}),
//set up the JsonReader
reader: new Ext.data.JsonReader({
root: 'results',
totalProperty: 'total',
id: 'id'
},
{name: test, type: 'string', mapping: 'test'} //columnmapping
)
});
//load datastores
ds.load();
}
//create the columnmodel
function getColumnModel() {
cm = new Ext.grid.ColumnModel([{
header: "Testcolumn",
dataIndex: 'test',
//width: 150,
editor: false
}]);
//set the default for sorting the columns
cm.defaultSortable = false;
return cm;
}
//create the grid
function buildGrid() {
//create the form
var gridForm = new Ext.form.Form({});
//create the grid
var grid = new Ext.grid.EditorGrid('editor-grid', {
ds: ds,
cm: getColumnModel(),
enableColLock:false,
selModel: new Ext.grid.RowSelectionModel({singleSelect:true})
});
//set the layout for the grid
var layout = Ext.BorderLayout.create({
center: {
margins:{left:3,top:3,right:3,bottom:3},
panels: [new Ext.GridPanel(grid)]
}
}, 'grid-panel');
//render the grid
grid.render();
//set the header
var gridHead = grid.getView().getHeaderPanel(true);
var tb = new Ext.Toolbar(gridHead, [{}]);
//activate function updateDB when a cell is edited
grid.on('afteredit', updateDB);
function updateDB(oGrid_event){
jsonData = "[";
for(i=0;i<ds.getCount();i++) {
record = ds.getAt(i);
jsonData += Ext.util.JSON.encode(record.data) + ",";
}
jsonData = jsonData.substring(0,jsonData.length-1) + "]";
gridForm.submit(
{
//waitMsg: 'Saving changes, please wait...',
url:'index.php?ac=saveData', //php function that saves the data
params:{data:jsonData},
success:function(form, action) {
alert('Congrats! Your changes were saved!!!!');
},
failure: function(form, action) {
alert('Oops the delete did not work out too well!');
}
}
);
}
//render form
gridForm.render('editGrid');
}
return {
//the init
init : function() {
setupDataSource();
buildGrid();
},
getDataSource: function() {
return ds;
}
}
}();
Ext.onReady(GridUI.init, GridUI, true);
The HTML-code: index.tpl
PHP Code:
<html>
<head>
<script src="ext-all.js" type="text/javascript"></script>
<script src="adapter/ext/ext-base.js" type="text/javascript"></script>
<script src="sample.js" type="text/javascript"></script>
<link href="resources/css/ext-all.css" media="screen" rel="Stylesheet" type="text/css" />
</head>
<body>
<div id="grid-panel" style="width:1024px;height:668px;">
<div id="editor-grid"></div>
<div id="editGrid">
</div>
</div>
</body>
</html>
The php code (index.php):
PHP Code:
function showData()
{
//make your query here that selects all your data for the grid
//you have to modify the $res and fetch yourself to make the query succesfull
$res = $this->dbi->query('your query here');
while($obj =& $this->dbi->fetch())
{
$arr[] = $obj;
}
//encode your data for the jsonreader
echo $_GET['callback'].'({"total":"'.$rows.'","results":'.json_encode($arr).'})';
}
function saveData()
{
$data = json_decode(stripslashes($_POST['data']));
//make here a query that updates the database
}
Remember you still have to do some work yourself: make the database queries and modify the ColumnModel and DataStore so it matches your data.
$data in saveData() is an 2D array, which you have to read by a foreach loop and put it in the database. Use echo var_dump($data); to see how the array is build!
Note that in this example the whole grid is submitted once you edit a sell, so if you have a 100+ record grid it becomes slower and slower.
When I find out how, I post an example in this topic how to update only one cell in the database after editing.
Good luck!
Example of editor-grid using php and mysql to save data
Reply With Quote