PJ wrote:
Hi Jim,
Sorry I could not gat back to you on your suggestion. I've been under
the weather for a couple of days but am almost over it.
Your suggestion does not work... yet.
I'll insert comments & questions below...
Jim Lucas wrote:
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}%'
)";
A written, the code does not produce a result.
Questions:
1. why c.id AS publisher_id and a.id AS author_id? I do not see a
reference to these in the code
2. why "a.last_name LIKE '{$Auth}%'? What does that change from
LEFT(last_name, 1) = '$Auth'? the LEFT clause will give me all authors
whose names begin with A; I don't care what the rest of the string may
be - the authors' last names always starts with a capital letter.
3. What were you expecting to get as a result from the code? It looks to
me like the results will only give all authors whose names begin with A.
And that I am already getting with the code as I have written. Sorry for
my ignorance, but that is why I am asking for help.
4. So far, no one has used the book_author.ordinal to select the author
; perhaps I have not been clear on that as that is what I use to
determine if there are more than 1 author. Ordinal 1 is for all authors
who are unique for a book; ordinal 2 is for authors who are the second
author for a book.
Thanks for explaining the ordinal significance. Does it really make a difference if the ordinal is
1 or 2? Does 1 indicate the primary author and the 2 indicate supporting authors???
If a book has three authors and all three had an ordinal of 2, would it make any difference then if
one was 1 and the remaining two were marked 2?
If it makes no difference, the the column is pointless.
If it does make a difference (identifying one as 1 and the remaining as 2), then keep it and we will
deal with that later.
But, all this can be done without that column.
Read my other email that I about to send, responding to a different part of this thread.
Now, if my code is not too messy or cumbersome (I refer to your e-mail
of March 15) then what I really need is a way to save the book.id from
my query to a $tring and then use that to select the book in another
query that will give me the second author. I am wondering how that is
done; I have already used this in retrieving arrays but the id is
changed in a simple $bk_id = $result - damn, I forget where I found that
in the manuals. I used it in another situation like this:
$catvar = array_values($category[$categoryID]);
$cat = $catvar[1];
$catvar was ids like 9, 6, 17, etc. and assigning it to $cat ranged them
as 1, 2, 3, 4, etc. (which is what I want to avoid)
Then I should be able to do a
"SELECT CONCAT_WS(" ", first_name, last_name) as Author2
FROM author a, book b, book_author ba
WHERE $bk_id = ba.bookID && ba.authID = a.id"
or
"SELECT CONCAT(first_name, " ", last_name, book_author.ordinal) as Author2
FROM author a, book b, book_author ba
WHERE $bk_id = ba.bookID && ba.authID = a.id"
I probably have something wrong in the code; I haven't tried it as I am
not sure how to retrieve the $bk_id.
And the CONCAT with the ordinal, I can filter the 1 or the 2 when echoing.
I must be really long-winded but this is a long and complicated exercise
for my little brain. :'(
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
--
Jim Lucas
"Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them."
Twelfth Night, Act II, Scene V
by William Shakespeare
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
