By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,985 Members | 2,907 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,985 IT Pros & Developers. It's quick & easy.

Can't use GROUP BY from PHP

P: n/a
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 HardwareInstallationsSummary 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
Jun 6 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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 HardwareInstallationsSummary 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 HardwareInstallationsSummary WHERE LocationID = '" & _
loc_ID & "' GROUP BY Manufacturer"

or

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

This assumes loc_id is a string.
Jun 6 '06 #2

P: n/a
In article <PM****************@newsread1.news.pas.earthlink.n et>,
salad <oi*@vinegar.com> 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 HardwareInstallationsSummary 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 HardwareInstallationsSummary WHERE LocationID = '" & _
loc_ID & "' GROUP BY Manufacturer"

or

"SELECT * FROM HardwareInstallationsSummary 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 HardwareInstallations.Manufacturer, CountOfModel, Model, Platform
FROM HardwareInstallations WHERE LocationID = 7 GROUP BY
HardwareInstallations.Manufacturer [nativecode=37000 [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.
Jun 6 '06 #3

P: n/a
You can't use GROUP BY with SELECT *.

Select specific columns and include all of them as GROUP BY, SUM(), COUNT(),
or something.
Jun 6 '06 #4

P: n/a
salad <oi*@vinegar.com> wrote in
news:PM****************@newsread1.news.pas.earthli nk.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 HardwareInstallationsSummary 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/
Jun 7 '06 #5

P: n/a
David W. Fenton wrote:
salad <oi*@vinegar.com> wrote in
news:PM****************@newsread1.news.pas.earthli nk.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 HardwareInstallationsSummary 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.
Jun 7 '06 #6

P: n/a
On Tue, 06 Jun 2006 19:13:21 GMT, "Rick Wannall"
<wa*****@notadomain.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.


Jun 7 '06 #7

P: n/a
salad <oi*@vinegar.com> wrote in
news:oh***************@newsread2.news.pas.earthlin k.net:
David W. Fenton wrote:
salad <oi*@vinegar.com> wrote in
news:PM****************@newsread1.news.pas.earthli nk.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 HardwareInstallationsSummary 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/
Jun 7 '06 #8

P: n/a
David W. Fenton wrote:
salad <oi*@vinegar.com> wrote in
news:oh***************@newsread2.news.pas.earthlin k.net:

David W. Fenton wrote:
salad <oi*@vinegar.com> wrote in
news:PM****************@newsread1.news.pas.eart hlink.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 HardwareInstallationsSummary 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.

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.
Jun 7 '06 #9

P: n/a
salad <oi*@vinegar.com> wrote in
news:3h*****************@newsread1.news.pas.earthl ink.net:
David W. Fenton wrote:
salad <oi*@vinegar.com> wrote in
news:oh***************@newsread2.news.pas.earthlin k.net:
David W. Fenton wrote:

salad <oi*@vinegar.com> wrote in
news:PM****************@newsread1.news.pas.ear thlink.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 HardwareInstallationsSummary 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.


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/
Jun 8 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.