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...
>
>
>> 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
>
--
unheralded genius: "A clean desk is the sign of a dull mind. "
-------------------------------------------------------------
Phil Jourdan --- [email protected]
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
