Can't Update Db Values

Yii 1.1 General Discussion
1

Hi everyone,

I’ve got a table “settings” that I’m trying to update, but it just won’t work. My code currently:




        $rawJSONString = file_get_contents('php://input');

        $post = json_decode($rawJSONString);

        $setting = $post->setting;

        $value = $post->value;

 

        $user_id = Yii::app()->user->id;


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

        $sql = "UPDATE settings SET $setting=$value WHERE user_id=$user_id";

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

        $data = $command->execute();

I’ve even tried connecting via the php mysql commands, and it just won’t work! I can’t figure it out… I’ve tried running the SQL statement directly and it works (via phpmyadmin), but when I run this method above - nothing.

Thanks in advance!

2

Dear Friend

Would you please try the following.




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

        $sql = "UPDATE settings SET :setting=:value WHERE user_id=:user_id";

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

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

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

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

        $data = $command->execute();
3

I got this error:




CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''items_purchased'='1' WHERE user_id='103'' at line 1. The SQL statement executed was: UPDATE settings SET :setting=:value WHERE user_id=:user_id;
4

can you post your table schema

5

Dear Friend

Sorry for that not being working.

Please try this.




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

        $sql = "UPDATE settings SET ".$setting." =:value WHERE user_id=:user_id";

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

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

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

        $data = $command->execute();
6

Thanks very much for the help, guys!

seenivasan - That code doesn’t give any error, but again, settings table is not getting updated…

alirz -

CREATE TABLE IF NOT EXISTS settings (

user_id int(11) NOT NULL,

name varchar(256) NOT NULL,

city varchar(256) NOT NULL,

items_purchased int(11) DEFAULT NULL,

PRIMARY KEY (user_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

is this what you meant?

7

Dear Friend

I just simulated your scenario by creating the settings table by the sql code you have given.

Only autoincrement part is missing. Anyway that is not the issue.

I inserted couple of records without any values for items_purchased..

I created three variables $item_purchased,$value and $User_id.

The code is working well and nicely updating the items_purchased field.

I also tried with PDO::PARAM_INT and also tried without PDO::PARAM_STR and PDO::PARAM_INT.

Every thing is working.

I do not know where exactly we are erring.

Keep working on it.