I will try and keep this as simple as poss but please forgive me if I don't explain things too clearly.
I have a table 'Books' with primary key 'BookID'.
Now, because a book can have multiple authors, editors, illustrators, consultants etc and any mix of those contributors I have created 3 additional tables as follows;
BookContributors
with fields
BookID,
ContributorID
Contributors
with fields
ContributorID
Name
StatusID
ContributorStatuses
with fields
StatusID
StatusDescription
and setting up PHPR with the SQL works fine in that it returns all of the contributors for any one book except that it insists on outputting a separate row on the list page for each contributor where I would like all the contributors to be in the Contributor column of just one row. I have exhausted my limited experience and would ask if anyone has any suggestions? I sure it must be possible. I have got around it by inserting custom code in the listing php but would rather have PHPR do it for the sake of compliance.
The SQL I have so far (very cut down) is
SELECT
books.id,
books.title,
contributors.name,
contributorstatuses.contribution
FROM books
INNER JOIN bookcontributors ON books.id = bookcontributors.bookID
INNER JOIN contributors ON bookcontributors.contributorID = contributors.id
INNER JOIN contributorstatuses ON contributors.statusID = contributorstatuses.id
WHERE books.statusID =1
thanks
Annalise