PHP 5 / MySQL 5 subquery problem

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

Re: PHP 5 / MySQL 5 subquery problem

"Diogenes" wrote in message
news:EZPWg.119267$1T2.96496@pd7urf2no...
> 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

Try first to use single quotation marks instead of double.
Double quotations are used for variables with blank spaces in their names.
Try and reply, I'm searching for one similar sql I used recently

Re: PHP 5 / MySQL 5 subquery problem

"Diogenes" wrote in message
news:EZPWg.119267$1T2.96496@pd7urf2no...
> 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



Here's the 2nd option (for Oracle)
select o.*

from offer o, norm n

where n.TF=o.PHONE

and not exists (select 1 from norm n where n.TF=o.PHONE)

--Put indexes on joined vars

Re: PHP 5 / MySQL 5 subquery problem

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

Re: PHP 5 / MySQL 5 subquery problem

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

Re: PHP 5 / MySQL 5 subquery problem

KrunoG wrote:
>
>
> Try first to use single quotation marks instead of double.
> Double quotations are used for variables with blank spaces in their names.
> Try and reply, I'm searching for one similar sql I used recently
>
Well I tried the single quote, but had the same result.

Thanks for the suggestion.

Jim

Re: PHP 5 / MySQL 5 subquery problem

KrunoG wrote:

> Here's the 2nd option (for Oracle)
> select o.*
>
> from offer o, norm n
>
> where n.TF=o.PHONE
>
> and not exists (select 1 from norm n where n.TF=o.PHONE)
>
> --Put indexes on joined vars
>

I did try something similar to that. It becomes a
correlated SELECT statement yielding a cartesian product
which then needs a DISTINCT clause to get what I wanted.

I can't remember if I tried this with PHP. It did work from
the MySQL command line; but then so did my original query.

Do you think it might be a bug in PHP?

Cheers
Jim

Re: PHP 5 / MySQL 5 subquery problem

Captain Paralytic wrote:
> Diogenes wrote:
>>
>>SELECT pkey, Name FROM tracks WHERE artist="Rolling Stones"
>>AND pkey NOT IN (SELECT Song FROM requests WHERE client="jim")
>>
>
>
> It is almost always more efficient to use the LEFT JOIN alternative for
> this task
>

I'm a little naive on SQL. Could you redo the above SELECT
statement with your suggested alternative? Then I'll try it
in PHP.

Cheers
Jim

Re: PHP 5 / MySQL 5 subquery problem

Captain Paralytic wrote:
> Diogenes wrote:
>>
>>SELECT pkey, Name FROM tracks WHERE artist="Rolling Stones"
>>AND pkey NOT IN (SELECT Song FROM requests WHERE client="jim")
>>
>
>
> It is almost always more efficient to use the LEFT JOIN alternative for
> this task
>

I'm a little naive on SQL. Could you redo the above SELECT
statement with your suggested alternative? Then I'll try it
in PHP.

Cheers
Jim

Re: PHP 5 / MySQL 5 subquery problem

Diogenes wrote:
> Captain Paralytic wrote:
> > Diogenes wrote:
> >>
> >>SELECT pkey, Name FROM tracks WHERE artist="Rolling Stones"
> >>AND pkey NOT IN (SELECT Song FROM requests WHERE client="jim")
> >>
> >
> >
> > It is almost always more efficient to use the LEFT JOIN alternative for
> > this task
> >
>
> I'm a little naive on SQL. Could you redo the above SELECT
> statement with your suggested alternative? Then I'll try it
> in PHP.
>
> Cheers
> Jim

Without seeing the scheme and some sample data I can't be sure that
I've captured all the nuances of what you want, but try:

SELECT pkey, Name FROM tracks
LEFT JOIN requests ON tracks.pkey = requests.Song AND requests.client =
"jim"
WHERE tracks.artist="Rolling Stones" AND requests.Song IS NULL

Re: PHP 5 / MySQL 5 subquery problem

Diogenes wrote:
> Captain Paralytic wrote:
> > Diogenes wrote:
> >>
> >>SELECT pkey, Name FROM tracks WHERE artist="Rolling Stones"
> >>AND pkey NOT IN (SELECT Song FROM requests WHERE client="jim")
> >>
> >
> >
> > It is almost always more efficient to use the LEFT JOIN alternative for
> > this task
> >
>
> I'm a little naive on SQL. Could you redo the above SELECT
> statement with your suggested alternative? Then I'll try it
> in PHP.
>
> Cheers
> Jim

Without seeing the scheme and some sample data I can't be sure that
I've captured all the nuances of what you want, but try:

SELECT pkey, Name FROM tracks
LEFT JOIN requests ON tracks.pkey = requests.Song AND requests.client =
"jim"
WHERE tracks.artist="Rolling Stones" AND requests.Song IS NULL

Re: PHP 5 / MySQL 5 subquery problem

Kudos to the Captain! That works beautifully both on the
command line and in PHP!

Thank you! Thank you! Thank you!

Cheers
Jim

Captain Paralytic wrote:
> Diogenes wrote:
>
>>Captain Paralytic wrote:
>>
>>>Diogenes wrote:
>>>
>>>>SELECT pkey, Name FROM tracks WHERE artist="Rolling Stones"
>>>>AND pkey NOT IN (SELECT Song FROM requests WHERE client="jim")
>>>>
>>>
>>>
>>>It is almost always more efficient to use the LEFT JOIN alternative for
>>>this task
>>>
>>
>>I'm a little naive on SQL. Could you redo the above SELECT
>>statement with your suggested alternative? Then I'll try it
>>in PHP.
>>
>>Cheers
>>Jim
>
>
> Without seeing the scheme and some sample data I can't be sure that
> I've captured all the nuances of what you want, but try:
>
> SELECT pkey, Name FROM tracks
> LEFT JOIN requests ON tracks.pkey = requests.Song AND requests.client =
> "jim"
> WHERE tracks.artist="Rolling Stones" AND requests.Song IS NULL
>

Re: PHP 5 / MySQL 5 subquery problem

Kudos to the Captain! That works beautifully both on the
command line and in PHP!

Thank you! Thank you! Thank you!

Cheers
Jim

Captain Paralytic wrote:
> Diogenes wrote:
>
>>Captain Paralytic wrote:
>>
>>>Diogenes wrote:
>>>
>>>>SELECT pkey, Name FROM tracks WHERE artist="Rolling Stones"
>>>>AND pkey NOT IN (SELECT Song FROM requests WHERE client="jim")
>>>>
>>>
>>>
>>>It is almost always more efficient to use the LEFT JOIN alternative for
>>>this task
>>>
>>
>>I'm a little naive on SQL. Could you redo the above SELECT
>>statement with your suggested alternative? Then I'll try it
>>in PHP.
>>
>>Cheers
>>Jim
>
>
> Without seeing the scheme and some sample data I can't be sure that
> I've captured all the nuances of what you want, but try:
>
> SELECT pkey, Name FROM tracks
> LEFT JOIN requests ON tracks.pkey = requests.Song AND requests.client =
> "jim"
> WHERE tracks.artist="Rolling Stones" AND requests.Song IS NULL
>