Re: [PHP] difficult select problem
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
FROMbook 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:
Re: [PHP] difficult select problem
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 =
SELECTbook.*
FROMbook
INNER JOINbook_authorON (book.id = book_author.bookID)
INNER JOINauthorON (book_author.authID = author.id)
WHEREauthor.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
FROMbook
INNER JOINbook_authorON (book.id = book_author.bookID)
INNER JOINauthorON (book_author.authID = author.id)
WHEREbook.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 'ulFound: ', 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
Re: [PHP] difficult select problem
On Thu, Apr 9, 2009 at 1:39 PM, PJ af.gour...@videotron.ca wrote:
[snip]
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
The query optimizer can use an index on author.last_name to evaluate
WHERE author.last_name LIKE '{$Auth}%' . It simply seeks/scans the
index since the last_name values are ordered, and starts returning
values when it reaches the first row that begins with $Auth and stops
returning rows when it finds a value is greater than the pattern.
To evaluate WHERE LEFT('$Auth', LENGTH('$Auth')) = '$Auth' , the
server has to calculate a substring of last_name for every row in the
table and then compare those substrings to the value $Auth.
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] difficult select problem
Andrew Ballard wrote:
On Thu, Apr 9, 2009 at 1:39 PM, PJ af.gour...@videotron.ca wrote:
[snip]
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
The query optimizer can use an index on author.last_name to evaluate
WHERE author.last_name LIKE '{$Auth}%' . It simply seeks/scans the
index since the last_name values are ordered, and starts returning
values when it reaches the first row that begins with $Auth and stops
returning rows when it finds a value is greater than the pattern.
To evaluate WHERE LEFT('$Auth', LENGTH('$Auth')) = '$Auth' , the
server has to calculate a substring of last_name for every row in the
table and then compare those substrings to the value $Auth.
Andrew
Thanks Andrew. Just what I wanted to know. ;-)
--
unheralded genius: A clean desk is the sign of a dull mind.
-
Phil Jourdan --- p...@ptahhotep.com
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
Re: [PHP] difficult select problem
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
FROMbook 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
Re: [PHP] difficult select problem
2009/4/7 Bastien Koert phps...@gmail.com: On Tue, Apr 7, 2009 at 1:10 PM, Michael A. Peters mpet...@mac.com wrote: PJ wrote: Bob McConnell wrote: From: PJ 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: Actually, it appears you simply don't like the accurate answers you have been given. First, let me say that I am pretty fresh to all this. Second, I believe I can get the authors with several more queries as I have done for listings of all the books and by categories, but I am trying to limit the queries thinking that that will speed up the data retrieval. A friend of mine who manages many large scale websites with massive databases says that isn't always the case, especially if you don't have a dedicated SQL server with very fast disks and lots of memory. He's found that in many situations it is faster to do several sql queries and let php sort it out then to use a bunch of joins, subselects, etc. in order to reduce the number of sql queries. Has to do with how sql works on the filesystem, and the IO that can result from sql needing to do a more complex query, and what is fastest varies upon your setup. I think he said sub-selects are the worst because sql has to create a virtual table for the subselect and that can really slow the query down, but I might be mistaken about that. Thus unless he has a problem application that is way too slow on hardware he can't upgrade, he opts for what is easier code to read and maintain. Sometimes that's faster than hard to read queries anyway. Simple queries will almost alwasy be faster than a large join. Join query response times can be affected by the order of the join if the primary table is not the largest one amoung other factors. Well .. this really depends and - if speaking of large scale sites with a lot of traffic - you must recognize that you just move the payload to another server. in that case this would be the webserver. I give the example of Apache: one process takes up as much RAM as the biggest page it serves. so if a few pageviews require the application to parse a lot of data, all apache processes will use a large amount of ram (I've seen systems with 120mb per process!). High traffic requires a lot of processes and when as your webserver starts to swap because the RAM is filled up, you're in deep shit. And you'd wish to have a normalized DB layout and effective queries. Setting up a DB-Slave might be better than buying a lot of webservers. ;) Also, the DB server is build for what you're trying to accomplish, the webserver running php and serving pages.. well, maybe the server would spend his CPU time in another way if he'd had the chance... whatever. ;) byebye Another thing to consider here is that the data is relatively static. Perhaps you could build a xml representation on the first display of the book and pay the piper once in building the data. From there build an xml snippet ( and store it in the database perhaps in the main books table. Then on future calls to that book to display the same data, request the xml and use that to display all the data. That way you get the search functionality you are looking for and a one time hit to build that data into one common format. That would make the future reads of that data much quicker. -- Bastien Cat, the other other white meat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] difficult select problem
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
FROMbook 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???
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.
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
Re: [PHP] difficult select problem
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.*
FROMbook
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
FROMbook
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 'ulFound: ', 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 ''
Re: [PHP] difficult select problem
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
FROMbook 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}%'
);
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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] difficult select problem
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
Re: [PHP] difficult select problem
PJ, On Tue, Apr 7, 2009 at 11:37 AM, PJ af.gour...@videotron.ca wrote: $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) Let me try to clarify what I'm saying about your query. The above query will ONLY return authors who match the WHERE condition, thus have last name starting with A. This query will never find the second author for those books, unless that author's last name also starts with an A. That's why you first need to get a list of the book IDs that match your WHERE condition, and then grab the authors related to those book IDs (whether through two queries or using a sub-query). You've been shown several different ways of doing this through the responses provided. Do the provided queries not work for your test data? - Lex
RE: [PHP] difficult select problem
From: PJ 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: Actually, it appears you simply don't like the accurate answers you have been given. 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. There is no way to do that in a single select. You need to have at least two and possibly three queries to answer your question. First you get a list of authors where their name begins with 'A'. Then you use that result to select a list of all books with more than one author. Then you can use that result to select all authors for them. Everyone has told you this requires processing beyond what SQL can provide. Why is that a problem? Bob McConnell -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] difficult select problem
Bob McConnell wrote: From: PJ 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: Actually, it appears you simply don't like the accurate answers you have been given. First, let me say that I am pretty fresh to all this. Second, I believe I can get the authors with several more queries as I have done for listings of all the books and by categories, but I am trying to limit the queries thinking that that will speed up the data retrieval. Third, I have tried the answers proposed and have not been able to make them work up to now. Will continue... (it's a bit difficult at the moment as I do not have my entire head about me - nasty cold) Fourth, I don't see any reference to the book_author.ordinal which seems important to me to determine the placement of the authors. 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. There is no way to do that in a single select. You need to have at least two and possibly three queries to answer your question. First you get a list of authors where their name begins with 'A'. Then you use that result to select a list of all books with more than one author. Then you can use that result to select all authors for them. I am a bit confused as to how to use the result in further queries. And I don't understand what the difference is in using: $SQL = SELECT b.id FROM book AS b 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' ; instead of: $SQL = SELECT b.id FROM book b, book_author ba, author a WHERE b.id = ba.bookID a.id = ba.authID LEFT(a.last_name, 1) = '$Auth' ; It seems simpler without the JOINs; but is there an advantage or is that merely a personanl choice of several possibilities? Everyone has told you this requires processing beyond what SQL can provide. Why is that a problem? It's not a problem. I'm just trying learn what can and cannot be done. For instance, I could just put new columns in the db for author1 and author2. But I suspect it might not be as efficient with a great many books (like thousands). Bob McConnell -- unheralded genius: A clean desk is the sign of a dull mind. - Phil Jourdan --- p...@ptahhotep.com 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
Re: [PHP] difficult select problem
PJ wrote: Bob McConnell wrote: From: PJ 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: Actually, it appears you simply don't like the accurate answers you have been given. First, let me say that I am pretty fresh to all this. Second, I believe I can get the authors with several more queries as I have done for listings of all the books and by categories, but I am trying to limit the queries thinking that that will speed up the data retrieval. A friend of mine who manages many large scale websites with massive databases says that isn't always the case, especially if you don't have a dedicated SQL server with very fast disks and lots of memory. He's found that in many situations it is faster to do several sql queries and let php sort it out then to use a bunch of joins, subselects, etc. in order to reduce the number of sql queries. Has to do with how sql works on the filesystem, and the IO that can result from sql needing to do a more complex query, and what is fastest varies upon your setup. I think he said sub-selects are the worst because sql has to create a virtual table for the subselect and that can really slow the query down, but I might be mistaken about that. Thus unless he has a problem application that is way too slow on hardware he can't upgrade, he opts for what is easier code to read and maintain. Sometimes that's faster than hard to read queries anyway. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] difficult select problem
On Tue, Apr 7, 2009 at 1:10 PM, Michael A. Peters mpet...@mac.com wrote: PJ wrote: Bob McConnell wrote: From: PJ 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: Actually, it appears you simply don't like the accurate answers you have been given. First, let me say that I am pretty fresh to all this. Second, I believe I can get the authors with several more queries as I have done for listings of all the books and by categories, but I am trying to limit the queries thinking that that will speed up the data retrieval. A friend of mine who manages many large scale websites with massive databases says that isn't always the case, especially if you don't have a dedicated SQL server with very fast disks and lots of memory. He's found that in many situations it is faster to do several sql queries and let php sort it out then to use a bunch of joins, subselects, etc. in order to reduce the number of sql queries. Has to do with how sql works on the filesystem, and the IO that can result from sql needing to do a more complex query, and what is fastest varies upon your setup. I think he said sub-selects are the worst because sql has to create a virtual table for the subselect and that can really slow the query down, but I might be mistaken about that. Thus unless he has a problem application that is way too slow on hardware he can't upgrade, he opts for what is easier code to read and maintain. Sometimes that's faster than hard to read queries anyway. Simple queries will almost alwasy be faster than a large join. Join query response times can be affected by the order of the join if the primary table is not the largest one amoung other factors. Another thing to consider here is that the data is relatively static. Perhaps you could build a xml representation on the first display of the book and pay the piper once in building the data. From there build an xml snippet ( and store it in the database perhaps in the main books table. Then on future calls to that book to display the same data, request the xml and use that to display all the data. That way you get the search functionality you are looking for and a one time hit to build that data into one common format. That would make the future reads of that data much quicker. -- Bastien Cat, the other other white meat
Re: [PHP] difficult select problem
PJ wrote: 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. You've been given a few solutions for this and your only reply has been doesn't do what I want. Did you try Jim's suggestion? What didn't it do that you wanted it to? -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] difficult select problem
On Mon, Apr 6, 2009 at 12:32 PM, PJ af.gour...@videotron.ca 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. -- unheralded genius: A clean desk is the sign of a dull mind. - Phil Jourdan --- p...@ptahhotep.com 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 $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 last_name like '$Auth%' ; The above assumes that $Auth has a value of A and should pull all the authors whose last name starts with the letter A -- Bastien Cat, the other other white meat
Re: [PHP] difficult select problem
Hi Bastien, Thanks for the suggestion. But my code already does that. I think it is as difficult to formulate the problem in plain English as to figure out how to solve it: The problem is: Within the results some books have 2 authors: 1) is there a second author for any of the books in the results 2) find and echo the second author. So far, I have not been able to figure out how to go about that without a lot of queries. Bastien Koert wrote: On Mon, Apr 6, 2009 at 12:32 PM, PJ af.gour...@videotron.ca mailto:af.gour...@videotron.ca 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 http://b.id = bp.bookID LEFT JOIN publishers AS c ON bp.publishers_id = c.id http://c.id LEFT JOIN book_author AS ba ON b.id http://b.id = ba.bookID LEFT JOIN author AS a ON ba.authID = a.id http://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. -- unheralded genius: A clean desk is the sign of a dull mind. - Phil Jourdan --- p...@ptahhotep.com mailto:p...@ptahhotep.com 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 $SQL = SELECT b.*, c.publisher, a.first_name, a.last_name FROM book AS b LEFT JOIN book_publisher as bp ON b.id http://b.id/ = bp.bookID LEFT JOIN publishers AS c ON bp.publishers_id = c.id http://c.id/ LEFT JOIN book_author AS ba ON b.id http://b.id/ = ba.bookID LEFT JOIN author AS a ON ba.authID = a.id http://a.id/ WHERE last_name like '$Auth%' ; The above assumes that $Auth has a value of A and should pull all the authors whose last name starts with the letter A -- Bastien Cat, the other other white meat -- unheralded genius: A clean desk is the sign of a dull mind. - Phil Jourdan --- p...@ptahhotep.com 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
Re: [PHP] difficult select problem
PJ,
On Mon, Apr 6, 2009 at 12:32 PM, PJ af.gour...@videotron.ca wrote:
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.
First off, are you trying to search for book TITLES that start with A or
book AUTHORS that start with A?
If it's authors, it might make more sense to do a select that retrieves the
book ids that have an author whose last name starts with an A and once you
have those IDs, do a second query that will retrieve all authors for those
books. So something like below (UNTESTED):
//Find out which books are written by an author with a last name starting
with $Auth
$SQL = SELECT b.id FROM book AS b
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' ;
Then do the following select statement with a foreach loop that retrieves
the book IDs above
$query = SELECT b.id, GROUP_CONCAT(CONCAT(' ', a.first_name, a.last_name)
ORDER BY a.last_name)
FROM book AS b
LEFT JOIN book_author AS ba ON b.id = ba.bookID
LEFT JOIN author AS a ON ba.authID = a.id
WHERE b.id = $value
GROUP BY b.id ; // $value is the b.id for each iteration of the foreach
loop
More information about the GROUP_CONCAT() function can be found in the MySQL
reference (
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
)
- Lex
Re: [PHP] difficult select problem
Hi Lex,
Thanks for responding.
Indeed, it is a very difficult problem - for me, at least; I think it is
as difficult to formulate the problem in plain English as to figure out
how to solve it.
My code already has selected the books whose authors last names start
with A as well as the authors themselves.
Within the results some books have 2 authors.
The problem is:
1) to find if there is second author for any of the books in the results
2) find and echo the second author.
So far, I have not been able to figure out how to go about that without
a lot of queries.
Some observations inserted below:
Lex Braun wrote:
PJ,
On Mon, Apr 6, 2009 at 12:32 PM, PJ af.gour...@videotron.ca
mailto:af.gour...@videotron.ca wrote:
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.
First off, are you trying to search for book TITLES that start with
A or book AUTHORS that start with A?
If it's authors, it might make more sense to do a select that
retrieves the book ids that have an author whose last name starts with
an A and once you have those IDs, do a second query that will
retrieve all authors for those books. So something like below (UNTESTED):
//Find out which books are written by an author with a last name
starting with $Auth
$SQL = SELECT b.id http://b.id FROM book AS b
LEFT JOIN book_author AS ba ON b.id http://b.id = ba.bookID
LEFT JOIN author AS a ON ba.authID = a.id http://a.id
WHERE LEFT(last_name, 1) = '$Auth' ;
Then do the following select statement with a foreach loop that
retrieves the book IDs above
$query = SELECT b.id http://b.id, GROUP_CONCAT(CONCAT(' ',
a.first_name, a.last_name) ORDER BY a.last_name)
I believe this should be (CONCAT_WS(' ', a.first_name, a.last_name) or
(CONCAT(a.first_name,' ', a.last_name)
Problem here is that I need to use the ba.ordinal somewhere in the cod
to distinguish between the authors - if ba.ordinal is 1, the author is
listed as the first author and if there is no ba.ordinal = 2 for the
ba.bookID, then there is only 1 author. I have not included this in my
query because it only returns the ordinal number for the author which
does not help me at all. I need to know who the second author is.
In effect, it would be great if I could get 2 joins on the same table
(author) for
1. (CONCAT(first_name, ' ', last_name) AS Author WHERE ab.ordinal = 1
LEFT(last_name, 1) = $Auth
2. (CONCAT(first_name, ' ', last_name) AS Author1 WHERE ab.ordinal = 2
Of course, my example does not work; it is meant to give an idea of what
I'm looking for.
There must be a simple solution to this... but how simple is it to find
it :-)
Phil
FROM book AS b
LEFT JOIN book_author AS ba ON b.id http://b.id = ba.bookID
LEFT JOIN author AS a ON ba.authID = a.id http://a.id
WHERE b.id http://b.id = $value
GROUP BY b.id http://b.id ; // $value is the b.id http://b.id
for each iteration of the foreach loop
More information about the GROUP_CONCAT() function can be found in the
MySQL reference
(http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat)
- Lex
--
unheralded genius: A clean desk is the sign of a dull mind.
-
Phil Jourdan --- p...@ptahhotep.com
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
Re: [PHP] difficult select problem
On Mon, Apr 6, 2009 at 2:05 PM, PJ af.gour...@videotron.ca wrote:
My code already has selected the books whose authors last names start
with A as well as the authors themselves.
Within the results some books have 2 authors.
If you already have the book IDs where this happens, then for each bookID
(probably a foreach loop in your code) simply do a query at that point that
will obtain ALL authors for that bookID -- don't include the condition that
last name start with an A in this query.
$query = SELECT b.id http://b.id, GROUP_CONCAT(CONCAT(' ',
a.first_name, a.last_name) ORDER BY a.last_name)
I believe this should be (CONCAT_WS(' ', a.first_name, a.last_name) or
(CONCAT(a.first_name,' ', a.last_name)
Sorry, yes that should be
GROUP_CONCAT( CONCAT(' ', a.first_name, a.last_name) ORDER BY a.last_name)
so that it concatenates each author as FirstName LastName and then groups
all authors for a given book separated by commas, so bookID=1 would have
something like Author1FirstName LastNameAuthor1, FirstNameAuthor2
LastNameAuthor2
Problem here is that I need to use the ba.ordinal somewhere in the cod
to distinguish between the authors - if ba.ordinal is 1, the author is
listed as the first author and if there is no ba.ordinal = 2 for the
ba.bookID, then there is only 1 author. I have not included this in my
query because it only returns the ordinal number for the author which
does not help me at all. I need to know who the second author is.
In effect, it would be great if I could get 2 joins on the same table
(author) for
1. (CONCAT(first_name, ' ', last_name) AS Author WHERE ab.ordinal = 1
LEFT(last_name, 1) = $Auth
2. (CONCAT(first_name, ' ', last_name) AS Author1 WHERE ab.ordinal = 2
The problem you'd run into with #1 above, is you'd only get authors whose
last name starts with A if they're the primary author. Is that the desired
functionality you're looking for?
- Lex
Re: [PHP] difficult select problem
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 ? Union won't help because you don't know the results from the first query to put into the union. You could do it in one query but whether it's worth it is up to you to decide test. I've removed the book_publisher stuff to make it easier to understand. Step 1: get the books the author has written. You don't need to join all of the tables here because you only want the bookid's. select bookid from book_author ba inner join authors a on (ba.authorid=a.id) where left (last_name, 1) = 'A' Step 2: Get all authors for those books: select ba.* from book_author ba inner join authors a on (ba.authorid=a.id) where ba.bookid in ( select bookid from book_author ba inner join authors a on (ba.authorid=a.id) where left (last_name, 1) = 'A' ); They should be inner joins, not left joins. If you need to join to other tables (book_publishers etc) don't do it in the inner query, do it in the outer (select ba.* stuff). -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
