Grouping results of query

Hi;

I'm brand new to PHP (just starting today to convert tons of
ColdFusion/Access code to PHP/MySQL). There is a function in
Coldfusion that I can't find an equivalent to in PHP. Here is
what I am trying to do:

If I have a query that results in this information:
State Firstname Lastname
Idaho Terry Pratchet
Vermont Dan Brown
Vermont Bob Smith
Virginia Alice Johnson
Virginia David Jones

I would like to output the info like this:

Idaho
Terry Pratchet
Vermont
Dan Brown
Bob Smith
Virginia
Alice Johnson
David Jones

But all I can find in PHP is a way to loop through the query
and show every row... if you are familiar with Coldfusion,
what I am looking for is something similar to this:

<CFOUTPUT GROUP='State'>
#State#<BR>
<CFOUTPUT>
#Firstname# #Lastname#
</CFOUTPUT>
<BR>
</CFOUTPUT>

Is there a function that does this in PHP, or do I need to
write code that watches the value of 'State' and manually
takes care of things whenever it changes?

Thanks for the help.

re: Grouping results of query

Curt Bousquet wrote:[color=blue]
> Hi;
>
> I'm brand new to PHP (just starting today to convert tons of
> ColdFusion/Access code to PHP/MySQL). There is a function in
> Coldfusion that I can't find an equivalent to in PHP. Here is
> what I am trying to do:
>
> If I have a query that results in this information:
> State Firstname Lastname
> Idaho Terry Pratchet
> Vermont Dan Brown
> Vermont Bob Smith
> Virginia Alice Johnson
> Virginia David Jones
>
> I would like to output the info like this:
>
> Idaho
> Terry Pratchet
> Vermont
> Dan Brown
> Bob Smith
> Virginia
> Alice Johnson
> David Jones
>
> But all I can find in PHP is a way to loop through the query
> and show every row... if you are familiar with Coldfusion,
> what I am looking for is something similar to this:
>
> <CFOUTPUT GROUP='State'>
> #State#<BR>
> <CFOUTPUT>
> #Firstname# #Lastname#
> </CFOUTPUT>
> <BR>
> </CFOUTPUT>
>
> Is there a function that does this in PHP, or do I need to
> write code that watches the value of 'State' and manually
> takes care of things whenever it changes?
>
> Thanks for the help.[/color]

your table looks strange, but i suppose it's just the tabs.

mysql takes care of your problem, have a look at SELECT and GROUP BY

micha

re: Grouping results of query

On 28 Apr 2005 01:13:08 -0700, micha wrote:[color=blue]
> Curt Bousquet wrote:[color=green]
>> Idaho Terry Pratchet
>> Vermont Dan Brown
>> Vermont Bob Smith
>> Virginia Alice Johnson
>> Virginia David Jones
>>
>> I would like to output the info like this:
>>
>> Idaho
>> Terry Pratchet
>> Vermont
>> Dan Brown
>> Bob Smith
>> Virginia
>> Alice Johnson
>> David Jones[/color]
>
> mysql takes care of your problem, have a look at SELECT and GROUP BY[/color]

No it doesn't, unless you propose to have separate queries for each
state. So Curt will have to do something like:

$state = '';
while ( $row = mysql_fetch_array( $res ) )
{
if ( strcmp( $state, $row['state'] ) )
{
$state = $row['state'];
echo $state."\n";
}
echo "\t{$row['firstname']} {$row['lastname']}\n";
}


--
Firefox Web Browser - Rediscover the web - http://getffox.com/
Thunderbird E-mail and Newsgroups - http://gettbird.com/

re: Grouping results of query

Curt Bousquet wrote:
[color=blue]
> Hi;
>
> I'm brand new to PHP (just starting today to convert tons of
> ColdFusion/Access code to PHP/MySQL). There is a function in
> Coldfusion that I can't find an equivalent to in PHP. Here is
> what I am trying to do:
>
> If I have a query that results in this information:
> State Firstname Lastname
> Idaho Terry Pratchet
> Vermont Dan Brown
> Vermont Bob Smith
> Virginia Alice Johnson
> Virginia David Jones
>
> I would like to output the info like this:
>
> Idaho
> Terry Pratchet
> Vermont
> Dan Brown
> Bob Smith
> Virginia
> Alice Johnson
> David Jones
>[/color]

I've never seen one discussed, so assuming it does not exist, you would have
to make one. There are two ways. The first is to make many trips to the
server as in (With postgress examples):

$results = pg_query("SELECT state from table group by state");
$rows = pg_fetch_all($results);
$answer = array();
foreach ($rows as $row) {
$results = pg_query(
"SELECT firstname,lastname
FROM states where state=".$row["state"]);
$answer[$row["state"]] = pg_fetch_all($results);
}

Second possibility would be to pull the whole thing down, and then walk
through it like so:

$results = pg_query("SELECT * from table group by state");
$answer = array();
while ($row = pg_fetch_array($results)) {
$state = $row["state"];
if (!isset($answer[$state])) {
$answer[$state"] = array();
}

$answer[$state][] = $row;
}


Either one of these could be made into a generalized routine. Send us your
result by clacks.

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)

re: Grouping results of query

On 28 Apr 2005 01:13:08 -0700, "micha" <chotiwallah@web.de> wrote:
[color=blue]
>
>Curt Bousquet wrote:[color=green]
>> Hi;
>>
>> I'm brand new to PHP (just starting today to convert tons of
>> ColdFusion/Access code to PHP/MySQL). There is a function in
>> Coldfusion that I can't find an equivalent to in PHP. Here is
>> what I am trying to do:
>>
>> If I have a query that results in this information:
>> State Firstname Lastname
>> Idaho Terry Pratchet
>> Vermont Dan Brown
>> Vermont Bob Smith
>> Virginia Alice Johnson
>> Virginia David Jones
>>
>> I would like to output the info like this:
>>
>> Idaho
>> Terry Pratchet
>> Vermont
>> Dan Brown
>> Bob Smith
>> Virginia
>> Alice Johnson
>> David Jones
>>
>> But all I can find in PHP is a way to loop through the query
>> and show every row... if you are familiar with Coldfusion,
>> what I am looking for is something similar to this:
>>
>> <CFOUTPUT GROUP='State'>
>> #State#<BR>
>> <CFOUTPUT>
>> #Firstname# #Lastname#
>> </CFOUTPUT>
>> <BR>
>> </CFOUTPUT>
>>
>> Is there a function that does this in PHP, or do I need to
>> write code that watches the value of 'State' and manually
>> takes care of things whenever it changes?
>>
>> Thanks for the help.[/color]
>
>your table looks strange, but i suppose it's just the tabs.
>
>mysql takes care of your problem, have a look at SELECT and GROUP BY[/color]

GROUP BY is not required unless you're performing aggregate functions
such as min(), max(), count(), etc.

An ORDER BY in the query and, as the OP suggests, code that outputs a
new State whenever it changes is the solution.

--
David ( @priz.co.uk )

re: Grouping results of query

Kenneth Downs <knode.wants.this@see.sigblock> wrote let it be
known in news:l127k2-ngb.ln1@pluto.downsfam.net:
[color=blue]
> Curt Bousquet wrote:
>[/color]
[color=blue][color=green]
>> I would like to output the info like this:
>>
>> Idaho
>> Terry Pratchet
>> Vermont
>> Dan Brown
>> Bob Smith
>> Virginia
>> Alice Johnson
>> David Jones
>>[/color]
>
> Second possibility would be to pull the whole thing down,
> and then walk through it like so:
>
> $results = pg_query("SELECT * from table group by state");
> $answer = array();
> while ($row = pg_fetch_array($results)) {
> $state = $row["state"];
> if (!isset($answer[$state])) {
> $answer[$state"] = array();
> }
>
> $answer[$state][] = $row;
> }
>
>
> Either one of these could be made into a generalized
> routine. Send us your result by clacks.[/color]

Everybody knows the post office is faster than clacks... but
thanks. I got it working with code that is a combination of
what you posted and the idea from Ewoud. I was hoping that
there would be an 'elegant' solution built into PHP (which is
why I wasted hours Googling for such a solution), but I guess
this brute force method does the trick :)

I need to use this code a LOT, so am going to work on fitting
it into a function that I can call by passing it the name of
the field to watch and the HTML to output when that field
changes. If I get it all worked out, I'll post the results
here.

re: Grouping results of query

Curt Bousquet wrote:
[color=blue]
> Kenneth Downs <knode.wants.this@see.sigblock> wrote let it be
> known in news:l127k2-ngb.ln1@pluto.downsfam.net:
>[color=green]
>> Curt Bousquet wrote:
>>[/color]
>[color=green][color=darkred]
>>> I would like to output the info like this:
>>>
>>> Idaho
>>> Terry Pratchet
>>> Vermont
>>> Dan Brown
>>> Bob Smith
>>> Virginia
>>> Alice Johnson
>>> David Jones
>>>[/color]
>>
>> Second possibility would be to pull the whole thing down,
>> and then walk through it like so:
>>
>> $results = pg_query("SELECT * from table group by state");
>> $answer = array();
>> while ($row = pg_fetch_array($results)) {
>> $state = $row["state"];
>> if (!isset($answer[$state])) {
>> $answer[$state"] = array();
>> }
>>
>> $answer[$state][] = $row;
>> }
>>
>>
>> Either one of these could be made into a generalized
>> routine. Send us your result by clacks.[/color]
>
> Everybody knows the post office is faster than clacks... but[/color]

Not so sure, I understand they can send pictures now, something about
different colors of light.

Glad you got it working.


--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)