Re: formulate nested select

PJ wrote:

Ian wrote:

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.

Hmmm.... That was it. I'm not clear on this... what was MySQL getting? if not a string... sorry for my ignorance...

You have to quote strings in sql, only numbers (and booleans) don't have quotes. ie $sql = ".. where left(a.last_name, 1) = '" . mysql_real_escape_string($Auth) . "'"; single quotes around the value and it's properly escaped. -- Postgresql & php tutorials http://www.designmagick.com/