Where to format data query results

[eluser]gfraser99[/eluser]
I am wondering what the best practice is for formatting data query results, as in should this be done in the model, controller or view. Here is the exact example I am dealing with.

I want to populate a select list which takes an array with 1 key and 1 value to populate.

Code:

Array
(
    [1] => 'apples'
    [2] => 'oranges'
)

My model query result returns all records and all columns:

Code:

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => 'apples'
        )

    [1] => Array
        (
            [id] => 5
            [name] => 'oranges'
        )

)

The array I need to pass to my view does not match the format my model returns the data in.
1. Should I build another model method to return only the data I need?
2. Should I format the returned data to the array format I require to populate my select list in my view within the controller?
3. Should the logic be built in the view?

[eluser]TWP Marketing[/eluser]
The model is intended to access your database and then massage the returned data into a format needed by the controller, which in turn passed it to the view in finished or almost finished form.

The controller may need to manipulate data received from the model, based on other values, such as configuration, login status, etc., but do most of the formatting in the model. That way, if you someday change the db, you only need to change your model and not the controller or views.

About the only data manipulation done in the view is to loop an array of values to be echo'd to display.

Your option 1 is the preferred MVC way of handling data.

[eluser]gfraser99[/eluser]
Yes, the way I have implemented it is to create a common method that all models can access which does this:

Code:

/**
* Return an array of qualified record arrays by key value pair
* @param string The database column you want the return array keyed by
* @param string The database columne you want the return array value to contain
* @return array Array of qualified record arrays or empty array if no units were retrieved
*/
    function get_list($key, $value, $table=NULL){    
    
  $query = $this->db
           ->get($this->primary_table);
    
    
  if($query->num_rows > 0){
   foreach($query->result_array() as $row){
    $arrReturn[$row[$key]] = $row[$value];
   }
   return $arrReturn;
  }
  return array();
    }

$data['dropdown'] = $this->model->get_list('id', 'name');

[eluser]TWP Marketing[/eluser]
So you have a generic method to read a table. Now you can call that method from another model method (Same model file) and process the result(s) from the generic method to format it as needed by the controller. All the work is done in the model.

[EDIT]
If you want your model method(s) to be accessible from other models, you might look into a library instead.
The User Guide is your friend<G>...

[eluser]Aken[/eluser]
[quote author="TWP Marketing" date="1345573536"]If you want your model method(s) to be accessible from other models, you might look into a library instead.[/quote]

Be easier to just load models inside models.

[eluser]Unknown[/eluser]

Code:

/**
  * model class
  * database:  user
  * field:   id username
  * @return array()
  */

function get_usernames()
{
  $data = array();
  $query = $this->db->get('user');
  if ($query->num_rows() > 0){
   foreach ($query->result() as $row){
    $data[] = $row->username;    //fetch only username
   }
   $query->free_result();
  }
  return $data;
}

[eluser]gfraser99[/eluser]
Actually..what I did to was make MY_Model extend CI_Model and store it in the application/core folder. Then all my other models extend off MY_Model, such as User_Model.

This enables me to create a parent method in My_Model like:
primary_table is set as a property in the child model class.

Code:

/**
* Return an array of qualified record arrays by key value pair
* @param string $key The key you want to index the return recordset by (eg..id)
* @param string $value The value you want to grab from the recordset to set as the value in the return array
* @return array Array of qualified record arrays or empty array if no units were retrieved
*/
function get_list($key, $value, $table=NULL){    
    
    if(is_null($table)){
        $table = $this->primary_table
    }
    $query = $this->db
             ->get($table);
    
    
   if($query->num_rows > 0){
      foreach($query->result_array() as $row){
        $arrReturn[$row[$key]] = $row[$value];
     }
     return $arrReturn;
   }
   return array();
}

If I need a list of ids and names I would simply call:

Code:

$this->load->model('model_name');
$arrNames = $this->model_name->get_list('id', 'name');

This is accessible to all my child models and I don't have to keep writing that function which I use in almost all my models. Shapes the data in a return ready to drop into a CodeIgniter Select list.

[eluser]Alejus[/eluser]
The last code in the post have some bugs, this works for me:

In the controller:

Code:

$data['lista_tipos'] = $this->global_model->get_Sec('tipos_id','nombretipos','tipos'); //$id, $name, $from

In the model

Code:

function get_Sec($id, $name, $from){

$result = array();

$result_key = $this->db->query('SELECT '.$id.', '.$name.' FROM '.$from.' ORDER BY '.$id.' ASC');

  foreach($result_key->result() as $row)
    {
    $result[$row->$id]= $row->$name;
    }
    
return $result;

}

In the view:

Code:

$it = $isi['tipos_id']; // sent by the form
echo form_dropdown('tipos_id', $lista_tipos, set_value('tipos_id', $it));

Alejus