data structure challenge

[eluser]Random dude[/eluser]
I'm fairly new to php, and I'm wondering how I can gather this particular set of data as a single $variable in a Model, and then pass it to a view.

I have categories, and each category has items within it. items have "id" and "title" feilds.

In my view I want to print:

category 1
item_id item_title
item_id item_title
item_id item_title
item_id item_title

category 2
item_id item_title
item_id item_title
item_id item_title
item_id item_title

category 3
item_id item_title
item_id item_title
item_id item_title
item_id item_title

etc.

My first try was a multidimensional array
$array[ $category_name ] = array ( $item_id => $item_title, $item_id => $item_title ....)
and repeat.

So in the view I can:

echo $category_name;
echo $item_id echo $item_title
echo $item_id echo $item_title


What do you think?
Current state of affairs is I can even get my code to compile!

[eluser]Johan André[/eluser]
Create a table: categories
id int(11) primary_key auto_increment
title varchar(100)

Create a table: items
id int(11) primary_key auto_increment
title varchar(100)
category_id int(11)

Use a join:

Code:

$this->db->select('c.title AS category_title');
$this->db->select('i.title AS item_title');
$this->db->from('categories c');
$this->db->join('items i', 'i.id = c.category_id');
$results = $this->db->get()->result();

Spice the query with a $this->db->where('c.id', $category_id); to get just items one category. You probably should do $this->db->order_by('c.title', 'asc'); too...

In the view - iterate through the rows and print the category only if it's not the same as last iteration.

[eluser]jedd[/eluser]
A la [url="http://ellislab.com/forums/viewthread/136469"]this thread[/url] and [url="http://ellislab.com/forums/viewreply/622401/"]this one too[/url].

[eluser]Developer13[/eluser]
[quote author="Nicholas Reed" date="1262712459"]Current state of affairs is I can even get my code to compile![/quote]

Huh? Compile??

[eluser]Random dude[/eluser]
[quote author="Developer13" date="1262741478"][quote author="Nicholas Reed" date="1262712459"]Current state of affairs is I can even get my code to compile![/quote]

Huh? Compile??[/quote]

Hahaha. Execute without error perhaps!