How To Fetch User Table Id Value Into Another Table

Yii 1.1 General Discussion
1

Hi, I’ve three tables in my database namely user, preferences and questions. My preferences table is like this…

CREATE TABLE IF NOT EXISTS preferences (

id int(11) NOT NULL AUTO_INCREMENT,

questionid int(11) DEFAULT NULL,

answers varchar(100) DEFAULT NULL,

user_id int(11) DEFAULT NULL,

PRIMARY KEY (id),

KEY questionid (questionid),

KEY user_id (user_id)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

My questions table is like this

CREATE TABLE IF NOT EXISTS questions (

id int(11) NOT NULL AUTO_INCREMENT,

question varchar(400) DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

                  I given foreign keys to preferences table to questionid from questions table id and user_id from user table id. Now i'm inserting answers values into preferences. Can i know how to fetch questionid and user_id into preferences table. My questions are in _form.php file.
2

I take it the question’s answers in the form are supposed to be dynamic?

Something like:




<?php echo $form->dropDownList($model, 'question_id', CHtml::listData(Asnwers::model()->findAll(<maybe insert answers type here like: "question_type = 1">), 'id', 'question'); ?>

As for storing the question id, you are typing the questions manually.

Why not insert them in the DB and fetch them so you can recover its ID in the $_POST?

If you still want to do them manually you can make a regular field with the HTML name attribute like


name="<question_id_here>"

but this is not the way to do it.

As for the user id, you can get the current user id in Yii with


Yii::app()->user->id;

You should take a look at the demo inside the Yii framework folder named Blog, it really helps out to get you started.

Regards,

SilverPT

3

Thanks for reply. In my table having questions. Can i know how fetch questionid from database to _form.php file. And when i try for user id as you said the value is displaying on my page but its not stored in my database.

4

Can i know how to insert displaying value into database, Actually my user id is displaying my page, now how to insert this display value into database.

5

I got solution thank you… I added beforeSave() function in model.

6

Hi, i’ve one more dought. Every time the questionid data is stored in new row. I mean now when i select option in dropdownlist it saving to my database like this

id:1

questionid:1

answers:haii

userid:1

Again if i select questionid is 1 with different answer it saving new row in database, but i need to store in the previous answer place my new answer in my table.




<?php echo $form->dropDownList($model, 'answers', array( 'select'=>'select',

                                                                'Not Really'=>'Not Really',

                                                                'A little bit'=>'A little bit',

                                                                'Light but regular'=>'Light but regular',

                                                                'Heavy but not regular'=>'Heavy but not regular',

                                                                'Regular, hardcore exercise'=>'Regular, hardcore exercise'

                ),

                    array('onchange'=>'this.form.submit()')


                    ); ?>

            <?php echo $form->dropDownList($model,'questionid', CHtml::listData(Questions::model()->findAll("id=2"), 'id', 'answers'), array('style'=>'visibility:hidden')) ?>

pls help me .

7

Hum I’m not sure I understand the question, do you mean when the user updates the answer to a particular question, you want to store the previous answer and not just update the answers field?

In that case, you could edit the actionUpdate to instead of just updating the record creating a new one.

You could create a new attribute in the model named


active

for example which would be a boolean.

When the user updates a determined record, you could set this boolean to


$model->active = FALSE

, save the record and then create a new record with the very same information as the previous one, but you would set the


$model->active = TRUE

.

This is a simple way to maintain the user’s answers history. Then to fetch the user’s answers, you could do something like:


$answers = Preferences::model->findAll("user_id=Yii::app()->user->id AND question_id=:question_id AND active = TRUE", array("question_id" => <question id here>));

Regards,

SilverPT

8

pls help me how to update new value instead of previous value in my "preferences" table.

9

You mean when the user tries to update the answer the first time its OK but when he updates again the previous answer is what he chose the first time instead of the second time?

If that is the case then it is because in your actionUpdate() you are not loading the model right.

Are you doing this like I sugested?


$answers = Preferences::model->findAll("user_id=Yii::app()->user->id AND question_id=:question_id AND active = TRUE", array("question_id" => <question id here>));

What do you mean "i need to save instead of "hiii" to "bye" when i select same questionid" ?

10

yes, i need to store second answer in the place of first answer. and where i need to write this code




$answers = Preferences::model->findAll("user_id=Yii::app()->user->id AND question_id=:question_id AND active = TRUE", array("question_id" => <question id here>));
11

So you dont need to save history then?

12

yes

13

Then why are you creating a new record when you update?

You said it was needed when I sugested it.

I still dont udestand your problem, the actionUpdate should do.

Grab the answer from the $_POST request when you update the answer and then in the actionUpdate do something like:




$model->answer = $_POST[name_of_variable]

if($model->update()){

...
14

Its not working? The problem is when select ddifferent answer for same question the new answer is need to store in the place of old answer

15

You should take a look at the blog application that comes inside the yii framework folder in demos\blog, it really helps you out to understand those concepts.

Regards,

SilverPT

16

Can i know how to update values based on foreign key.

17

Hi, here user_id is foreignkey from usertable and questionid is foreignkey from questions table. Now i’m inserting 4 questions values in preferences table as like this…




id:1             2         3 .........

qusetionid:1     2         1 .......

answers:hello    Good      verygood  .......

user_id:1        1         1  ......

Above the values are storing in my db. But, when i select answers to questionid:1 first it saved like "hello" and again if I select different answer like "verygoog" its saving new row, but i need to store this new answer in the place of pld answer.no need to store in new row.

18

Well, then you just need to find the row with that question_id and user_id. So mething like


$row = Preferences::model()->find("question_id =:question_id, user_id =:user_id",array("question_id" => $_POST[question_id], "user_id" => Yii::app()->user->id);

$row->answer = $_POST["answer"];

$row->save();

This is basic logic, you should read tutorials and Yii books, there are plenty of free tutorials out there.

Regards

19

Hi, when i try like this i’m getting this error pls help me…

.

Pls help me, i’m struck here only.

20

HI, Now am inserting my all question answers in db. But when i select different answer to each question in dropdown need to update this new answers in the place of previous answers. But here is nw answer is adding instead of previous one, but its delete previous and its inserting. I need only updation. Here is my controller code…




public function actionIndex()

	{

                

		$model = new Preferences();

		$data = Questions::model()->findAll();

                //print_r($_POST["questionid"]);

                //$id = Preferences::model()->id;

               // $questionid = Preferences::model()->questionid;

                if(isset($_POST['Preferences']))

		{

                    $questionid = Preferences::model()->questionid;

                    $questionid = $_POST['Preferences'];

                   foreach ($questionid as $value) {

                                    $find = Preferences::model()->findAll("questionid='".$value."'");

                                    if(count($find)>0) {

                                          Preferences::model()->udateAll("questionid='".$value."'");

                                          Preferences::model()->deleteAll("questionid='".$value."'");

                                        $model->attributes=$_POST['Preferences'];

                                        $model->questionid=$value;

                                    }else{

                                        $model->attributes=$_POST['Preferences'];

                                        $model->questionid=$value;

                                    }

                   }

                        if($model->save())

                                $this->redirect(array('preferences/index'));

                  

		}


                $this->render('index',array(

			'data1'=>$data, 'model'=>$model

		));


	}

When i select second answer for first question i need update this one, when i run this code i’m getting this error