search control

Yii 1.1 General Discussion
1

How i connecta result of a query to dropdownlist???

I have this code




public function actionSearch()

	{

		$model=new anagrafe;

		

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

		{

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

				$cognome = strtoupper( $_POST['cognome']);

				$nome= strtoupper($_POST['nome']);

				$connection=new CDbConnection('oci:dbname=//10.0.0.227:1521/oraprod','ora02','oracle');

				$connection->active=true;

				$command=$connection->createCommand("SELECT ANAGRA_ID FROM ANAGRAFE WHERE   NOME='$nome' AND COGNOME='$cognome'");

				$command->execute();   

				$reader=$command->query();

				 foreach($reader as $row) 

					{

						$ID= $row['ANAGRA_ID'];

					}

			

				$this->redirect(array('view','id'=>$model->ANAGRA_ID=$ID));

		}


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

			'model'=>$model,));

	

		}

I wish if the variable $ID has more than one result will appear in a dropdownlist or other control where then i can choose which view

2

You should read the documentation about CActiveRecord, because you should not do the query as you did but use CActiveRecord->findAll();

You can create a dropDownList poplated with activeRecords like that:




$criteria=$model->search();




echo CHtml::activeDropDownList($model,'ANAGRA_ID',

        CHtml::listData(Anagrafe::model()->findAll($criteria),

	'ANAGRA_ID','COGNOME')

);

Data are retrived by Anagrafe::model()->findAll($criteria), wich return an array of active record.

You can read the documentation about CActiveRecrod::findAll() for understand how it works

Data are filtered by $criteria, that is an instance of CDbCriteria created by the model.

You can read the documentation about CDbCriteria for understand how it works.

CHtml::activeDropDownList create a dropDownList for the field ANAGRA_ID of your model. So when this ddl will be posted, in your model there will be the selected ANAGRA_ID.

You can read the documentation about CHtml::dropdownlist for understand how it works.

The third parameter of CHtml::activeDropDownList should be an array, that’s why we use CHtml::listData, a function that create an array of data from an array of CActiveRecord.

You can read the documentatio about CHtml::listData for understand how it works.

The documentation of Yii is good and understandable, the main problem is to understand what to read for resolve a particular problem.

I am advicing you where to read the information about how is all working, so, please, try reading all that before trying to code.

3

So, I know how dropdownlist and lisdata works because I’ve already used in other contexts but I don’t understand how I create this line of code


$criteria=$model->search();

where should I create? In view? In the controller?

I should create a function search() in my model doesn’t exist this function…?

It is unclear…can you help me???

4

If you use a model generated with gii (or yiic shell) by one of the last version of framework,

there should already be the function search() in the model.

The function search of a model is smth like that:




	/**

	 * 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('NOME',$this->NOME,true);


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


		return new CActiveDataProvider(get_class($this), array(

			'criteria'=>$criteria,

		));

	}

This function returns a CDbCriteria on wich has been many time called the function compare. This function adds some condition to the where of the query, so it will look like “WHERE NOME LIKE ‘%$this->NOME%’ AND COGNOME LIKE ‘%$this->COGNOME%’”.

so, in the controller there should be smth like that:




public function actionSearch()

{

	$model=new anagrafe;


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

	{

                //return the dataProvider with all comparison

		$dataProvider=$model->search();

		

	}


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

	'model'=>$model,

	'criteria'=>$dataProvider->criteria // the criteria, from data provider

	));


}

I mistook in my previous post, because the search function returns a CActiveDataProvider, not a CDbCriteria. Anyway you can get the CDbCriteria from the dataProvider easily.

At this point in the view you can write your dropdownlist like that:




echo CHtml::activeDropDownList($model,'ANAGRA_ID',

        CHtml::listData(Anagrafe::model()->findAll($criteria),

        'ANAGRA_ID','COGNOME')

);

Please be advised that I didn’t tested this code, I just copied from different files and contextualize a bit, so there can be sintax error. Is just an idea about what is the best practice (or, al least, how the framework is usually doing)

5

I have this error…

CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 936 OCIStmtExecute: ORA-00936: missing expression

6

Zaccaria, thanks for the post. It’s really helped a lot. It took a bit of fiddling as I’m reasonably new to Yii, but it makes for a nice, succinct solution.

Matt

7

Thank you very much for appreciate my post!

You are absolutely the first guy I see posting his first post only for thanks, without asking anything, my congrats.

I will be pleased to answer to any of your question in future.