Prado PHP Framework HOW TO 5 - TDataGrid with TDropDownList, TCheckBox

One of the good component's of Prado is it's DataGrid. But i must admit, it refreshes the page so much. In this point you'll understand how javascript is good. But for the one's who dealed with the same problem that how to use TCheckBox or TDropDownList in TDataGrid, i want to explain how i overcome it in here, not how javascript is better.

In here i use basics of TDataGrid if you want to see more about TDataGrid, you can look here.

Also to use this example i define a database schema named as 'test' and username and password also named as 'test'. Then i define 2 tables in my schema, one for items and one for types. Items have type_id's as one of their row. So we can get the values from two tables at the same time while using drop down column in our data grid. I also include the sql file to the source code.

First we define our data grid component;
<com:TDataGrid
ID="DataGrid"
DataKeyField="ID"
AutoGenerateColumns="false"
OnItemCreated="itemCreated"
OnEditCommand="editItem"
OnUpdateCommand="saveItem"
OnDeleteCommand="deleteItem"
OnCancelCommand="cancelItem"
>

Then we define our components that'll be in our data grid;
----------------------------The checkbox--------------------------------
<com:TTemplateColumn ID="checkbox_column">
<prop:ItemTemplate>
<com:TCheckBox ID="select" />
<com:THiddenField ID="item_id" />
</prop:ItemTemplate>
</com:TTemplateColumn>

-------------------------------Edit Column-------------------------------
<com:TEditCommandColumn
UpdateText="Save"
EditText="Edit"
CancelText="Cancel"
/>

------------------------------Drop Down List Column--------------------
<com:TDropDownListColumn
ID="dropdown_column"
DataTextField="type_name"
ListDataSource=<%= $this->LoadDropDown() %>
ListValueField="type_id"
/>

------------------------------Text Box Column----------------------------
<com:TBoundColumn
ID="textbox_column"
DataField="item_name"
/>

------------------------------Delete Column-------------------------------
<com:TButtonColumn
ID="delete_column"
Text="Delete"
CommandName="delete"
/>

At the end we close the data grid component's tag.
</com:TDataGrid>

And we define a button to delete the selected one's.
<com:TButton ID="delete_selecteds"
Text="Delete Selecteds"
onClick="DeleteSelecteds"
/>

And we mustn't forget to enclose our TDataGrid and TButton in TForm component. Now our html page is ready, so get going to write the php page.

We define a local variable to populate our data grid everytime and onLoad function first;
private $_data=null;
public function onLoad($param){
parent::onLoad($param);
if(!$this->IsPostBack){
$this->DataGrid->DataSource=$this->Data;
$this->DataGrid->dataBind();
}
}

We bind the data we have, to our data grid, above. Then comes our second function that creates a TList component and populates our drop down list column in our data grid with it;
public function LoadDropDown(){
$db_connection = $this->Application->Modules['dbconnection']->Database;
$db_connection->Active = true;

$sql_query = "SELECT * FROM types";
$command = $db_connection->createCommand($sql_query);
$db_records = $command->query();

$data=new TList; $i=0;
foreach($db_records as $key)
$data->add(array('type_id'=>$key["type_name"]));

$db_connection->active = false;
return $data;
}

After that we started to define our public functions that are called by our data grid component when a delete, update, save or cancel command comes;
public function itemCreated($sender,$param){
$item=$param->Item;
if($item->ItemType==='EditItem'){
$item->textbox_column->TextBox->Columns=10;
}
if($item->ItemType==='Item' || $item->ItemType==='AlternatingItem' || $item->ItemType==='EditItem'){
$item->delete_column->Button->Attributes->onclick='if(!confirm(\'Are you sure?\')) return false;';
}
}
This function above, provides a confirm box to our delete buttons to check if person is sure to delete the item.

public function editItem($sender,$param){
$this->DataGrid->EditItemIndex=$param->Item->ItemIndex;
$this->DataGrid->DataSource=$this->Data;
$this->DataGrid->dataBind();
}
With this function above, we open the text boxes, drop down lists instead of the items. So user can edit them.

public function saveItem($sender,$param){
$item=$param->Item;
if($this->_data===null)
$this->loadData();

$updateRow=null;
foreach($this->_data as $index=>$row)
if($row['ID']===$this->DataGrid->DataKeys[$item->ItemIndex])
$updateRow=&$this->_data[$index];

if($updateRow!==null){
$old_typename = $updateRow['type_name'];
$old_itemname = $updateRow['item_name'];
}

$new_typename = $item->dropdown_column->DropDownList->SelectedValue;
$new_itemname = $item->textbox_column->TextBox->Text;

$db_connection = $this->Application->Modules['dbconnection']->Database;
$db_connection->Active = true;
$transaction = $db_connection->beginTransaction();
try{
$sql_query = "Select type_id from types where type_name='". $new_typename ."'";
$command = $db_connection->createCommand($sql_query);
$new_typeid = $command->queryScalar();

$sql_query = "Select type_id from types where type_name='". $old_typename ."'";
$command = $db_connection->createCommand($sql_query);
$old_typeid = $command->queryScalar();

$sql_query = "Select item_id from items where item_type='" . $old_typeid . "' and item_name='". $old_itemname
."'";
$command = $db_connection->createCommand($sql_query);
$item_id = $command->queryScalar();

$sql_query = "update items set item_name='". $new_itemname ."', item_type='". $new_typeid ."' where item_id='
". $item_id ."'";
$command = $db_connection->createCommand($sql_query);
$command->execute();
$transaction->commit();
}catch(Exception $ex){
$transaction->rollback();
var_dump("UPPSSS Update Failed");
return;
}

$this->updateGrid(
$this->DataGrid->DataKeys[$item->ItemIndex],
$new_typename,
$new_itemname
);

$this->DataGrid->EditItemIndex=-1;
$this->DataGrid->DataSource=$this->Data;
$this->DataGrid->dataBind();
$db_connection->Active = false;
}
With this function above, if user clicks the save button in edit mode, we first find the old values of the item and then find the item's id. Then we get the new values of the item from text box and drop down list and update the item with them.

public function cancelItem($sender,$param){
$this->DataGrid->EditItemIndex=-1;
$this->DataGrid->DataSource=$this->Data;
$this->DataGrid->dataBind();
}
With this function above, if user clicks the cancel button in edit mode, we do nothing and refresh the list.

public function deleteItem($sender,$param){
$this->deleteFromGrid($this->DataGrid->DataKeys[$param->Item->ItemIndex]);
$this->DataGrid->EditItemIndex=-1;
$this->DataGrid->DataSource=$this->Data;
$this->DataGrid->dataBind();
$this->response->reload();
}
With this function above, if user clicks the delete button we delete the item at this index.

public function deleteSelecteds(){
foreach($this->DataGrid->Items as $i){
if($i->checkbox_column->select->Checked)
$this->deleteFromGrid($this->DataGrid->DataKeys[$i->ItemIndex]);
}
$this->response->reload();
}
With this function above, if user clicks our 'Delete Selecteds' button we walk through the indexes whom their checkbox's clicked and delete them one by one.

Other functions are implementation details, that's why they defined as 'protected';
protected function getData(){
if($this->_data===null)
$this->loadData();
return $this->_data;
}

protected function loadData(){
$db_connection = $this->Application->Modules['dbconnection']->Database;
$db_connection->Active = true;

$sql_query= "SELECT item_id, item_name, item_type FROM items";
$command = $db_connection->createCommand($sql_query);
$db_records = $command->query(); $i = 0;
foreach($db_records as $key){
$sql_query = "SELECT type_name FROM types where type_id='" . $key["item_type"] . "'";
$command = $db_connection->createCommand($sql_query);
$type_name = $command->queryScalar();
$array[$i] = array(
'ID'=>$i+1,
'type_name'=>$type_name,
'item_name'=>$key["item_name"],
);
$i++;
}

$this->_data = $array;
$this->saveData();
$db_connection->active = false;
}
With this function above we populate our datagrid from our mysql server.

protected function saveData(){
$this->setViewState('Data',$this->_data);
}

protected function updateGrid($id, $type_name, $item_name){
if($this->_data===null)
$this->loadData();
$updateRow=null;
foreach($this->_data as $index=>$row)
if($row['ID']===$id)
$updateRow=&$this->_data[$index];
if($updateRow!==null){
$updateRow['type_name']=$type_name;
$updateRow['item_name']=$item_name;
$this->saveData();
}
}
With this function above we update the values of the item that is at that index.

protected function deleteFromGrid($id){
if($this->_data===null)
$this->loadData();
$deleteIndex=-1;
foreach($this->_data as $index=>$row)
if($row['ID']===$id){
$deleteIndex=$index;
$deleteRow = &$this->_data[$index];
}
if($deleteIndex>=0){
unset($this->_data[$deleteIndex]);
if($deleteRow!==null){
$old_typename = $deleteRow['type_name'];
$old_itemname = $deleteRow['item_name'];
}

$db_connection = $this->Application->Modules['dbconnection']->Database;
$db_connection->Active = true;

$sql_query = "Select type_id from types where type_name='" . $old_typename . "'";
$command = $db_connection->createCommand($sql_query);
$type_id = $command->queryScalar();
$transaction = $db_connection->BeginTransaction();

try{
$sql_query = "delete from items where item_name='". $old_itemname ."' and item_type='". $type_id ."'"
;
$command = $db_connection->createCommand($sql_query);
$command->execute();
$transaction->commit();
}catch(Exception $ex){
$transaction->rollBack();
var_dump("UPPSSS Delete Failed");
return;
}
$this->saveData();
$db_connection->Active = false;
}
With this function above we delete the item that is at that index.

Here is the source code with the mysql dump of the database we used in the example. Also to run the application you need to copy Prado's directory to the same place with the datagrid directory.