CDbCriteria - bind params

I need to specify bind paramaters in my CDbCriteria object, the only problem I’m having is that conditions are only added if they exist in the GET, for example:

$criteria=new CDbCriteria;

		

if(!empty($_GET['gender']))

{

	$criteria->addCondition('gender=:gender');

}


if(!empty($_GET['location']))

{

	$criteria->addCondition('location=:location');

}


$criteria->params=array(':gender'=>$_GET['gender'], ':location'=>$_GET['location']);

If one of the GET variables is empty then the query fails because "number of bound variables does not match number of tokens".

How can I resolve this issue?

Secondly, when I try to use an addInCondition() alongside a normal condition I get an error about bind variables, even though I’m just specifying the value directly, eg:

$criteria->addInCondition(‘genre’, $_GET[‘genre’]);

if I try to bind the $_GET variable it then says "reset() expects parameter 1 to be array, string given"

$criteria=new CDbCriteria;

$params = array();


if(!empty($_GET['gender']))

{

        $criteria->addCondition('gender=:gender');

        $params[':gender'] = $_GET['gender'];

}


if(!empty($_GET['location']))

{

        $criteria->addCondition('location=:location');

        $params[':location'] = $_GET['location'];

}


if(count($params))

    $criteria->params=$params;

secondly

$criteria->addInCondition('genre', array($_GET['genre']));

Cheers

Cheers Antonio. $_GET[‘genre’] is already an array so I don’t think it needs to be entered as an array. I confirmed this because I get an “array to string conversion” error when I enclose it in an array.

Anyway it is still complaining that the number of bind variables do not match.

I checked the generated SQL and this is what I get:

WHERE ((gender=:gender) AND (genre IN (:ycp0, :ycp1))). Bound with :gender=‘2’

It seems that the ‘genre’ bind variables are not getting generated.

This only occurs when the $_GET[‘gender’] variable is present.

EDIT: I think I found out what the problem was. Basically the bind params have to be added to $criteria->params like this:

$criteria->params[':gender']=$_GET['gender'];

I think this is due to a limitation in Yii.

Great catch G,

I never had this problem as I use sub-queries on addInConditions. I wonder if IN statements are supported differently on the new CDbCommand as suggested by qiang. Didn’t have a deep look at its code yet.

Congrats anyway, I will note this as a hint for future challenges.

.

I had a similar problem with sessions. You can’t directly assign array values to a $session, for example:

$sessid="abc";


$session[$sessid]['id']=$model->id

This does not work, instead you have to do it like this:

$params['id']=$model->id;


$session[$sessid]=$params;