I am working on a site, i have got a form view and wanted to replace the input box with a select dropdown with possible values coming from the database.
I managed to get this working, but did so by placing a bunch of code in the _form.php view (which I am sure is wrong).
So I wanted to know the correct way to achieve what I wanted.
Here is a snippet of code from the _form.php view.
<div class="row">
<?php echo $form->labelEx($model,'personId'); ?>
<?php
// Use the results from this query to create the dropdown.
$command = Yii::app()->db->createCommand('SELECT id, firstName FROM Person');
$results = $command->queryAll();
echo $form->dropDownList($model,'personId', CHtml::listData($results, 'id', 'firstName')); ?>
<?php echo $form->error($model,'personId'); ?>
</div>
I have taken out some of the SQL statement to make it more readable, but this give the jist of what I want.
So basically, do I create an action in the associated controller? I really have no idea as im new to Yii and MVC in general.
Hey thanks for the quick reply. That worked! Maybe I should have included my entire query since I think it makes the solution a bit different. Although hopefully not.
<div class="row">
<?php echo $form->labelEx($model,'personId'); ?>
<?php
// Use the results from this query to create the dropdown.
$command = Yii::app()->db->createCommand('SELECT id, CONCAT_WS(" ", firstName, lastName) AS firstAndLastName FROM Person WHERE id NOT IN (SELECT personId FROM ContactDetails)');
$results = $command->queryAll();
echo $form->dropDownList($model,'personId', CHtml::listData($results, 'id', 'firstAndLastName'));
?>
<?php echo $form->error($model,'personId'); ?>
</div>
So where would I put that SQL statement? I’m guessing it doesn’t belong in the view.
Basically what I did was in my associated model I created the following function:
public function getFirstAndLastName()
{
$rows = Yii::app()->db->createCommand('SELECT id, CONCAT_WS(" ", firstName, lastName) AS firstAndLastName FROM Person WHERE id NOT IN (SELECT personId FROM ContactDetails)')->queryAll();
return $rows;
}
Oh I see now what you mean, I didnt understand yesterday on the chat.
It’s a Mvc (Model view controller) which is what most frameworks use to structure the code.
Most people prefer to but the code for finding data in the model (for advanced querys while a simple find() goes into the controller) , code for printing it in the view and use the controller to connect the two.
So your solution seems is fine but you could make it slightly better
public function getFirstAndLastName()
{
$rows = Yii::app()->db->createCommand('SELECT id, CONCAT_WS(" ", firstName, lastName) AS firstAndLastName FROM Person WHERE id NOT IN (SELECT personId FROM ContactDetails)')->queryAll();
return CHtml::listData( $rows, 'id', 'firstAndLastName');
}
<?php echo $form->dropDownList($model, 'personId', ContactDetails::model()->getFirstAndLastName()); ?>