Strange things with CDbCommand

Archives Yii 1.0.x Discussions
1

Here is the code:

$a = Yii::app()->db->createCommand('SELECT productID FROM Product WHERE productID=1')->queryScalar();


        echo $a.'<br />';


        $altsCmd = Yii::app()->db->createCommand('SELECT secondaryID, userID, type FROM AlternativeProducts WHERE primaryID=:productID ORDER BY dtAdded DESC LIMIT 0,:maxAlts');


        echo 'productID='.$p->productID.'<br />';


        die('testBeforeBind');


        $altsCmd->bindParam(':productID', $p->productID, PDO::PARAM_INT);


        die('testAfterBind');

It shows

Quote

testBeforeBind

If I comment it out, it shows blank page (not "testAfterBind" as expected).

At the same time, this code (in another method) works fine:

$sizesCmd = Yii::app()->db->createCommand('SELECT s.size


        FROM Size s


        INNER JOIN Stock st ON s.sizeID=st.sizeID


        WHERE st.productID=:productID');


        $sizesCmd->bindParam(':productID', $p->productID, PDO::PARAM_INT);


        $sizes = $sizesCmd->queryColumn();

or

$coloursCmd = Yii::app()->db->createCommand('SELECT c.name colour, crp.secondaryID productID


        FROM Colour c


        INNER JOIN ColourRelatedProducts crp ON c.colourID=crp.colourID


        WHERE crp.primaryID=:productID');


        $coloursCmd->bindParam(':productID', $p->productID, PDO::PARAM_INT);


        $colours = $coloursCmd->queryAll();

Here is DB config:

'db'=>array(


            'connectionString' => 'mysql:host=localhost;dbname=*****',


            'username' => '******',


            'password' => '******',


            'emulatePrepare' => true,


            //'schemaCachingDuration' => YII_DEBUG === true ? 60 : 3600*24,


        ),

Any ideas?

2

I think you didn't turn on error display on your server.

I'm not sure if you can bind a parameter that is used in the LIMIT part (need to check PDO manual about this).

3

Qiang, it works fine on my local PC, the problem comes when I move this to server. There is slightly different MySQL version there:

5.0.51b-comunity - at home

5.0.22 - on the remote server

4

Did you try turning on error display and see what is the error message?

5

It's on…

display_errors=On

display_startup_errors=On

it displays other errors, but nothing on this one…

6

Instead of using bindParam, try bindValue.

Is your server's PHP version the same as your dev machine's?

7

I've just expereimented some more and found out, that you're right - when I don't have params in LIMIT section, it works fine. But that's strange…

On the local PC I have:

$altsCmd = Yii::app()->db->createCommand('SELECT secondaryID, userID, type FROM AlternativeProducts WHERE primaryID=:productID ORDER BY dtAdded DESC LIMIT 0,:maxAlts');


        $altsCmd->bindParam(':productID', $p->productID, PDO::PARAM_INT);


        $altsCmd->bindParam(':maxAlts', Yii::app()->params['product']['maxAlts'], PDO::PARAM_INT);


        $alts = $altsCmd->queryAll();

and it's fine. On the remote one only this works:



        $altsCmd = Yii::app()->db->createCommand('SELECT secondaryID, userID, type FROM AlternativeProducts WHERE primaryID=:productID ORDER BY dtAdded DESC LIMIT 0,'.Yii::app()->params['product']['maxAlts']);


        $altsCmd->bindParam(':productID', $p->productID, PDO::PARAM_INT);


        $alts = $altsCmd->queryAll();
8

And this is fine also:

$altsCmd = Yii::app()->db->createCommand('SELECT secondaryID, userID, type FROM AlternativeProducts WHERE primaryID=:productID ORDER BY dtAdded DESC LIMIT 0,:maxAlts');


        $altsCmd->bindValue(':productID', $p->productID, PDO::PARAM_INT);


        $altsCmd->bindValue(':maxAlts', Yii::app()->params['product']['maxAlts'], PDO::PARAM_INT);


        $alts = $altsCmd->queryAll();
9

And, yes, versions differ - PHP 5.2.6 is mine, 5.1.6 is remote…

Arhh… it took me more than a day to fix such simple things…

10

BindParam is special because it uses variable reference. The 'productID' is a property defined via __get(), and I guess that's why it may have trouble (this could be a PHP bug that was fixed in later versions).

die() is your friend. ;)

11

yes… But it’s good to know where to die() to learn something :)

Thanks!