Web Application Development Tip- Save Data From CGridView

In this web application development example I will show you how to save data from CGridView into database. This will help you during web application development to save bulk data into the database. In this exampe I will use the basic yii application created in this web application development example Yii ajax form submission. Here I will show you how to save the tasks details under a project. I will use the project table created for the example Yii cgridview filter date range.

Web Application Development Tip- Save Data From CGridView

First we will create the task table under the project. Task table has the fields id(pk) int,project_id(fk),task_name character varying,start_date date,end_date date and remarks character varying. You can run the below query to create the task table.

CREATE TABLE IF NOT EXISTS `task` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`project_id` int(11) NOT NULL,
`task_name` varchar(100) NOT NULL,
`start_date` date NOT NULL,
`end_date` date NOT NULL,
`remarks` varchar(250) NOT NULL,
PRIMARY KEY (`id`),
KEY `project_id` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

ALTER TABLE `task`
ADD CONSTRAINT `task_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`) ON UPDATE CASCADE;

Now we will insert data into the task table. You can run the below query to insert the values into the table.

INSERT INTO `report`.`task` (`id`, `project_id`, `task_name`, `start_date`, `end_date`, `remarks`) VALUES (NULL, '1', 'Task1', '2015-01-01', '2015-01-14', 'Completed'), (NULL, '1', 'Task2', '2015-02-18', '2015-02-27', 'Completed'), (NULL, '2', 'Task3', '2015-03-15', '2015-03-25', 'Completed'), (NULL, '1', 'Task4', '2015-04-15', '2015-04-25', 'Completed'), (NULL, '1', 'Task5', '2015-05-05', '2015-05-25', 'Completed');

Next we will create the view file at ‘myapp\protected\views\site\task.php’. Paste the below code into the file.

<?php
Yii::app()->clientScript->registerScript("showdatepicker","
function showdatepicker()
{
$('.datepick').each(function(){
$(this).datepicker({
changeMonth: true,
changeYear: true,
dateFormat:'dd/mm/yy',
showAnim:'slideDown'
});
});
}",CClientScript::POS_END);
?>

<?php $form=$this->beginWidget('CActiveForm', array(
'action' => Yii::app()->createUrl('site'),
'enableAjaxValidation'=>true,
)); ?>


<?php echo $form->labelEx($model,'project',array('class'=>'control-label')); ?>
<?php
$DisplayColumns = "id,name";
$query = 'select id,name from project where name LIKE :searchTerm ORDER BY name';

$cntrlr = Yii::app()->createAbsoluteUrl('combogrid/generateoptions', array('sql'=>$query,'dColumns' => $DisplayColumns));

$this->widget('ext.combogrid.CombogridWidget',array(
'model'=>$model,
'attribute'=>'project',
'htmlOptions'=>array('class'=>'form-control','title'=>'Project'),
'options'=>array(
'minLength'=>'1',
'showOn'=>true,
'url'=> $cntrlr,
'debug'=>true,
'width'=>500,
'alternate'=> true,
'colModel'=>array(   array('columnName'=>'id','width'=>'10','label'=>'Id','align'=>'left','hidden'=>true),
array('columnName'=>'name','width'=>'50','label'=>'Project','align'=>'left')
),
'select'=>'js: function( event, ui ) {
$("#Task_project_id").val( ui.item.project_id );
$("#Task_project").val( ui.item._name );
$("#task-grid").yiiGridView("update", {data: {"Task[project_id]": ui.item.id}});
$("#project_name").focus();
return false;
} '
),
));
echo $form->hiddenField($model,'project_id');
echo $form->error($model,'project');
?>


<?php $this->endWidget(); ?>

<?php $form2=$this->beginWidget('CActiveForm', array(
'action' => Yii::app()->createUrl('site/tasksubmit'),
'enableAjaxValidation'=>true,
)); ?>
<?php
;
$this->widget('zii.widgets.grid.CGridView', array(
'id'=>'task-grid',
'itemsCssClass'=>'table table-bordered table-condensed table-hover table-striped dataTable',
'dataProvider'=>$model->search(),
'afterAjaxUpdate'=>'showdatepicker',
'enablePagination' => true,
'enableHistory'=>true,
'pagerCssClass'=>'dataTables_paginate paging_bootstrap table-pagination',
'pager' => array('header'=>'','htmlOptions'=>array('class'=>'pagination')),
'columns' => array(
array('name'=>'id','value'=>'CHtml::textField("Task[id][]",$data["id"],array("class"=>"datepick"))','type'=>'raw','header'=>'Remarks','headerHtmlOptions' => array('style' => 'display:none'),'htmlOptions' => array('style' => 'display:none'),'filterHtmlOptions' => array('style' => 'display:none')),
array('name'=>'task_name','value'=>'$data["task_name"]','header'=>'Task'),
array('name'=>'start_date','value'=>'CHtml::textField("Task[start_date_".$data["id"]."]",Yii::app()->dateFormatter->format("dd/MM/yyyy",$data["start_date"]),array("class"=>"datepick"))','type'=>'raw','header'=>'Started On','htmlOptions'=>array('style'=>'width:50px;')),
array('name'=>'end_date','value'=>'CHtml::textField("Task[end_date_".$data["id"]."]",Yii::app()->dateFormatter->format("dd/MM/yyyy",$data["end_date"]),array("class"=>"datepick"))','type'=>'raw','header'=>'Ended On','htmlOptions'=>array('style'=>'width:50px;')),
array('name'=>'remarks','value'=>'CHtml::textField("Task[remarks_".$data["id"]."]",$data["remarks"])','type'=>'raw','header'=>'Remarks','htmlOptions'=>array('style'=>'width:50px;')),
),
'htmlOptions'=>array('class'=>'grid-view'),
))
?>


<script>
function reload(data) {
$.fn.yiiGridView.update('task-grid');
alert('Saved successfully.');
}
</script>

<div style="float:right">
<?php

echo CHtml::ajaxSubmitButton('Save',CHtml::normalizeUrl($this->createUrl('site/tasksubmit')), array('success'=>'reload','error'=>'js:function(xhr, status, error){alert(xhr.responseText);}'),array('id'=>'submitbtn'));
?>
<?php $this->endWidget(); ?>

In the CGridView I have created three columns task, start date, end date and remarks. I have used jQuery datepicker to select date in the date fields. I have applied datepicker using the class ‘datepick’. I have used the hidden id field to get the values of each row. Yii helps you a lot to use jQuery in web application development. Next we will create the model file at ‘myapp\protected\models\Task.php’. Copy the below code into the file.

<?php


class Task extends CActiveRecord
{
public $project;

/**
* Declares the validation rules.
*/
public function rules()
{
return array(
array('id,project_id,task_name,start_date,end_date,remarks', 'safe'),
);
}



public function tableName()
{
return 'task';
}



public function search()
{
if(empty($this->project_id))
$this->project_id=null;

$command =Yii::app()->db->createCommand("SELECT COUNT(*) FROM task where project_id=:project_id");
$command->bindParam(":project_id",  $this->project_id,PDO::PARAM_STR);
$count=$command->queryScalar();

$sql="SELECT id,project_id,task_name,start_date,end_date,remarks FROM task where  project_id=:project_id";

$dataProvider=new CSqlDataProvider($sql, array(
'params' => array(':project_id'=>$this->project_id),
'totalItemCount'=>$count,
'db'=>Yii::app()->db,
'sort'=>array(
'attributes'=>array(
'task_name','start_date','end_date','remarks'
),
),
'pagination'=>array(
'pageSize'=>10,
),
));
return $dataProvider;
}



public function saveData()
{
$command =Yii::app()->db->createCommand("update task set start_date=:start_date,end_date=:end_date,remarks=:remarks where id=:id");
$command->bindParam(":start_date",  date("Y-m-d",strtotime(str_replace('/','-',$this->start_date))),PDO::PARAM_STR);
$command->bindParam(":end_date",  date("Y-m-d",strtotime(str_replace('/','-',$this->end_date))),PDO::PARAM_STR);
$command->bindParam(":remarks",  $this->remarks,PDO::PARAM_STR);
$command->bindParam(":id",  $this->id,PDO::PARAM_STR);
$command->execute();
}


}

Next create the action in the controller file at ‘myapp\protected\controllers\SiteController.php’ and copy the below code into the file.

public function actionTask()
{
$model=new Task;
if(isset($_GET['Task']))
{
$model->unsetAttributes();
$model->attributes = $_GET['Task'];
}
$this->render('task',array('model'=>$model));
}


public function actionTaskSubmit()
{

$model    = new Task;
$ids = $_POST['Task']['id'];
$data    = $_POST['Task'];

if(count($ids)>0)
{

foreach ($ids as $id)
{
$model->id=$id;
$model->start_date = $data["start_date_".$id];
$model->end_date   = $data["end_date_".$id];
$model->remarks   = $data["remarks_".$id];
$valid=$model->validate();
if($valid){
$result=$model->saveData();

}
else
{
$error = CActiveForm::validate($model);
if($error!='[]')
echo $error;
Yii::app()->end();
}


}

}

}

Now enter the url ‘http://localhost/myapp/index.php?&r=site/task’ into the browsers address bar and you can see the below screen were you will be able to save the data from CGridView into the database. Hope this tip will help you during web application development for bulk saving of data. Yii has extensive support for jQuery and ajax which really simplifies our job during web application development.

web application development