How To Run A Sql Request With Yii

Yii 1.1 General Discussion
1

Hello Folks,

I would like to know how can I use this SQL request inside YII and where should I insert it inside controller or the view code ?

Basically, I wand to have a dropdownlist and select if yes or no a hotel room has a picture or not ?


SELECT *

FROM hotel AS p, hotel_room AS q

WHERE `Image` LIKE '%No_Image%'

AND p.id=q.id

Thank you in advance

2

See the tutorial Working with Databases.

3

thank you for the reply.

Please do you have any clear eg, I can not understand it :unsure:

4

See chapter 2 of the tutorial:




$sql="SELECT * FROM hotel AS p, hotel_room AS q WHERE `Image` LIKE '%No_Image%' AND p.id=q.id";


$rows=Yii::app()->db->createCommand($sql)->queryAll();

var_dump($rows);  //an array of your record rows
5

Thank you Joblo for your help,

Yes I saw that, but what I want is how to use it inside a dropdownlist in view code ?


      . CHtml::encode('Have Image')

            . CHtml::dropdownList('Image', 'Image_id', array(

                /*Should I insert it here? */=>'Yes',

                /*Should I insert it here? */=>'No',),

             array('options' => array($_GET['Image']=>array('selected'=>true)),

                   'style' => 'width:100%;'))

or should I insert it first in Model or Controller and after call it in View ?

I guess I wasn’t clear when I said " I can not understand it", it’s my fault sorry

6

Don’t place the sql-queries in the view.

You should prepare the db-data in the controller or maybe a helper class with static methods.




class DbData {


 public static function getHaveImageDropDownData()

 {

    $sql="SELECT * FROM hotel AS p, hotel_room AS q WHERE `Image` LIKE '%No_Image%' AND p.id=q.id";

    $rows=Yii::app()->db->createCommand($sql)->queryAll();

    return CHtml::listData($rows, ....); 


 } 


 ...


}

In your view:




CHtml::encode('Have Image')

CHtml::dropdownList('Image', 'Image_id', DbData::getHaveImageDropDownData(),...);
7

thank you very much Joblo,

sorry but the … in


return CHtml::listData($rows, ....);

are for what exactly :unsure: ?

8

Please take a look at the reference and tutorials, wiki articles how to work with Yii.

9

sorry Im a beginner with basic knowledge and quiet lost :unsure:

anyway thank you very much for your help

10

Take the tutorials(definitiv guide, blog tutorial,…) as starting point.

There you always have the links to the classes reference. It’s very important to know the classes and methods and how they can help you to implement your solutions.

Maybe a Yii book is a good choice too.

I did PHP programming a lot of years without a framework.

I didn’t like the existing, but I was happy when I detected Yii.

It’s worth to start from scratch, but it’s worth every minute :wink:

It speeds up coding a lot and helps you to generate clean code if you know how to …