Solved - Bindparam Not Working Properly

Yii 1.1 General Discussion
1

I’ve spent countless hours trying to figure out why my sql statement won’t work when I use bindParam. I’ve searched out here and found some threads, but none seem to resolve my problem.

I’m not getting any errors, but it just seems not to be binding to the value therefore nothing is being returned.

Here is my code:





$connection=Yii::app()->db;

$sql="UPDATE narratives SET description = '$this->description', content = '$this->content' WHERE id = ':id'"; 			

$command=$connection->createCommand($sql);

$command->bindParam(':id',$this->id,PDO::PARAM_STR);

$result = $command->execute(explode(" ",$sql)); 

return $result;

Here is a dump from the logs:


SELECT * FROM narratives WHERE id = 'remotest'

2013/03/04 12:47:47 [trace] [system.db.CDbCommand] Executing SQL: UPDATE narratives SET description = 

'For testing', content = '<p>This is test data</p>' WHERE id = ':id'. Bound with :id='remotest', 

0='UPDATE', 1='narratives', 2='SET', 3='description', 4='=', 5='\'For', 6='testing\',', 

7='content', 8='=', 9='\'<p>This', 10='is', 11='test', 12='data</p>\'', 13='WHERE', 14='id', 15='=', 16='\':id\''

Here is a vardump of $command which seems to be substituting :id with the actual value "remotest":




[font=Times][size=2]CDbCommand#1 ( [params] => array() [CDbCommand:_connection] => CDbConnection#2 ( [connectionString] => 'mysql:host=localhost;dbname=ng' [username] => 'ng' [password] => 'mypassword' [schemaCachingDuration] => 0 [schemaCachingExclude] => array() [schemaCacheID] => 'cache' [queryCachingDuration] => 0 [queryCachingDependency] => null [queryCachingCount] => 0 [queryCacheID] => 'cache' [autoConnect] => true [charset] => null [emulatePrepare] => null [enableParamLogging] => 'true' [enableProfiling] => false [tablePrefix] => null [initSQLs] => null [driverMap] => array ( 'pgsql' => 'CPgsqlSchema' 'mysqli' => 'CMysqlSchema' 'mysql' => 'CMysqlSchema' 'sqlite' => 'CSqliteSchema' 'sqlite2' => 'CSqliteSchema' 'mssql' => 'CMssqlSchema' 'dblib' => 'CMssqlSchema' 'sqlsrv' => 'CMssqlSchema' 'oci' => 'COciSchema' ) [pdoClass] => 'PDO' [CDbConnection:_attributes] => array() [CDbConnection:_active] => true [CDbConnection:_pdo] => PDO#3 ( ) [CDbConnection:_transaction] => null [CDbConnection:_schema] => null [behaviors] => array() [CApplicationComponent:_initialized] => true [CComponent:_e] => null [CComponent:_m] => null ) [CDbCommand:_text] => 'UPDATE narratives SET description = \'For testing\', content = \'[/size][/font][font=Times][size=2]This is test data[/size][/font]


[font=Times][size=2]\' WHERE id = \'remotest\'' [CDbCommand:_statement] => PDOStatement#4 ( [queryString] => 'UPDATE narratives SET description = \'For testing\', content = \'[/size][/font][font=Times][size=2]This is test data[/size][/font]


[font=Times][size=2]\' WHERE id = \'remotest\'' ) [CDbCommand:_paramLog] => array() [CDbCommand:_query] => null [CDbCommand:_fetchMode] => array ( '0' => 2 ) [CComponent:_e] => null [CComponent:_m] => null )Did it work?1[/size][/font]

Lastly, if I use the same code as above without any bindParam, it works fine. here is the sample code of that:





$connection=Yii::app()->db;

$sql="UPDATE narratives SET description = '$this->description', content = '$this->content' WHERE id = '$this->id'"; 			

$command=$connection->createCommand($sql);

$result = $command->execute(explode(" ",$sql)); 

return $result;

I would really appreciate some direction.

thanks,

2

Please try removing the quotes for the param. You have this




WHERE id = ':id'"

Try this:




WHERE id = :id"
3

Thanks for your reply, but have already tried that and if gives the following error:




CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: 

Invalid parameter number: number of bound variables does not match 

number of tokens. The SQL statement executed was: UPDATE narratives 

SET description = 'For testing', content = '<p>This is test data</p>' 

WHERE id = :id. Bound with :id='remotest', 0='UPDATE', 1='narratives', 2='SET',

 3='description', 4='=', 5='\'For', 6='testing\',', 7='content', 8='=', 9='\'<p>This', 

10='is', 11='test', 12='data</p>\'', 13='WHERE', 14='id', 15='=', 16=':id'
4

Dear Friend

Would you please check the following?




$connection=Yii::app()->db;

$sql='UPDATE narratives SET description = :description, content = :content WHERE id = :id'; //No sub quotes                 

$command=$connection->createCommand($sql);

$command->bindParam(':description',$this->description,PDO::PARAM_STR);

$command->bindParam(':content',$this->content,PDO::PARAM_STR);

$command->bindParam(':id',$this->id,PDO::PARAM_STR);

$command->execute(); //Here there is no need to pass parameters, as we have already called bindParam method.
5

Yes, I think that’s because your param is an integer, and you are using PDO::PARAM_STR which is for strings.

6

Yes, that worked! Now that I read your code, it makes perfect sense.

Thanks very much!

7

Just to clarify, this reply worked.

8

No, the are strings. I did get Seenivasan’s solution to work. Thanks for your help.