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';
# 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?
--
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
