Can't use GROUP BY from PHP

[Lorenzo Thurman]

I'm connecting to an access database and I would like the query to
return results grouped on a particular column. When I try the following
query, I get this error back from PHP:
DB Error: syntax Error

"SELECT * FROM HardwareInstall ationsSummary WHERE LocationID = $loc_ID
GROUP BY Manufacturer"

Iposted this question on a PHP list and someone there suggested that SQl
does not allow this to happen unless its part of an aggregate statement.
Is this true? Is there a way around this?
TIA

[salad]

Lorenzo Thurman wrote:

I'm connecting to an access database and I would like the query to
return results grouped on a particular column. When I try the following
query, I get this error back from PHP:
DB Error: syntax Error

"SELECT * FROM HardwareInstall ationsSummary WHERE LocationID = $loc_ID
GROUP BY Manufacturer"

Iposted this question on a PHP list and someone there suggested that SQl
does not allow this to happen unless its part of an aggregate statement.
Is this true? Is there a way around this?
TIA

It's possibe the $loc_id is not being parsed as its value. Remember,
strings are surrounded by quotes, dates by #, and everything else by
nothing. Here's how I might write your statement in Access

"SELECT * FROM HardwareInstall ationsSummary WHERE LocationID = '" & _
loc_ID & "' GROUP BY Manufacturer"

or

"SELECT * FROM HardwareInstall ationsSummary WHERE LocationID = """ &
loc_ID & """ GROUP BY Manufacturer"

This assumes loc_id is a string.

[Lorenzo Thurman]

In article <PM************ ****@newsread1. news.pas.earthl ink.net>,
salad <oi*@vinegar.co m> wrote:

Lorenzo Thurman wrote:

I'm connecting to an access database and I would like the query to
return results grouped on a particular column. When I try the following
query, I get this error back from PHP:
DB Error: syntax Error

"SELECT * FROM HardwareInstall ationsSummary WHERE LocationID = $loc_ID
GROUP BY Manufacturer"

Iposted this question on a PHP list and someone there suggested that SQl
does not allow this to happen unless its part of an aggregate statement.
Is this true? Is there a way around this?
TIA

It's possibe the $loc_id is not being parsed as its value. Remember,
strings are surrounded by quotes, dates by #, and everything else by
nothing. Here's how I might write your statement in Access

"SELECT * FROM HardwareInstall ationsSummary WHERE LocationID = '" & _
loc_ID & "' GROUP BY Manufacturer"

or

"SELECT * FROM HardwareInstall ationsSummary WHERE LocationID = """ &
loc_ID & """ GROUP BY Manufacturer"

This assumes loc_id is a string.

Thanks for the reply. Here's what I get when I use getDebugInfo():
SELECT HardwareInstall ations.Manufact urer, CountOfModel, Model, Platform
FROM HardwareInstall ations WHERE LocationID = 7 GROUP BY
HardwareInstall ations.Manufact urer [nativecode=3700 0 [Microsoft][ODBC
Microsoft Access Driver] You tried to execute a query that does not
include the specified expression 'CountOfModel' as part of an aggregate
function.]

LocationID being parsed correctly. It just cant get around this
aggregate thingy.

[Rick Wannall]

You can't use GROUP BY with SELECT *.

Select specific columns and include all of them as GROUP BY, SUM(), COUNT(),
or something.

[David W. Fenton]

salad <oi*@vinegar.co m> wrote in
news:PM******** ********@newsre ad1.news.pas.ea rthlink.net:

Lorenzo Thurman wrote:

I'm connecting to an access database and I would like the query
to return results grouped on a particular column. When I try the
following query, I get this error back from PHP:
DB Error: syntax Error

"SELECT * FROM HardwareInstall ationsSummary WHERE LocationID =
$loc_ID GROUP BY Manufacturer"

Iposted this question on a PHP list and someone there suggested
that SQl does not allow this to happen unless its part of an
aggregate statement. Is this true? Is there a way around this?
TIA

It's possibe the $loc_id is not being parsed as its value.

You obvioiusly know nothing about PHP.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[salad]

David W. Fenton wrote:

salad <oi*@vinegar.co m> wrote in
news:PM******** ********@newsre ad1.news.pas.ea rthlink.net:

Lorenzo Thurman wrote:

I'm connecting to an access database and I would like the query
to return results grouped on a particular column. When I try the
following query, I get this error back from PHP:
DB Error: syntax Error

"SELECT * FROM HardwareInstall ationsSummary WHERE LocationID =
$loc_ID GROUP BY Manufacturer"

Iposted this question on a PHP list and someone there suggested
that SQl does not allow this to happen unless its part of an
aggregate statement. Is this true? Is there a way around this?
TIA

It's possibe the $loc_id is not being parsed as its value.

You obvioiusly know nothing about PHP.

That is correct. Neither do the people on the PHP list. It appears
Rick Wannal spotted the error.

[Tom van Stiphout]

On Tue, 06 Jun 2006 19:13:21 GMT, "Rick Wannall"
<wa*****@notado main.de> wrote:

Rick is right. First work out the kinks in the Access query designer,
then implement in your code. The query designer would have pointed out
the error of your ways :-)

-Tom.

You can't use GROUP BY with SELECT *.

Select specific columns and include all of them as GROUP BY, SUM(), COUNT(),
or something.

[David W. Fenton]

salad <oi*@vinegar.co m> wrote in
news:oh******** *******@newsrea d2.news.pas.ear thlink.net:

David W. Fenton wrote:

salad <oi*@vinegar.co m> wrote in
news:PM******** ********@newsre ad1.news.pas.ea rthlink.net:

Lorenzo Thurman wrote:

I'm connecting to an access database and I would like the query
to return results grouped on a particular column. When I try the
following query, I get this error back from PHP:
DB Error: syntax Error

"SELECT * FROM HardwareInstall ationsSummary WHERE LocationID =
$loc_ID GROUP BY Manufacturer"

Iposted this question on a PHP list and someone there suggested
that SQl does not allow this to happen unless its part of an
aggregate statement. Is this true? Is there a way around this?
TIA

It's possibe the $loc_id is not being parsed as its value.

You obvioiusly know nothing about PHP.

That is correct. Neither do the people on the PHP list. It
appears Rick Wannal spotted the error.

No, the error was a Jet SQL error, not a PHP error.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[salad]

David W. Fenton wrote:

salad <oi*@vinegar.co m> wrote in
news:oh******** *******@newsrea d2.news.pas.ear thlink.net:

David W. Fenton wrote:

salad <oi*@vinegar.co m> wrote in
news:PM***** ***********@new sread1.news.pas .earthlink.net:
Lorenzo Thurman wrote:
>I'm connecting to an access database and I would like the query
>to return results grouped on a particular column. When I try the
>followin g query, I get this error back from PHP:
>DB Error: syntax Error
>
>"SELECT * FROM HardwareInstall ationsSummary WHERE LocationID =
>$loc_ID GROUP BY Manufacturer"
>
>Iposted this question on a PHP list and someone there suggested
>that SQl does not allow this to happen unless its part of an
>aggregat e statement. Is this true? Is there a way around this?
>TIA

It's possibe the $loc_id is not being parsed as its value.

You obvioiusly know nothing about PHP.

That is correct. Neither do the people on the PHP list. It
appears Rick Wannal spotted the error.

No, the error was a Jet SQL error, not a PHP error.

No. The error was a programmer error. It was a PHP error because there
was a Jet error that existed because of programmer error. Rick Wannal
spotted the programmer error that will fix the Jet error that will not
be result in a PHP error.

[David W. Fenton]

salad <oi*@vinegar.co m> wrote in
news:3h******** *********@newsr ead1.news.pas.e arthlink.net:

David W. Fenton wrote:

salad <oi*@vinegar.co m> wrote in
news:oh******** *******@newsrea d2.news.pas.ear thlink.net:

David W. Fenton wrote:

salad <oi*@vinegar.co m> wrote in
news:PM**** ************@ne wsread1.news.pa s.earthlink.net :

>Lorenzo Thurman wrote:
>
>>I'm connecting to an access database and I would like the
>>query to return results grouped on a particular column. When I
>>try the following query, I get this error back from PHP:
>>DB Error: syntax Error
>>
>>"SELECT * FROM HardwareInstall ationsSummary WHERE LocationID =
>>$loc_ID GROUP BY Manufacturer"
>>
>>Iposted this question on a PHP list and someone there
>>suggest ed that SQl does not allow this to happen unless its
>>part of an aggregate statement. Is this true? Is there a way
>>around this? TIA
>
>It's possibe the $loc_id is not being parsed as its value.

You obvioiusly know nothing about PHP.

That is correct. Neither do the people on the PHP list. It
appears Rick Wannal spotted the error.

No, the error was a Jet SQL error, not a PHP error.

No. The error was a programmer error. It was a PHP error because
there was a Jet error that existed because of programmer error.
Rick Wannal spotted the programmer error that will fix the Jet
error that will not be result in a PHP error.

The PHP part of the code was correct. It would have produced no
error had the SQL been correct.

It was not a PHP error -- it was PHP reporting an error that came
from the ODBC driver.

Calling it a "programmer error" is ludicrous, since had it been a
PHP syntax error or a SQL error, it would still have been typed by
the programmer involved.

My point in drawing this out is that the answers that suggested
treating the construction of the dynamic SQL string the same way you
would do it in VBA were ignorant of the way PHP parses variables
inline in strings, and missed the point entirely.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/