How To Update Morethan Hundreds Rows From Databasetable In Yii

Yii 1.1 General Discussion
1

Thanks in advance.I have one form for updation.When submit the form i want to update 100 rows of database table with one form submission.Is it possible with one query?If i use each query for each row in databsetable it tooks lot of time to update the all data.

2

There is method updateAll(), that could help U.

U can look to it :

http://www.yiiframework.com/doc/api/1.1/CActiveRecord#updateAll-detail

1 Like
3

$sql=“update sd_stu_att2 set attendance=’” . $srlz . “’ where student_id=’” . $student_id . “’”;

I want to execute this into 100 times when 100 different students are there.Hence 100 query needs to execute.Is there one query executes for 100 different student_id in the table?

4

You can use CASE and you have to generate a update sql string in a loop like this:





/*

UPDATE sd_stu_att2 SET attendance= CASE

WHEN student_id= 1 THEN 'attendance1';

WHEN id = 2 THEN 'attendance2';

....

...

ELSE title

END;

*/


$sql = 'UPDATE sd_stu_att2 SET attendance= CASE ';


foreach($data as $studentId=>$value)

 $sql .= "WHEN student_id= $studentId THEN '$value' "


$sql .= 'ELSE title END';

But you should take care of sql-injection (mysql unescape…) or better use params.