I'll try to make it simple. I have a table for my books, one for the authors and a junction table with the id from my books with the author ID.
Now my issue is, I don't want to list the books more than once in the case the book has many authors. I could use the Group By, but I'm looking for a way to list the book once and instead of showing only one author, in the case it has more than one, I'd like to add the other authors in the same listing.
Really thought about this during the day, and I can't think of a good way to do it. Any help is greatly appreciated!
Thanks!
Do two SQL statements. Something like this...
$sql = "SELECT id, name FROM books";
foreach ($conn->query($sql) as $book) {
$sql = "SELECT a.name
FROM authors a, author_books ab
WHERE ab.book_id = $book['id']
AND ab.author_id = a.id";
foreach ($conn->query($sql) as $author) {
echo $row["name"];
}
}
Cheers
see this example
CREATE TABLE book
(
bID INT,
bName VARCHAR(100),
PRIMARY KEY (bID)
);
--
CREATE TABLE author
(
aID INT,
aName VARCHAR(100),
PRIMARY KEY (aID)
);
CREATE TABLE book_author
(
aID INT ,
bID INT ,
PRIMARY KEY (aID,bID),
FOREIGN KEY (aID) REFERENCES author(aID),
FOREIGN KEY (bID) REFERENCES book(bID)
);
INSERT INTO author (aID, aName) VALUES
(1, 'The Doctor'),
(2, 'Dalek1'),
(3, 'Dalek2'),
(4, 'Amy'),
(5, 'Rory');
INSERT INTO book (bID, bName) VALUES
(1, 'The Book of Crazy'),
(2, 'Is Time Really Time'),
(3, 'I Think without Pants'),
(4, 'Perfection'),
(5, 'Rise of the Daleks ');
INSERT INTO book_author (aID, bID) VALUES
(4, 1),
(1, 2),
(4, 2),
(5, 2),
(1, 3),
(2, 4),
(2, 5),
(3, 5);
----------
--the query
SELECT DISTINCT b.bName
FROM book b,author a,book_author ba
WHERE ba.aID=a.aID and ba.bID=b.bID and (a.aName = 'Amy' or a.aName='The Doctor')
--------------
--output
--Is Time Really Time
--I Think without Pants
--The Book of Crazy
all you need to do is use the DISTINCT keyword and follow Justin Wood's method
I think what you are looking for is GROUP_CONCAT
SELECT b.Name, GROUP_CONCAT(a.Name) AS Authors
FROM Book b
INNER JOIN BookAuthor ba
ON b.ID = ba.BookID
INNER JOIN Author a
ON ba.AuthorID = a.ID
GROUP BY b.Name;
Provides the output
50 Shades of Grey EL James
Design Patterns: Elements of Reusable Object-Oriented Software Ralph
Johnson,Erich Gamma,John Vlissides,Richard Helm
Harry Potter and the Goblet of Fire JK Rowling
Harry Potter and the Philosopher`s Stone JK Rowling