haliphax wrote:
> On Tue, Mar 31, 2009 at 5:15 PM, PJ <af.gour...@videotron.ca> wrote:
>> kyle.smith wrote:
>>> What about using "IN", something like:
>>> SELECT * FROM book WHERE id IN (SELECT bookID FROM book_authors WHERE
>>> authID IN (SELECT author.id FROM authors WHERE last_name LIKE
>>> "$Auth%"));
>>> You could use LEFT instead of LIKE, too.
>> Well, I learned something here... but that also revealed another,
>> hidden, problem which I had not considered - the order by clause which I
>> had stupidly not included in my example:
>> $SQL = "SELECT * FROM book
>> Â  Â  Â  Â WHERE id IN (SELECT bookID
>> Â  Â  Â  Â FROM book_author WHERE authID IN (SELECT author.id
>> Â  Â  Â  Â FROM author WHERE LEFT(last_name, 1 ) = '$Auth'))
>> Â  Â  Â  Â ORDER BY $sort $dir
>> Â  Â  Â  Â LIMIT $offset, $records_per_page ";
>> I now realize that to keep things as simple as possible in the rest of
>> the code, I must join 1 column to the query result and that is last_name
>> from the author table.
>> the spelling, column and table names are spelled correctly. Without the
>> ORDER BY I get the right results. I'll try to JOIN the author table ???
>> But I see that I may be trying to do too much - I thought of showing how
>> many books were listed under each letter of the alphabet but I don't see
>> how it can be done in any simiple way as it would mean that I would have
>> to do the select once with the ORDER BY and a second time without it
>> just to get the number of listing. If there are a lot of books, like
>> thousands, it might slow down things.
>> I suppose I could live with ORDER BY "title" as that does not require
>> another effort.
>> Any thoughts or suggestions?
>>> -----Original Message-----
>>> From: PJ [mailto:af.gour...@videotron.ca]
>>> Sent: Tuesday, March 31, 2009 2:06 PM
>>> To: Jim Lucas
>>> Cc: Chris; php-general@lists.php.net
>>> Subject: Re: [PHP] formulate nested select
>>> Gentlemen & all others,
>>> The problem was partly fixed with ' ' around $Auth... but...
>>> somehow, I see that the results do not work with the rest of the script.
>>> The results give a an array within an array - so this mucks up all the
>>> rest which is set up to deal with only the book table. The count() is
>>> off as it shows the results_per_page setting and the pagination is off -
>>> it shows 10 books but only displays 5 entries (from 5 arrays which, I
>>> suppose is the reason for the coun() showing 10. The first page shows 5,
>>> but the second indicates 7 books but displays only 6...
>>> Now, I suppose that there are 2 ways to fix things:
>>> 1. Redo the rest of the script (a royal pain, I suspect) or 2. SELECT
>>> only the books that are attributed to the targeted authors - which is
>>> what I wanted to do in the first place. Something like:
>>> $SQL = "SELECT * FROM book b
>>> WHERE b.id = (SELECT book_author.bookID WHERE book_author.authID
>>> = (SELECT author.id WHERE LEFT(author.last_name, 1 ) = '$Auth')";
>>> I want to avoid joins as that seems to screw up the rest of the code
>>> which is in an include page that needs to be repeated as long as there
>>> are letters in the alphabet.
>>> I'll try to figure something out, but as somebody not too optimistic
>>> once said: "it sure don't look too good" (American, I believe...) :-)
>>>> Jim Lucas wrote:
>>>>> Chris wrote:
>>>>>> PJ wrote:
>>>>>>> I cannot find anything on google or the manuals/tutorials that
>>>>>>> gives some kin of clear explanation of how to so nested selects
>>>>>>> with where or whatever.
>>>>>>> I have three tables: books, authors and book-authors.
>>>>>>> I need to retrieve only those books whose author's names begin with
>>> A.
>>>>>>> I have tried several maniipulations of where and select with select
>>>>>>> subqueries and I cannot get results from the queries.
>>>>>>> For example
>>>>>>> "SELECT * FROM book b, book_authors c (SELECT id FROM author WHERE
>>>>>>> LEFT(author.last_name = $Auth )) as a WHERE a.id = c.authID && b.id
>>>>>>> = c.bookID ....<snip>
>>>>>> Not really a php question :P
>>>>>> You don't need a subquery for this. You can join all of the tables
>>>>>> together and just use the where clause to cut down your results, but
>>>>>> I'll give an example of both.
>>>>>> select *
>>>>>> from
>>>>>> books b inner join book_authors c on (b.id=c.bookId) inner join
>>>>>> authors a on (a.id=c.authorId) where left(a.last_name = 'A');
>>>>> correct me if I'm wrong, but did you use the left() function
>>>>> in-correctly?
>>>>> The documentation shows a different way to use it then you describe.
>>>>> Something more like the following:
>>>>> WHERE
>>>>> LEFT(a.last_name, 1) = 'A';
>>>>> But that would be case-sensitive...
>>>>> So, something like this would work better IMHO
>>>>> WHERE
>>>>> UPPER(LEFT(a.last_name, 1)) = 'A';
>>>>> or
>>>>> WHERE
>>>>> a.last_name ILIKE 'A%';
>>>>> would do the trick
>>>>>> or
>>>>>> select *
>>>>>> from
>>>>>> books b inner join book_authors c on (b.id=c.bookId) where
>>>>>> c.authorId in ( select id from authors where left(last_name='A')
>>>>> Again...
>>>>> SELECT id FROM authors WHERE LEFT(last_name, 1) = 'A')
>>>>> but yet again, case-sensitive...
>>>>> SELECT id FROM authors WHERE UPPER(LEFT(last_name, 1)) = 'A') or
>>>>> SELECT id FROM authors WHERE last_name ILIKE 'A%'
>>>>> would do the trick
>>>>>> );
>>>> Thank you for the suggestions, gentlemen.
>>>> As to the case sensitivity, since the authors' names must be written
>>>> with the first letter in uppercase, even "Anonymous" or "Unknown" I
>>>> assume I don't need to specify uppercase. Or does it really make a
>>>> difference? Glad to learn of the option, though.
>>>> I'm just starting on the listing of the books by author and just
>>>> realized that the sorting should be by author (last name). Can I
>>>> foresee a problem in that since the last_name is in associative tables
>>>> and not in the book table? Or does the JOIN incorporate the last_name
>>>> in the results?
> Please refrain from top-posting. It makes your threads MUCH harder to
> follow.
Sorry, once I realize I've got another problem my little peanut figures
it's related to the current problem. But, OK, I understand and will do
my best.

> If you're now wanting to filter conditionally and on multiple fields,
> I would suggest doing that after the fact in PHP. Grab the data you
> need with your SQL statement and then use PHP to manipulate the
> results to your liking based on user preference/input. Don't
> forget--you're working with another language besides SQL... you don't
> have to do EVERYTHING on the database. ;)
Could you clarify, please? Do you mean do the query and then manipulate
the results for display on a web page, for instance?
I have quite a few queries to display all the books and I suspect that
what you are suggesting is that I do one query joining all the stuff I
need and then manhandle the the results. :-\

unheralded genius: "A clean desk is the sign of a dull mind. "
Phil Jourdan --- p...@ptahhotep.com

PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to