I am trying to execute multiple sql statements in yii , this is to reduce round trips to the server . This is for a field called niche specialization . A maximum of three enteries are allowed for a user , however the field is optional . This is the code :
$sql = " Delete from cur_niche
where userid =:id ; " ;
$var2 = array_filter($object->nspecial) ;
if(!empty($var2))
{ $str = "" ;
$i = 0 ;
foreach($var2 as $n)
{ if($i==0)
$str.= "(:id,:n".$i.")" ;
else
$str.= ", (:id,:n".$i.")" ;
$i++ ;
}
$sql .= "Insert into cur_niche (userid,nspecial) Values ".$str." ; " ;
}
$command = Yii::app()->db->createCommand($sql) ;
$command->bindParam(":id",$id,PDO::PARAM_STR) ;
if(!empty($var2))
{ $i = 0 ;
foreach($var2 as $n)
{
$str = ":n".$i ;
$command->bindParam($str,$n,PDO::PARAM_STR) ;
$i++ ;
}
}
$result = $command->execute() && $result ;
I have tried dumping all my variables , the sql statement is formed correctly , the $object has the correct data from the form , In the binding the $str is working fine . However I am facing the following issue : 1) If there is only one entry in the niche spcialization array($object->nspecial) , the entry is working fine . 2) However if more than one field is filled , it fails to execute the insert , this is because the userid and nspecial fields form a composite primary key .
Doesnt the composite primary key depend on both values forming a unique combination (assuming that the user enters unique values in various fields) ? If I remove the primary key constraint it just duplicates the value of one field with two rows in the db .What am I doing wrong here ?
