Posted by Captain Paralytic on 10/11/06 14:00

Diogenes wrote:

> Kind readers,
>
> I am currently developing an app with PHP 5 & MySql 5.
>
> The following select statement works as expected from
> the MySql command line but produces an error when run from PHP.
>
> SELECT pkey, Name FROM tracks WHERE artist="Rolling Stones"
> AND pkey NOT IN (SELECT Song FROM requests WHERE client="jim")
>
> The error produced by PHP is
>
> "Query failed: You have an error in your SQL syntax; check
> the manual that corresponds to your MySQL server version for
> the right syntax to use near ')' at line 1"
>
> I've done a workaround by substituting the pkey values in
> the parentheses in place of the subquery SELECT statement,
> but I can't figure out why the above query does not work in
> PHP but does in MySQL.
>
> The versions of each app read as follows ...
>
> >mysql -V
> mysql Ver 14.12 Distrib 5.0.22, for Win32 (ia32)
>
> >php -v
> PHP 5.2.0-dev (cli) (built: Jul 19 2006 16:20:24)
> Copyright (c) 1997-2006 The PHP Group
> Zend Engine v2.2.0, Copyright (c) 1998-2006 Zend Technologies
>
> Any insight would be much appreciated.
>
> Cheers
> Jim

It is almost always more efficient to use the LEFT JOIN alternative for
this task

[Back to original message]