PJ,
On Mon, Apr 6, 2009 at 12:32 PM, PJ <af.gourmet@xxxxxxxxxxxx> wrote:
> I am trying to limit the search for books to only those that start with
> "A" (does not have to be case sensitive); but within that selection
> there may be a second author whose name may start with any letter of the
> alphabet.
>
First off, are you trying to search for book TITLES that start with "A" or
book AUTHORS that start with "A"?
If it's authors, it might make more sense to do a select that retrieves the
book ids that have an author whose last name starts with an "A" and once you
have those IDs, do a second query that will retrieve all authors for those
books. So something like below (UNTESTED):
//Find out which books are written by an author with a last name starting
with $Auth
$SQL = "SELECT b.id FROM book AS b
LEFT JOIN book_author AS ba ON b.id = ba.bookID
LEFT JOIN author AS a ON ba.authID = a.id
WHERE LEFT(last_name, 1) = '$Auth' ";
Then do the following select statement with a foreach loop that retrieves
the book IDs above
$query = "SELECT b.id, GROUP_CONCAT(CONCAT(' ', a.first_name, a.last_name)
ORDER BY a.last_name)
FROM book AS b
LEFT JOIN book_author AS ba ON b.id = ba.bookID
LEFT JOIN author AS a ON ba.authID = a.id
WHERE b.id = $value
GROUP BY b.id "; // $value is the b.id for each iteration of the foreach
loop
More information about the GROUP_CONCAT() function can be found in the MySQL
reference (
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
)
- Lex
