select query issue

Archives Bug Discussions
1

Hi all,

I have a table named as a student

where i am having the following values.

right now i am able to see the school id in my list student section but instead of id, i want to show the school name as the school value.

For that purpose,i have created the following function into my model section -


 public function getSchoolName()

        {

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

                $command = "SELECT sch.name FROM `tbl_student` AS st LEFT JOIN tbl_school AS sch ON st.school_id = sch.id WHERE st.id='".$id."' ";

                $dataReader=$connection->createCommand($command)->query();

                $rows=$dataReader->readAll();

                return $rows;


        }

And in the _view.php, i am trying to get this value like the following


  <b><?php echo CHtml::encode($data->getAttributeLabel('school_id')); ?>:</b>

	<?php echo Student::getSchoolName(); ?>

	<br />

but i am getting the error as the function is undefined. Can anyone please tell me how can i get the school name on the student CRUD page?

Thanks in advance.

2

Why don’t you call name directly from $data? Which way do you create $data?

Please try this way:


<?php echo $data->school_name?>
3

Thanks for your reply yugene

Because the school name is coming form the different table and i am showing it on the student CRUD page

Got the error-

Please help me.

4

You’ve missed some basic OO stuff here and missed making use of Yii!'s relationship support, which would make your life easier.

The reason that you’re getting an undefined method error is that your method “getSchoolName()” is declared as an instance method, i.e. it only exists for instantiated objects. Using Student::getSchoolName() is a reference to a class level method, which you have declared as you use the static keyword as part of the function declaration to make it a class method. In your case it doesn’t make sense as a class method though so $data->getSchoolName() would be the appropriate call.

Making an SQL call per student isn’t ideal and it would be best to join the two tables together as you fetch the student data. Also, take a look at the “relations()” method as this will enable you to get the related school object as an attribute of the Active Record, I’m assuming you’re using Active Records with Yii!. There’s more on this subject here: http://www.yiiframework.com/doc/guide/1.1/en/database.arr

5

Thanks for your reply Say_Ten,

Yes i a am aware of the relation function and here is my function


public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		  return array(

                        'school_id' => array(self::HAS_MANY, 'School', 'id', 'together'=>true ),

                );

	}

Could you please tell me what i am doing wrong here?

6

I am getting Array as a value result value though my query is proper as i have checked the output into my phpmyadmin.

here is the function form my student controller


 public function getSchoolName($id)

        {

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

                $command = "SELECT sch.name FROM `tbl_student` AS st LEFT JOIN tbl_school AS sch ON st.school_id = sch.id WHERE st.id='".$id."' ";

                $dataReader=$connection->createCommand($command)->query();

                $rows=$dataReader->readAll();

                return $rows;


        }

n my view page is like this-


 <b><?php echo CHtml::encode($data->getAttributeLabel('school_id')); ?>:</b>

	<?php echo $data->getSchoolName($data->id);?>

	<br />

Can anyone please tell me what is wrong here?

7

I am getting values now. :)

Tried something like this-




  <b><?php echo CHtml::encode($data->getAttributeLabel('school_id')); ?>:</b>

	<?php

            $schoolDtls = $data->getSchoolName($data->id);

            echo $schoolDtls['name'];

        ?>

	<br />

Its working now. Thanks.

8

Now if i want to click on the id(which is the link) and got the the "view" page, i am not able to get the school name

here is my code

student/views/view.php




<?php $this->widget('zii.widgets.CDetailView', array(

	'data'=>$model,

	'attributes'=>array(

		'id',

		'firstname',

		'lastname',

		'email',

                'school_id',

                

	),

)); ?>

I want to have school name value there instead of school_id. what should i change to get the value?

9

Please take a look at documentation first, I think it’ll give you a good start with this question

Regards

10

Your relation name is currently school_id, which clashes with the database field attribute and is not a good description for the relation. Calling is school would be a better bet. It also looks like the wrong attribute type, a Student belongs to a School and a School has many Students. So the relations function should probably be:




public function relations()

{

    return array(

        'school' => array( self::BELONGS_TO, 'School', 'school_id' ),

    );

}

Then to get the school name in the CDetailView you would be able to use:

‘school.name’.