Welcome Guest, Not a member yet? Register   Sign In
output mysql result: n:m relationship
#1

[eluser]lolmann[/eluser]
I am relatively new to CI and also to database modeling. I did search for older posts on this topic and couldn't find the answer. But if you know about a post/article which answers this questions, feel free to link to it!

I have a book library database with three tables:
-books,
-authors and
-books2authors

books2authors defines which books have been written by which authors. Since an author can write multiple books and a book can be written by multiple authors this is a n:m relationship, right?

I'm trying to output a list of all my books in the form

- Author: Book Title
- Author2: Book Title2
etc.

I managed to do this.

The tricky part for me is: what to do if a book has more than one author?

I select the data from the database in my model like this:

Code:
$this->db->distinct();
        $this->db->join('books2authors', 'books2authors.book_id = books.book_id');
        $this->db->join('authors', 'books2authors.author_id = authors.author_id');
        $query = $this->db->get('books');
        return $query->result();

But if a book has been written by two authors, this query gives me a result row for each of the authors. My list will then look like this:

- Author1: Book Title1
- Author2: Book Title1

Whereas I want it to look like:

-Author1, Author2: Book Title1

I experimented adding a GROUP BY statement to the query:
Code:
$this->db->group_by("books.book_id");
But this causes the second author of the book to disappear entirely and my book list looks like this:

- Author1: Book Title1

What I need is a query that returns an array or object of books with a sub-array/sub-object of authors so I could walk through the list in my view file and check whether there is more than one author to a book in the list.

Another way would be to use two queries: one to get all the books and one to find all the corresponding authors. But I'm trying to avoid this because this would increase the number of MySql queries a lot. I'd prefer the first solution.

Does anyone have an idea how to solve this?

(I hoe my problem has become clear. If not so, feel free to say it and I'll try to re-explain.)
#2

[eluser]crumpet[/eluser]
I think you want the mysql function GROUP_CONCAT
http://dev.mysql.com/doc/refman/5.0/en/g...oup-concat
#3

[eluser]lolmann[/eluser]
Thank you crumpet! GROUP_CONCAT does the job.

It's still a hassle because I have to deal with the string (explode it and do things with the resulting array) but at least it saves me a lot of mysql queries.




Theme © iAndrew 2016 - Forum software by © MyBB