473,323 Members | 1,589 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Can't use GROUP BY from PHP

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
9 1454
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Mike | last post by:
I am sure that I am making a simple boneheaded mistake and I would appreciate your help in spotting in. I have just installed apache_2.0.53-win32-x86-no_ssl.exe php-5.0.3-Win32.zip...
3
by: moon | last post by:
Hi all, Im trying to execute this statement in SQL Plus but am getting the error not a single-group group function. My code is below. To explain what Im trying to get at, I want to return the...
1
by: Tamer Higazi | last post by:
Hi! I have problems creating joins between tables and to limit rows for a specified clolumn. I have 2 tables! employees departments I face the problems with the GROUP BY clause and i don't...
0
by: Randell D. | last post by:
Folks, I have two tables - one called parent, the other called group. table group is made up of ID,parentID,groupHash and groupName. table parent is made up of...
13
by: Leo | last post by:
Hi I had asekd previously a question qith regards to running a DLL file from ASP. It was not running for me. Bo error returned, nothing, Just an empty string (when it should have not been...
5
by: Bayla Frankl | last post by:
Hi all, I am a little stumped. I have a query I am trying to run to retrieve the last Progress Note record from the database for the current patient so that the therapists can see the last note...
12
by: Bill Moran | last post by:
Hey all. I've hit an SQL problem that I'm a bit mystified by. I have two different questions regarding this problem: why? and how do I work around it? The following query: SELECT GCP.id,...
10
by: Extremest | last post by:
I know there are ways to make this a lot faster. Any newsreader does this in seconds. I don't know how they do it and I am very new to c#. If anyone knows a faster way please let me know. All...
2
by: Extremest | last post by:
Here is the code I have so far. It connects to a db and grabs headers. It then sorts them into groups and then puts all the complete ones into another table. Problem I am having is that for some...
52
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.