[eluser]bigtony[/eluser]
[quote author="dotty" date="1256912163"]Could you expand on this using 'stories' and 'comments' as examples?[/quote]
To use your original question about stories, authors and comments you would have 3 database tables something like this:
'Author' table: author_id, author_name
'Story' table: story_id, story_title, story_text, story_author_id
'Comment' table: comment_id, comment_text, comment_story_id
Assume from above that author_id, story_id and comment_id are the primary keys (probably auto-increment).
Hence you have a hierarchy of data, being that each author can have any number of stories and each story can have any number of comments. Each database table will have its own model containing methods to insert, update, delete, retrieve, etc.
So let's say you want to show one particular story along with the name of the author and all comments made for that story. Your 'Story' model could contain a function like this:
Code:
function get_complete_details($story_id) {
$this->db->from('story');
$this->db->join('author', 'story_author_id = author_id'); // Get author details
$this->db->join('comment', 'story_id = comment_story_id', 'LEFT'); // Get all comments (if any)
$this->db->where('story_id', $story_id);
return $this->db->get();
}
The above should return a result set you can pass to your view to foreach through. Each row will be for each different comment with the story & author data duplicated. So you would only display the story & author on the first pass through the loop and from then on just the comments. If there are no comments for the story there should still be 1 row, but with the comment fields empty.
Obviously above is not tested but is just to give a flavour of how you could go about it. And your app is probably more complex (i.e you may allow more than one author per story).
