[SOLVED] NULL instead of value on Yii DAO SQL INSERT: bindValue

I have the action:

UPDATED

Action in Item Controller. Action called from ajaxLink in item/view/#

public function actionAddInterest() {

            $itm= Item::model()->find("`ItemId` = :itm", array(':itm' => $_GET['ItemId']));

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

            $sql1 = "INSERT INTO interest (UserId, ItemId)

                        VALUES(:usr, :itm)";

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


            $command->bindValue(":usr", Yii::app()->user->id);

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

            $command->execute();

        } 

Even with the added GET request it still returns NULL. Any Ideas?

ORIGINAL

public function actionAddInterest() {

    $model = new Item;

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

    $sql1 = "INSERT INTO interest (UserId, ItemId)

                VALUES(:usr, :itm)";

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


    $command->bindValue(":usr", Yii::app()->user->id);

    $command->bindValue(":itm", $model->ItemId);

    // $command->bindValue(":itm", $model->ItemId, PDO::PARAM_INT);  //also tried

    $command->execute();

} 

No value is being captured from $model->ItemId though and it returns a NULL for the input. What am I missing here?

You’re using an empty Item instance - you haven’t populated it with any values. If you want to use an existing item you should be using

$model = Item::model()->find($criteria);

I assume you are passing the id of the item you want into that controller/action?

So you could do:

$itemId = Yii::app()->request->getParam('itemId'); //same as $_GET/$_POST ie $_REQUEST

$model = Item::model()->find('itemId = :itemId', array(':itemId' => $itemId));

Hi Luke,

Thanks for the response. I tried adding in what you provided and it still is not changing things.

I also tried

$itm= new Item(); 

$model = $itm->find('ItemId=:itm',array(':itm'=>$_GET['ItemId'])); 


$command->bindValue(":item", $model->ItemId);

but still the value comes out NULL

This action sits in the Item controller and is executed from an ajax button on an item/view page. So the ItemId that I need is also the id for that view page.

Try and cast it to an int ?

Mind sharing an example?

Your first example look right.

Try this:

$dbCommand->bindParam(':itm', $model->ItemId, PDO::PARAM_INT);

Or by casting to int:

$dbCommand->bindParam(':itm', (int)$model->ItemId, PDO::PARAM_INT);

This doesn’t work either unfortunately.

I have updated the original question by adding a GET request to try and get the current ItemId. Still returning NULL though. Any other ideas?

Dump the value to the browser / log.

Sorry should have detailed how I know it is NULL. I have been logging the entire time in t/s this. If I just let the logs read the attempted input it comes back with :itm=NULL

I also tried dumping the variable and through firebug the response for this…returns NULL. So something is not working with the $_GET.

$itm= Item::model()->find("`ItemId` = :itm", array(':itm' => $_GET['ItemId']));

var_dump($itm);

die();

Right.

public function actionAddInterest($itemId) {

How are you calling it, btw?

You need to pass the parameter from your ajax.

Here is the code from my item/view. Not sure how to pass the parameter from the ajax as you mention.

<?php 

  echo CHtml::ajaxLink(

    'Add Interest',          

    array('/item/addInterest'), 

    array(

      'update'=>'#int_res'

      )

  );

?>

I think you need to add a data field to it:

array(

  'update' => '#int_res',

  'data' => array('itemId' => $model->id),

),

jacmoe, you got it! I really appreciate the help. Below is the code that is now working. Any thoughts on being the right thing when it comes to preventing SQL injection? My concern would be this

$command->bindValue(":itm", $_GET['ItemId']);

To be complete and for others to see this is what I now have.

Controller

public function actionAddInterest() {

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

  $sql1 = "INSERT INTO interest (UserId, ItemId)

    VALUES(:usr, :itm)";

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

  $command->bindValue(":usr", Yii::app()->user->id);

  $command->bindValue(":itm", $_GET['ItemId']);

  $command->execute();

}

View

<?php 

  echo CHtml::ajaxLink(

    'Add Interest',          

    array('/item/addInterest'), 

    array(

      'data' => array('ItemId' => $model->ItemId),

      'update'=>'#int_res'

      )

  );

?>

Great that it works now!

If you used PDO::PARAM_INT or a simple cast to int then you can be sure that it’s integers regardless of input.

But it looks pretty safe already.

Cool thanks for the additional feedback and again thanks for the help!