data binding

Yii 1.1 General Discussion
1

Hi

Can someone verify I am binding my params correctly? I’ve read both http://www.yiiframework.com/doc/api/CDbConnection and http://www.yiiframework.com/doc/guide/database.dao




$q = "SELECT areaName as `name`,para1 as descrp, para2,image as img

                FROM areas

                WHERE `areaName` = \"%:areaSearch%\";";


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

        $command->bindParam(":areaSearch",$areaSearch,PDO::PARAM_STR);

        $dataReader = $command-> query();

        $results=$dataReader->readAll();

It does not return a result array as expected.

2

Try this:




$q = 'SELECT areaName as name, para1 as descrp, para2, image as img from areas where areaName = ":areaSearch"';

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

$command->bindParam(':areaSearch', $areaSearch);

$results = $command->queryAll();
3

Try bindValue() instead of bindParam().

4

Binding a parameter will quote the placeholder based on the underlying driver so one should not place any qoutes around the placeholder.

eg.




$q = 'SELECT areaName as name, para1 as descrp, para2, image as img from areas where areaName = :areaSearch';

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

$command->bindParam(':areaSearch', $areaSearch);

$results = $command->queryAll();
5

But he wants to add % before and after search term.

Try




$q = 'SELECT areaName as name, para1 as descrp, para2, image as img from areas where areaName = :areaSearch';

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

$command->bindValue(':areaSearch', "%{$areaSearch}%");

$results = $command->queryAll();
6

Y!! is right, I wish to do a wildcard search. I’m still having trouble running the line above. I just get back an empty array. But when I run it without it works ie


  

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

        $command->bindValue(":areaSearch",$areaSearch);

        $results = $command->queryAll();

also what does the angled brackets in "%{$areaSearch}%" represent?

7

Hi thiswayup, Angled brackets tell the PHP parser to separate the contents (variable) from the rest of the string.

Try setting it up this way :




$q = 'SELECT areaName as name, para1 as descrp, para2, image as img from areas where areaName = :areaSearch';

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

$areaSearch = '%'.$areaSearch.'%';

$command->bindParam(':areaSearch', $areaSearch);

$results = $command->queryAll();

I have used bindParam in this instance so one must make sure to set the variable before passing it the method (as in the example above) :)

Actually, taking a look at your sql statement you have specified the incorrect operand (=) it should be (LIKE)

eg:




.....

$q = 'SELECT areaName as name, para1 as descrp, para2, image as img from areas where areaName LIKE :areaSearch';

......
8

nice catch GDzyne…

so even the previous examples maybe would work but "=" should be changed to "LIKE"…

because "=" search exactly that character…

but

"LIKE" uses _ and % as two search helper symbol…