Gentlemen,
First, let me thank you all for responding and offering suggestions. I
appreciate it and I am learning things.
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.
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
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?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
