[eluser]Taff[/eluser]
I have (as so often) got myself in a predicament which I thought I would be able to solve with join.
I would like to get a list of lists (just to make it complicated) from a table which are then output like this:
Code:
[0] => stdClass Object
(
[user_id] => 2
[list_id] => 2
[l_title] => test title
[l_notes] => with some notes
[l_milestone_id] => 5
[l_responsible] => 0
[l_tracked] => 0
)
[1] => stdClass Object
(
[user_id] => 2
[list_id] => 4
[l_title] => A second to do list
[l_notes] => with some more notes
[l_milestone_id] => 5
[l_responsible] => 1
[l_tracked] => 0
)
For each of these lists I have a varying number of items in a different table with a field called list_id.
My first approach (which failed miserably) was this:
Code:
function get_todo_list($who){
$this->db->select('*');
$this->db->from('todo_list');
$this->db->join('todo_item','todo_list.list_id = todo_item.list_id');
$this->db->where('user_id',$who);
$query = $this->db->get();
}
Which I was hoping would produce something like:
Code:
[0] => stdClass Object
(
[user_id] => 2
[list_id] => 2
[l_title] => test title
[l_notes] => with some notes
[l_milestone_id] => 5
[l_responsible] => 0
[l_tracked] => 0
[0]=>
(
[item_id] => 1
[list_id] => 2
)
[1]=>
(
[item_id] => 2
[list_id] => 2
)
)
[1] => stdClass Object
(
[user_id] => 2
[list_id] => 4
[l_title] => A second to do list
[l_notes] => with some more notes
[l_milestone_id] => 5
[l_responsible] => 1
[l_tracked] => 0
[0]=>
(
[item_id] => 1
[list_id] => 4
)
[1]=>
(
[item_id] => 2
[list_id] => 4
)
)
So do I need to get the results just from the todo_list and then loop through them somehow and make another database query for each of the results?
Any help or advice would be greatly appreciated.
Thanks in advance,
Taff
EDIT:Just to clear up I would like to do this:
[0] => stdClass Object
(
[user_id] => 2
[list_id] => 2 //Using this id I would like to query a table and return all the results as either an oject or a array somewhere here
[l_title] => test title
[l_notes] => with some notes
[l_milestone_id] => 5
[l_responsible] => 0
[l_tracked] => 0
)
[1] => stdClass Object
(
[user_id] => 2
[list_id] => 4//Using this id I would like to query a table and return all the results as either an oject or a array somewhere here
[l_title] => A second to do list
[l_notes] => with some more notes
[l_milestone_id] => 5
[l_responsible] => 1
[l_tracked] => 0
)
So that I can output something like this:
Code:
echo "To do list title: ".$data['l_title'];
echo "Notes: ".$data['l_notes'];
echo "Contains the following items:";
foreach ($data['sublist']->result as $row){
echo $row['item_title'];
}
echo "List assigned to ".$data['l_responsible'];
//grab the next item in a similar way
Hope somebody has done something like this in the past.
Taff