OK, so I owe you a big one :-*
Your code works... now, I'm faced with a horrendous problem and that is
to make sense of it all :-)
What I seem to get from this exercise is that I don't need the ordinals
at all.
Uuuuh... that's interesting. I get it. So now, I can update my
bookInsert script to eliminate the ordinals and just use the bookID and
authID .... which means I can list as may authors as I like and not
worry about limiting the number.
Looks like I will have to rewrite a bit of code. And I think this may
simplify my other code for publisher and categories.
Thank you, (kow-tow)- thank you, (kow-tow)- thank you, (kow-tow).
Now the silly questions = often I am curious what effect the differences
in code will have on performance.
For instance, why are you using author.last_name LIKE '{$Auth}%' rather
than LEFT('$Auth') ? As I have mentioned, it is not important what
follows the A
I think I understand the AS book_id and AS auth_id - is it to avoid
confusion with the book.id and author.id?
Anyway, I am beginning to see more light - could it be called
enLIGHTenment? :-D
Jim Lucas wrote:
> PJ wrote:
>> Gentlemen,
>>
>> First, let me thank you all for responding and offering suggestions. I
>> appreciate it and I am learning things.
>
> How about telling us/me what it did or did not do for you?
>
>> However, it looks like my message is not getting across:
>> The problem is not to retrieve only the authors whose last names begin
>> with A:
>>
>> 1) which books have a second author?
>> 2) who is the second author ?
>> This is determined by table book_author column ordinal (which can be 1
>> or 2) - if there is only 1 author for a book then there is no ordinal 2
>> linked to book_author bookID and authID.
>
> This is the first that I have heard about this 'ordinal'. From your
> description above, I don't get what it does for you. The book_author
> table should, basically, connect the books table to the authors table
> by a simple two column combination of ids. So, I don't understand the
> significance of that field. Could you explain it further please?
>
>>
>> The structure of the db:
>>
>> book
>> id
>> title
>> sub_title
>> descr
>> comment
>> bk_cover
>> copyright
>> ISBN
>> language
>> sellers
>>
>> author
>> id
>> first_name
>> last_name
>> book_author
>> authID
>> bookID
>> ordinal
>>
>
> Thanks for the structure. From this I can show you exactly what I
> would do to create the code. Minus the publishers information of
> course since you didn't supply the layout for those tables.
>
> $SQL = "
> SELECT book.*
> FROM book
> INNER JOIN book_author ON (book.id = book_author.bookID)
> INNER JOIN author ON (book_author.authID = author.id)
> WHERE author.last_name LIKE '{$Auth}%'
> ";
>
> $books = array();
> $bookIDs = array();
> if ( ( $results = mysql_query($SQL, $db) ) !== false ) {
> while ( $row = mysql_fetch_assoc($results) ) {
> $books[$row['id']] = $row;
> $bookIDs[] = $row['id'];
> }
> }
>
> if ( count($bookIDs) > 0 ) {
>
> print_r($bookIDs);
> # above should result in a structure that shows an indexed array
> of book ids for their values.
>
> $SQL = "
> SELECT book.id AS book_id, author.id AS author_id,
> author.first_name, author.last_name
> FROM book
> INNER JOIN book_author ON (book.id = book_author.bookID)
> INNER JOIN author ON (book_author.authID = author.id)
> WHERE book.id IN ( " . join(',', $bookIDs) . " )
> ";
>
> $authors = array();
> if ( ( $results = mysql_query($SQL, $db) ) !== false ) {
> while ( $row = mysql_fetch_assoc($results) ) {
> $authors[$row['book_id']][$row['author_id']] = $row;
> }
> }
>
> print_r($authors);
> # above should result in a multidimensional array of
> books/authors/(author data)
> # Now, you should be able to have two nexted foreach loops.
> # I will briefly give you an example.
> foreach ( $books AS $bookID => $bookData ) {
>
> # Display book information
> echo '<h1>', $bookID, '</h1>';
> print_r($bookData);
>
> # Test to see if the book has any authors
> if ( isset($authors[$bookID]) ) {
>
> # Tell us how many authors we found
> echo '<ul>Found: ', count($author[$bookID]), ' authors';
>
($authors[$bookID]) in line above :-)
> # Loop through the authors
> foreach ( $authors[$bookID] AS $authorID => $authorData ) {
> # Display the
> echo "<li>{$authorData['last_name']},
> {$authorData['first_name']}</li>\n";
> }
> echo '</ul>';
> } else {
> echo 'No authors found';
> }
> echo '<hr />';
> }
> } else {
> echo 'No books match your search pattern...';
> }
>
> ?>
>
> Ok, this is the second time I have written a script for you... You
> owe me :)
>
> This should return to you a header that includes the book ID and then
> print all the data for the book. That will be followed by any and all
> authors for the book.
>
> If it doesn't work... well I don't know what to do then.
>
> But, try it!!! Report back to the list what it did for you or what it
> missed on.
>
> Your responses have been very vague as to what an example did or
> didn't do for you.
>
> Tell us everything that worked or didn't and we might be able to help
> you to tweak the code so it DOES do what your are looking to do.
>
> Hope it works!
>
> Jim
>> categories and publishers are not realy relevant here...
>>
>> The code I have:
>>
>> $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' ";
>>
>> (PLEASE LET ME KNOW IF THERE IS SOMETHING WRONG WITH THE CODE)
>> It gives me these results:
>>
>> *array*
>> 6 =>
>> *array*
>> 'id' => string '6' /(length=1)/
>> 'title' => string 'Nubia.' /(length=6)/
>> 'sub_title' => string 'Corridor to Africa' /(length=18)/
>> 'descr' => string '' /(length=0)/
>> 'comment' => string '' /(length=0)/
>> 'bk_cover' => string '' /(length=0)/
>> 'copyright' => string '1977' /(length=4)/
>> 'ISBN' => string '0691093709' /(length=10)/
>> 'language' => string 'en' /(length=2)/
>> 'sellers' => string '' /(length=0)/
>> 'publisher' => string 'Princeton University Press' /(length=26)/
>> 'first_name' => string 'William Yewdale' /(length=15)/
>> 'last_name' => string 'Adams' /(length=5)/
>>
>> This is the first of 17 books and it would be sufficient if I were not
>> so damned demanding.
>> I know that there are several books in the list that have 2 authors. At
>> least 1 whose last name begins with A is an ordinal=2 (which is not
>> important when it comes to displaying) and there are a couple where
>> there are 2nd authors whose names begin with other letters of the
>> alphabet. It is these that I am targeting.
>> My question is:
>>
>> How do I filter my query to weed out these renegades?
>>
>> Can it be done using a concat as Author1 and another concat as Author2
>> (in place of the first_name and last_name joins ?
>> Or do I do another query just for the outlaws?
>>
>>
>
>
--
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
