Skip to content

Yii bindParam error with CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1048 Column

March 5, 2012

I have a project to import data to mysql. When I use $command->bindParam every field did not seem to be set with the value passed appropriately inside the foreach loop . It takes me sometime to figure out the issue. After checking the yii function references  I got the point . CDbCommand::bindParam signature below :

public CDbCommand bindParam(mixed $name, mixed &$value, integer $dataType=NULL, integer $length=NULL, mixed $driverOptions=NULL)

The second param is the value of the variable to be bound to. It must be passed by reference.

for example:

$sql = ” INSERT INTO city (name, code) VALUES(:name, :code) “;

$command = Yii::app()->db->createCommand(|$sql);

I came up with 2 cases :

case 1:

$command->bindParam(“:name”, “Phnompenh”); // is wrong because constant is passed.

$command->bindParam(“:code”, “855”); // is wrong because constant is passed.

case 2:

$cols = array(“name” => “phnompenh”, “code” => “855”);

foreach($cols as $col =>  $value) {

$command->bindParam(“:{$col}”, $value); // is wrong coz $value is not permitted to  be changed in PHP

}

The right way to do it is:

case 1

$name = “phnom penh”;

$code = ” 855″;

…..

$command->bindParam(“:name”, $name);

$command->bindParam(“:code”, $code);

case 2

foreach($cols as $col =>  &$value){ // $value can be changed by the body of foreach loop.

$command->bindParam(“:{$col}”, $value);

}

public CDbCommand bindParam(mixed $name, mixed &$value, integer $dataType=NULL, integer $length=NULL, mixed $driverOptions=NULL)

Advertisements

From → yii

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: