On Mon, Apr 6, 2009 at 2:05 PM, PJ <af.gour...@videotron.ca> wrote:
> My code already has selected the books whose authors last names start
> with A as well as the authors themselves.
> Within the results some books have 2 authors.
If you already have the book IDs where this happens, then for each bookID
(probably a foreach loop in your code) simply do a query at that point that
will obtain ALL authors for that bookID -- don't include the condition that
last name start with an A in this query.
> $query = "SELECT b.id <http://b.id>, GROUP_CONCAT(CONCAT(' ',
> > a.first_name, a.last_name) ORDER BY a.last_name)
> I believe this should be (CONCAT_WS(' ', a.first_name, a.last_name) or
> (CONCAT(a.first_name,' ', a.last_name)
Sorry, yes that should be
GROUP_CONCAT( CONCAT(' ', a.first_name, a.last_name) ORDER BY a.last_name)
so that it concatenates each author as "FirstName LastName" and then groups
all authors for a given book separated by commas, so bookID=1 would have
something like "Author1FirstName LastNameAuthor1", "FirstNameAuthor2
Problem here is that I need to use the ba.ordinal somewhere in the cod
> to distinguish between the authors - if ba.ordinal is 1, the author is
> listed as the first author and if there is no ba.ordinal = 2 for the
> ba.bookID, then there is only 1 author. I have not included this in my
> query because it only returns the ordinal number for the author which
> does not help me at all. I need to know who the second author is.
> In effect, it would be great if I could get 2 joins on the same table
> (author) for
> 1. (CONCAT(first_name, ' ', last_name) AS Author WHERE ab.ordinal = 1
> && LEFT(last_name, 1) = $Auth
> 2. (CONCAT(first_name, ' ', last_name) AS Author1 WHERE ab.ordinal = 2
The problem you'd run into with #1 above, is you'd only get authors whose
last name starts with "A" if they're the primary author. Is that the desired
functionality you're looking for?