PJ wrote:
I've searched the web, the tutorials, etc. with no luck and have asked
on MySql list with no luck and have even posted here with no replies.
So, let's try again:
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.
So, the real problem is to find if there is a second author in the
selection of books and to display that name.
My query shows all the books that begin with A:
$SQL = "SELECT b.*, c.publisher, a.first_name, a.last_name
FROM book AS b
LEFT JOIN book_publisher as bp ON b.id = bp.bookID
LEFT JOIN publishers AS c ON bp.publishers_id = c.id
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' ";
Within the results there are some books that have 2 authors and now I have to find if
there is a second author in the results and then be able to echo the second author.
So far, I have not been able to figure out how to go about that.
Do I need to do another query to find the second author or can it somehow be incorporated into the original query? Or can it be done with a UNION ?
Please help.
... and we begin ...
$SQL = "SELECT b.*, c.id AS publisher_id, c.publisher, a.id AS author_id, a.first_name, a.last_name
FROM book AS b
INNER JOIN book_publisher as bp ON b.id = bp.bookID
INNER JOIN publishers AS c ON bp.publishers_id = c.id
INNER JOIN book_author AS ba ON b.id = ba.bookID
INNER JOIN author AS a ON ba.authID = a.id
WHERE b.id IN (
SELECT b.id
FROM book AS b
INNER JOIN book_author AS ba ON b.id = ba.bookID
INNER JOIN author AS a ON ba.authID = a.id
WHERE a.last_name LIKE '{$Auth}%'
)";
Ok, with those changes made, you will now see that your result set is going to have duplicate entries for each book.
Well, almost duplicate...
Basically what you need to realize is that you are talking about row counts compounding upon themselves...
I will try and explain...
if you had ten books with a total of 16 unique authors and 8 unique publishers, you would end up with a result set
(without any WHERE clause) that was 10 x 16 x 8 = 1280 rows.
now, say that you put a limit of "authors last name must start with and 'A'" on the select (like you have)
say that three of the authors match that and of those three, they had 4 books that they have written and those 4 books
were published by 2 unique publishers. You would end up with 3 x 4 x 2 = 24 rows in result set.
I would consider handling all the above as I have stated, but you will need to add some sorting of the data in PHP to
make sense of it all.
something like the following should do. (completely untested!!!) typed right in the client here...
$book_information = array();
if ( ( $results = mysql_query($SQL, $db) ) !== false ) {
while ( $row = mysql_fetch_assoc($results) ) {
$book_information[$row['id']]['title'] = $row['title'];
# NOTICE: this row is here so you can replace it with other information from the book table
$book_information[$row['id']]['...'] = $row['...'];
$book_information[$row['id']]['authors'][$row['author_id']] = array('first_name' => $row['first_name'],
'last_name' => $row['last_name']);
$book_information[$row['id']]['publishers'][$row['publisher_id']] = $row['publisher'];
}
}
Do a print_r($book_information) and you should get an understanding of what is happening.
Jim Lucas
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
