Login

03-12-2008, 04:07 PM

[eluser]Firestorm ZERO[/eluser]
Just wondering how to do this. My mysql is kinda rusty and I dunno if I'm programming it right.

Ok. I am getting a bunch of books. For simplicity the book table has just ID and title. So in my book model I have...

Code:
$this->db->select(*);

$this->db->from('books');

$this->db->order_by('title');

$query = $this->db->get();

return ($query->num_rows >= 1) ? $query : 'FALSE';

The book can have 1 or more authors. So I made a bridge table called "books_authors" which has the Book ID (points to the ID in books table) and the Author ID (points to the ID in authors table) as the primary keys.

eg.

Book ID Author ID
1 1
1 2

Now how do I get it so that my result will have the title and authors (in an array) in each row. So that I can in my view I can have like...

Code:
&lt;?php foreach ($query->result() as $row) { ?&gt;

<tr>

   <td>&lt;?php echo $row->title ?&gt;</td>

   <td>&lt;?php foreach ($row->author as $key => $value) echo $value; ?&gt;</td>

</tr>

&lt;?php } ?&gt;

The problem I am having is to get the authors in an array in the result object. JOIN will make duplicate rows right? Is there a way to assign in $query a field like $query->author = $this->Author_Model->get($book_id); or something like that.