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.
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
>
--
unheralded genius: "A clean desk is the sign of a dull mind. "
-------------------------------------------------------------
Phil Jourdan --- pj@xxxxxxxxxxxxx
http://www.ptahhotep.com
http://www.chiccantine.com/andypantry.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
