Jim Lucas wrote:
> 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???
I decided on the outset that there would only be a max or 2 authors in
the author fields as there are few books authored by more than 2
writers. So I assign ordinal 1 for the primary author and 2 for the
second(ary). I thought that would be necessary for differentiating
between the two.
If there are 3 authors, then that is entered under sub_title. And often
these "authors" are not authors but editors for the books then are
really anthologies.
I had not thought of searching for these under Authors. I suppose that
could be done by another type of search.
>
>
> 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.
Will do.
>
>> 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
