On 31 Mar 2009 at 9:08, PJ wrote:
> 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 )
Hi,
I think this should be '$Auth' as MySQL will be expecting a string.
> 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?
Looks like there is a double quote ( " ) missing from the end of the $SQL variable.
When building complex SQL queries for use in PHP I usually test them in the mysql
command line client or a gui such as HeidiSQL first to make sure I am getting the
expected results.
Also you could try printing the SQL statement to the web page to make sure it looks as
expected.
You can try checking for a MySQL error within PHP by using these functions:
mysql_errno()
mysql_error()
Regards
Ian
--
>
>
> 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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
