Help FindByAllSql


$room = Room::model()->findAllBySql('SELECT name FROM am_room');

				

foreach ($room as $record){

  echo $record['name'];

  echo "<br>";

  $asset = Asset::model()->findAllBySql("SELECT asset_name FROM am_asset where room like ':room'", array('room' => 'Lab01'));

					

    foreach ($asset as $a){

	echo $a['asset_name'];

	echo "<br>";

    }

}

the result is:

[b]Lap01

[/b]

the code line


$asset = Asset::model()->findAllBySql("SELECT asset_name FROM am_asset where room like ':room'", array('room' => 'Lab01'));



is not return result if I replace it with this line bellow:


$asset = Asset::model()->findAllBySql("SELECT asset_name FROM am_asset where room like 'Lab01'");

the result is:

[b]Lap01

Monitor Elead 15[/b]

pls help me!

Please do not make duplicate posts (you posted the same question in the bug section that i deleted)

As for your problem… in the first findAllBySql when writing parameters you need to write the double colon too like:


array(':room'=>'Lab01')

P.S. when posting code use the code button (<> on the editor toolbar)so that it’s more readable

I fixed this problem but it still doesn’t working!


$rooms = Room::model()->findAllBySql('SELECT name FROM am_room');

                                

foreach ($rooms as $room){

  echo $room['name'];

  echo "<br>";

  $asset = Asset::model()->findAllBySql("SELECT asset_name FROM am_asset where room like ':room'", array(':room' => 'Lab01'));

                                        

    foreach ($asset as $a){

        echo $a['asset_name'];

        echo "<br>";

    }

}


$rooms = Room::model()->findAllBySql('SELECT name FROM am_room');

                                

foreach ($rooms as $room){

  echo $room['name'];

  echo "<br>";

  $asset = Asset::model()->findAllBySql("SELECT asset_name FROM am_asset where room like ':room'", array(':room' => $room['name']));

                                        

    foreach ($asset as $a){

        echo $a['asset_name'];

        echo "<br>";

    }

}

pls, someone can fix this problem!

Please don’t take this the wrong way.

It looks like you are new to Yii and used to doing things your own way. Nothing wrong with that, but…

I think you need to spend a few minutes learning model relations - this code could be much easier and Yii does so much of the heavy lifting for you.

yes, I’m a student. I’m using Yii framework for developing my assignment.

Can you give me a sample or a hint!

What is the relationship between room and asset?

Does each room HAS_MANY assets and each asset BELONGS_TO a room?

Yes, each room HAS_MANY assets.

Room model:




<?php


/**

 * This is the model class for table "{{room}}".

 *

 * The followings are the available columns in table '{{room}}':

 * @property string $name

 * @property string $description

 */

class Room extends CActiveRecord

{

	/**

	 * Returns the static model of the specified AR class.

	 * @return Room the static model class

	 */

	public static function model($className=__CLASS__)

	{

		return parent::model($className);

	}


	/**

	 * @return string the associated database table name

	 */

	public function tableName()

	{

		return '{{room}}';

	}


	/**

	 * @return array validation rules for model attributes.

	 */

	public function rules()

	{

		// NOTE: you should only define rules for those attributes that

		// will receive user inputs.

		return array(

			array('name', 'required'),

			array('name', 'length', 'max'=>100),

			array('description', 'safe'),

			// The following rule is used by search().

			// Please remove those attributes that should not be searched.

			array('name, description', 'safe', 'on'=>'search'),

		);

	}


	/**

	 * @return array relational rules.

	 */

	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(

		);

	}


	/**

	 * @return array customized attribute labels (name=>label)

	 */

	public function attributeLabels()

	{

		return array(

			'name' => 'Name',

			'description' => 'Description',

		);

	}


	/**

	 * Retrieves a list of models based on the current search/filter conditions.

	 * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.

	 */

	public function search()

	{

		// Warning: Please modify the following code to remove attributes that

		// should not be searched.


		$criteria=new CDbCriteria;


		$criteria->compare('name',$this->name,true);

		$criteria->compare('description',$this->description,true);


		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

	}

}



Asset Model


<?php


/**

 * This is the model class for table "{{asset}}".

 *

 * The followings are the available columns in table '{{asset}}':

 * @property integer $asset_id

 * @property string $asset_no

 * @property string $asset_name

 * @property string $asset_type

 * @property string $sub_type

 * @property string $important_level

 * @property integer $staff

 * @property string $room

 * @property double $asset_value

 * @property integer $use_time

 * @property string $status

 * @property string $hoctu

 * @property string $bophan

 * @property string $started_date

 * @property string $note

 *

 * The followings are the available model relations:

 * @property Account $staff0

 */

class Asset extends CActiveRecord

{

	private $_kyhan;

	/**

	 * Returns the static model of the specified AR class.

	 * @return Asset the static model class

	 */

	public static function model($className=__CLASS__)

	{

		return parent::model($className);

	}


	/**

	 * @return string the associated database table name

	 */

	public function tableName()

	{

		return '{{asset}}';

	}


	/**

	 * @return array validation rules for model attributes.

	 */

	public function rules()

	{

		// NOTE: you should only define rules for those attributes that

		// will receive user inputs.

		return array(

			array('asset_type, sub_type, important_level, room, asset_value, status, hoctu, bophan', 'required'),

			array('staff, use_time', 'numerical', 'integerOnly'=>true),

			array('asset_value', 'numerical'),

			array('asset_no', 'length', 'max'=>50),

			array('asset_name', 'length', 'max'=>300),

			array('asset_type, sub_type, important_level, room, status, hoctu, bophan', 'length', 'max'=>100),

			array('started_date, note', 'safe'),

			// The following rule is used by search().

			// Please remove those attributes that should not be searched.

			array('asset_id, asset_no, asset_name, asset_type, sub_type, important_level, staff, room, asset_value, use_time, status, hoctu, bophan, started_date, note', 'safe', 'on'=>'search'),

		);

	}


	/**

	 * @return array relational rules.

	 */

	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(

			'staff0' => array(self::BELONGS_TO, 'Account', 'staff'),

		);

	}


	/**

	 * @return array customized attribute labels (name=>label)

	 */

	public function attributeLabels()

	{

		return array(

			'asset_id' => 'Asset',

			'asset_no' => 'Asset No',

			'asset_name' => 'Asset Name',

			'asset_type' => 'Asset Type',

			'sub_type' => 'Sub Type',

			'important_level' => 'Important Level',

			'staff' => 'Staff',

			'room' => 'Room',

			'asset_value' => 'Asset Value',

			'use_time' => 'Use Time',

			'status' => 'Status',

			'hoctu' => 'Hoctu',

			'bophan' => 'Bophan',

			'started_date' => 'Started Date',

			'note' => 'Note',

		);

	}

	/**

	 * Retrieves a list of models based on the current search/filter conditions.

	 * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.

	 */

	public function search()

	{

		// Warning: Please modify the following code to remove attributes that

		// should not be searched.


		$criteria=new CDbCriteria;


		$criteria->compare('asset_id',$this->asset_id);

		$criteria->compare('asset_no',$this->asset_no,true);

		$criteria->compare('asset_name',$this->asset_name,true);

		$criteria->compare('asset_type',$this->asset_type,true);

		$criteria->compare('sub_type',$this->sub_type,true);

		$criteria->compare('important_level',$this->important_level,true);

		$criteria->compare('staff',$this->staff);

		$criteria->compare('room',$this->room,true);

		$criteria->compare('asset_value',$this->asset_value);

		$criteria->compare('use_time',$this->use_time);

		$criteria->compare('status',$this->status,true);

		$criteria->compare('hoctu',$this->hoctu,true);

		$criteria->compare('bophan',$this->bophan,true);

		$criteria->compare('started_date',$this->started_date,true);

		$criteria->compare('note',$this->note,true);


		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

	}

}

A tip:

Read the relational AR section of the Definitive Guide.

You want to setup relations for asset and room.

In the Room model when you have a relation named ‘assets’, you can do this:




Pseudocode:


$room=Room::model()->find__(whatever find method you want to use)

foreach $room

  //echo $room->someAttribute;

  foreach $room->asset as $roomAsset

    //echo $roomAsset;



Thanks dniznick so much!