multidimensional array from active record

[eluser]bhbutter123[/eluser]
I have files that are saved with a category field so I want to pull them from a database in the following format for ease of going through them. for example

category 1=>
all files with cat 1
category 2=>
all files with cat 2

that way i can go through with a foreach statement and put the category as a header and list all the files that have that category then move on to the next category and do the same. If I am thinking about this wrong let me know, if I am on the right track, what code would I use for the model? right now I have:

$query = $this->db->get('files_categories');
foreach($query->result_array() as $category_data)
{
$categories[$category_data['id']] = $category_data['category'];
}
foreach($categories as $category_id=>$category_name)
{

$this->db->select('files.*, files_categories.id as category_id');
$this->db->from('files');
$this->db->join('files_categories', 'files_categories.id = files.category', 'right');
$this->db->where('files.category', $category_id);
$this->db->order_by('timestamp', 'desc');
$subquery = $this->db->get();
$result['data'][$category_name] = $subquery->result_array();
}
$query = $this->db->get('files_categories');
if($query)
{
foreach($query->result_array() as $row)
{
$result['categories'][$row['id']] = $row['category'];
}
}
return $result;



it seems extremely klunky to me.

[eluser]tonanbarbarian[/eluser]
why not use one query and just determine if the category has changed
(i dont like queries within loops)
you were actually really close with the query you have inside the loop

Code:

$this->db->select(‘files.*, files_categories.id as category_id, files_categories.category’);
$this->db->from(‘files’);
$this->db->join(‘files_categories’, ‘files_categories.id = files.category’, ‘right’);
$this->db->order_by(‘files_categories.category ASC, timestamp desc’);
$query = $this->db->get();

$last_category = 0;
$result = array('data'=>array(),'categories'=>array());
foreach ($query->result_array() as $row) {
  if ($last_category != $row['category_id']) {
    // code here to process the change of category
    $result['data'][$row['category']] = array();
    $result['categories'][$row['category_id']] = $row['category'];
  }
  $last_category = $row['category_id'];
  // code here to process the file row
  $result['data'][$row['category']][] = $row;
}