I must be doing something wrong. Can't figure it out even though I've
been searching the manuals & tutorials, it still does not work. Here is
the exact code that I have tried; the first version is commented out and
obviously does not work either (the spelling, the table names and column
names are correct):
$books = array();
/*$SQL = "SELECT * FROM book b
JOIN book_author c
ON b.id = c.bookID
JOIN author a ON a.id = c.authID
WHERE LEFT(a.last_name,1) = $Auth
ORDER BY $sort $dir
LIMIT $offset, $records_per_page"; */
$SQL = "SELECT * FROM book b
INNER JOIN book_author c
ON b.id = c.bookID
WHERE c.authID = (SELECT id FROM author a WHERE LEFT(a.last_name, 1
) = $Auth )
ORDER BY $sort $dir
LIMIT $offset, $records_per_page";
if ( ( $results = mysql_query($SQL, $db) ) !== false ) {
while ( $row = mysql_fetch_assoc($results) ) {
$books[$row['id']] = $row;
}
}
echo "auth = :", $Auth ; ---> returns "auth = :A" (my quotes)
var_dump($results); ---> returns "boolean false" (my quotes)
========
Now, this:
$SQL = "select *
from book b
inner join book_author c
on (b.id = c.bookID)
inner join author a on (a.id = c.authID)
where left(a.last_name, 1 ) = $Auth;
if ( ( $results = mysql_query($SQL, $db) ) !== false ) {
while ( $row = mysql_fetch_assoc($results) ) {
$books[$row['id']] = $row; ---> returns "Parse error:
syntax error, unexpected '[', expecting ']' in this line....
There seems to be something odd in the query... and yet, it all three
look right to me... could there be some inconsistency in the tables,
like skipped records or a record in a column that is non-existent in a
corresponding column?
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
>
>> );
>>
>
>
>
--
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
