Yii- Cdbcommand-Bindparams

Yii 1.1 General Discussion
1

I have a big SQL query which gives me an array at the end.

The parts of the query are based on few conditions

e.g. if(isset($arrParams[‘height_min’]) && $arrParams[‘height_min’]!="" && isset($arrParams[‘height_max’]) && $arrParams[‘height_max’]!="")

{

$sql .= " height BETWEEN :height_min AND :height_max AND ";

}

Basically, few parts are appended to the sql depending on the current input.

How to bindParams for these condition based parts?

I have tried to put the bindparams in the same condition but it throws error - Invalid parameter number

if(isset($arrParams[‘height_min’]) && $arrParams[‘height_min’]!="" && isset($arrParams[‘height_max’]) && $arrParams[‘height_max’]!="")

{

$command->bindParam(":height_min", $arrParams[‘height_min’], PDO::PARAM_STR);

$command->bindParam(":height_max", $arrParams[‘height_max’], PDO::PARAM_STR);

}

The error displayed is-

CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens.

Please suggest a way to write such queries.

2

Try to debug. This error tells you that the number of placehoders and bind parameters aren’t equal. Trye to echo the final $sql and to count bindParam.

3

It is showing the same count. Is the problem because I have written bindParams in the condition?

4

there is an extra AND at the end of the string


$sql .= " height BETWEEN :height_min AND :height_max AND ";

paste the rest of the code

5

Thank you for the reply.

The error was because of the quotes around the placeholder.

Now I am facing another problem. There are some string values and some numeric values.

The SQL query is comparing numeric values also as string. It adds quotes around the value and returns fewer results because of that.

$command->bindParam(":distance", $distance);

$command->bindParam(":distance", $distance,PDO::PARAM_INT);

Both the above things are not working.

Can anyone suggest the solution for the same?

6

did you try casting it to int?


$command->bindParam(":distance", (int)$distance, PDO::PARAM_INT);
7

I tried the above code.

I received the following error-

Fatal error: Cannot pass parameter 2 by reference

8

how silly of me PDO::PARAM_INT basically does the same as (int)$distance paste your entire query maybe i can help you construct with query builder