Need some DB help

[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:

<?php foreach ($query->result() as $row) { ?>
<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.

[eluser]zilverdistel[/eluser]
Hi there, I'm just thinking loud, but what about a

Code:

group_by('title')

?

[eluser]zilverdistel[/eluser]
then maybe you might do something like

Code:

<?php
$counter = 1;
foreach ($query->result() as $row) {
   $title = $row->title;
   while ($row->title == $title && $counter <= $query->num_rows()) {
      ?&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
      $row = $query->next_row();
      $counter++;
   }
}
?&gt;

hope this helps ...

[eluser]Firestorm ZERO[/eluser]
Right now I got it working like this...

Code:

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

$query = $this->db->get(); // Get all the books in the book table

if ($query->num_rows() >= 1) // I got books!
{
    $result = $query->result_array(); // Change it to an array

    foreach ($result as &$row) // Go through each row in the array
    {
        // Get the author(s) of each row(book) from the bridge table
        $this->db->select('author.name');
        $this->db->from('book_author');
        $this->db->join('author', 'book_author_id = author.author_id', 'left');
        $this->db->where('book_id', $row['book_id']);

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

        $row['author'] = $query->result_array();
    }

    return $result;
}
else
{
    // No books!
    return FALSE;
}

Looks pretty big :/ So I don't know if it is the best way.

[eluser]zilverdistel[/eluser]
well, there's allways a better way. But if this works, and if we're not talking about a large scale project, I'd stick with this sollution ...

[eluser]Firestorm ZERO[/eluser]
Well I always like to look at alternative ways to get me thinking. To find the best ways to optimize for memory, speed, etc...

I'll look into that "group by" you mentioned.

[eluser]zilverdistel[/eluser]
[quote author="Firestorm ZERO" date="1205432998"]Well I always like to look at alternative ways to get me thinking. To find the best ways to optimize for memory, speed, etc...

I'll look into that "group by" you mentioned.[/quote]

let me know if it works out

[eluser]sandwormusmc[/eluser]

Code:

$sql='SELECT * FROM `books` LEFT OUTER JOIN author ON book_author_id=author.author_id;';
  $result=$this->db->query($sql);
  // error checking
  // return something from the result, i.e. $result->result_array() or $result->result_object()

Otherwise you might be doing 100*N DB queries for 100 books, checking for their authors. The above will be a large data set, too, but refining your SELECT portion to only include the columns you need should help performance.

[eluser]Firestorm ZERO[/eluser]
Yeah. I notice that it will use too many queries the way I'm using it right now.

This will become more of a problem because in my simple example I only have an "author" column. But there are more columns that have a M-M relationship in what I am doing and therefore more bridge tables and then more queries.

So I did some reading and I think this query will do what I want...

Code:

SELECT book.book_id, book.title, GROUP_CONCAT(author.name)
FROM book
LEFT JOIN book_author
ON book.book_id = author.author_id
LEFT JOIN author
ON book_author.author_id = author.author_id
GROUP BY book_id

Result

Code:

1   Book Title 1   Author 1, Author 2
2   Book Title 2   Author 2
3   Book Title 3   NULL (was not in the book_author table so no author)

So the GROUP BY clause will group the multiple rows. And GROUP_CONCAT will concatenate all the values of the rows it grouped by into one field. I'm assuming it will be a string so I will have explode it by the commas and then do a sort on the arrays.

I think GROUP_CONCAT might be MySQL specific. As well there is one problem. There is a limit to how much GROUP_CONCAT can hold. The default is I think 1024 characters.

Just wondering... for the people who do have Many-To-Many relationships in tables. How are you handling them?

[eluser]sandwormusmc[/eluser]
Cheesy ER diagram:

Code:

<1>=-=<2>

where 1 & 2 are tables with a M-M relationship.

translate to

Code:

<1>--=<3>=--<2>

So basically you'll have an intermediary table that holds the IDs of <1> and <2> as a unique entry for each row.

I.E. I track hosts and patches. Each host can have multiple patches installed on it, and each patch can be installed on multiple machines. A third table is necessary with a row specifying each host.id and patch.id to keep it in 3rd Normal Form (3NF).

Seems to work ...