How to group results???

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
swood
Forum Newbie
Posts: 3
Joined: Wed Aug 02, 2006 10:39 pm

How to group results???

Post by swood »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi, new to PHP and MYSQL.  I am having trouble formatting the display of my data.

I currently have a table that lists locations of meetings.

Code: Select all

$query = sprintf( "SELECT * FROM `meeting` order by town");

$result = mysql_query($query);

$num=mysql_numrows($result);
echo "<td align=center>Total number of meetings found $num</td></tr><td>";
$i=0;
while ($i < $num) {

$town=mysql_result($result,$i,"town");
$group_name=mysql_result($result,$i,"group_name");
$note=mysql_result($result,$i,"note");
$day_name=mysql_result($result,$i,"day_name");
$address=mysql_result($result,$i,"address");
$meetTime=mysql_result($result,$i,"meetTime");

echo "&nbsp;<b>$Town</b><br>";
echo "&nbsp;&nbsp;<b>Group Name: </b>$group_name<br>";
echo "&nbsp;&nbsp;<b>Address: </b>$address<br>";
echo "&nbsp;&nbsp;<b>$day_name: </b>$meetTime<br>";
echo "&nbsp;&nbsp;<b>Notes: </b>$note<br><hr><br>";

$i++;
}
mysql_close($conn);
}
My output currently is a like below. I would like to group the results by Town name. So that the town name is listed only once per set of groups. Thanks for your help. I hope I have been clear enough.

Steve

Code: Select all

AMAGANSETT
  Group Name: GROUP 1
  Address: Main St
  Sunday: 7:30
  Notes:

--------------------------------------------------------------------------------

 AMAGANSETT
  Group Name: GROUP 2
  Address: South St.
  Sunday: 11:00AM
  Notes: 

--------------------------------------------------------------------------------

 BABYLON
  Group Name: Group 3
  Address: Main St.
  Sunday: 7:30 AM
  Notes: 

--------------------------------------------------------------------------------

 BABYLON
  Group Name: Group 4
  Address: W. Main St.
  Sunday: 9:30 AM
  Notes:

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Follow the link in my signature to Forum Tour. From there, follow the link to Useful Posts. I believe the thread of your particular interest would be the second link.
swood
Forum Newbie
Posts: 3
Joined: Wed Aug 02, 2006 10:39 pm

Post by swood »

feyd,

Thanks for the reply and the links. Sorry about not conforming to proper techniques. I have been trying to muck throuhg this for some time now. I am just a hobbiest when it comes to programming. The basics I can handle but once I get into loops and variables I get lost. Some day I hope to get some formal training. Anyway, I was trying basically what was in that second link under Useful Posts.

It seems that I get all the results from the first query followed by the results from the second query.

My database looks something like this:

Code: Select all

+---+------------------------+----------+--------------+
|id | group_name             | town     | address      | 
+---+------------------------+----------+--------------+
|16 | MID-ISLAND             | BABYLON  | United Method|
|17 | OFF MAIN STREET        | BABYLON  | Christ Episco|
|18 | BRIDGE GROUP           | BAYSHORE | Captree State|
|19 | GOODY GUYS             | BAYSHORE | Bay Shore YMC|
|20 | HOLBROOK GROUP         | HOLBROOK | Christ Episco|
|21 | NUTS & BOLTS GROUP     | HOLBROOK | Cross of Chri|
|22 | CRAZY MEN              | HOLBROOK | Christ Episco|
|23 | FRIENDSHIP             | HOLBROOK | Robert Moses |
+---+------------------------+----------+--------------+
I started with output like this:

Code: Select all

TOWN: BABYLON
  GROUP: MID-ISLAND
  ADDRESS: UNITED METHOD
  ----------------------------------------------------
TOWN: BABYLON
  GROUP: OFF MAIN STREET
  ADDRESS: CHRIST EPISCO
  ----------------------------------------------------
TOWN: BAYSHORE
 GROUP1 IN BAYSHORE
  ----------------------------------------------------
TOWN: BAYSHORE
 GROUP2 IN BAYSHORE
  ----------------------------------------------------
TOWN: BAYSHORE
 GROUP3 IN BAYSHORE
  ----------------------------------------------------
TOWN: BAYSHORE
 GROUP1 IN HOLBROOK
  ----------------------------------------------------
TOWN: BAYSHORE
 GROUP2 IN HOLBROOK
 -----------------------------------------------------
ETC...
I am trying to get the output to look like this:

Code: Select all

TOWN: BABYLON
-----------------------------------------------------------------
-----------------------------------------------------------------
  GROUP: MID-ISLAND
  ADDRESS: UNITED METHOD
  --------------------------------------------------------
  GROUP: OFF MAIN STREET
  ADDRESS: CHRIST EPISCO
  --------------------------------------------------------
TOWN: BAYSHORE
-----------------------------------------------------------------
-----------------------------------------------------------------
 GROUPS IN BAYSHORE
  --------------------------------------------------------
TOWN: HOLBROOK
-----------------------------------------------------------------
-----------------------------------------------------------------
 GROUPS IN HOLBROOK
-----------------------------------------------------------------
ETC...
Using code from the link you provided which is basically what I was trying before I posted by output was in the form of:

Code: Select all

BABYLON
--------------------
BAYSHORE
--------------------
HOLBROOK
--------------------
GROUP
--------------------
GROUP
--------------------
GROUP
--------------------
Here is my php code:

Code: Select all

// $b is a value from a drop down menu.  value = town name
$query = sprintf( "SELECT * FROM `meeting` WHERE town = '$b' order by town");
$query2 = sprintf( "SELECT town FROM `meeting` WHERE town = '$b' group by town");

// Perform Query
$result = mysql_query($query);
$result2 = mysql_query($query2);


$num_rows = mysql_num_rows($result2);
$i=0;
///////////////////////////////////////////////////
while ($row = mysql_fetch_assoc($result2)) {
$town_name = $row['town'];
$i++;
//
if ($i == $num_rows) {
echo "<b>$town_name</b>:"; }
else 
//
{echo "<b>$town_name</b>:"; }
}
////////////////////////////////////////////////////
echo "<BR><BR>";
///////////////////////////
while ($row = mysql_fetch_array($result)) {
$town_name1 = $row['town'];
$group_name = $row['group_name'];
$address = $row['address'];


$array_town = ($row['town']);

if ($array_town != $town_name) {

$town_name = $array_town;
echo "GROUP:$group_name";
echo "<BR>";
echo "ADDRESS:$address";
echo "<HR>";
}
else

echo "<HR>";
}
I appreciate any insite to this. I understand that the first while is being fully completed. This gives me a list of towns. Then the second while runs and gives me all the groups. What I want is the first while to give the the first town and then something to give me all the groups in that town, go back to the first while get the next town and all the groups for that town, etc...

If coding was only as easy as english :D.

Thanks again. I am totally willing to read and learn.

Steve
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

This should only require a single query. They query to fetch all the groups (and associated town.) It should be ordered by town first, then however you wish afterward.

When pulling result data, keep track of the previous record's town. When they differ, write out the town heading. When they don't, don't write out the town heading.
swood
Forum Newbie
Posts: 3
Joined: Wed Aug 02, 2006 10:39 pm

Post by swood »

Feyd - Thanks for the input. You make it sound so simple and easy.

This is what I've come up with and it seems to give me what I want. Do you see anything that is really wrong with what I am doing? Thanks again.

Code: Select all

$num=mysql_numrows($result);
echo "<td align=center>Total number of meetings found $num</td></tr><td>";
for ($i = 0; $i < $num; $i++) {
// $i = 0 is the first record returned we always want a town header.
if ($i == 0 ) {
$lastTown = mysql_result($result,0,"town");
$currentTown = mysql_result($result,0,"town");
echo "<div id=townName><B>$currentTown<BR></b></div>";
echo "<br>";
}
else {
// $lastTown will be the previous record's town
$currentTown = mysql_result($result,$i,"town");
$lastTown = mysql_result($result,$i-1,"town");
}
// dipslays results without town heading
if ($lastTown == $currentTown){
$town=mysql_result($result,$i,"town");
$group_name=mysql_result($result,$i,"group_name");
$note=mysql_result($result,$i,"note");
$dayName=mysql_result($result,$i,"$c");
$address=mysql_result($result,$i,"address");
echo "<hr>";
echo "&nbsp;&nbsp;<b>Group Name: </b>$group_name";
echo "<br>";
echo "&nbsp;&nbsp;<b>Address: </b>$address";
echo "<br>";
echo "&nbsp;&nbsp;<b>$d: </b>$dayName";
echo "<br>";
echo "&nbsp;&nbsp;<b>Notes: </b>$note";
echo "<br>";
}
else {
// displays results with town heading
$town=mysql_result($result,$i,"town");
$group_name=mysql_result($result,$i,"group_name");
$note=mysql_result($result,$i,"note");
$dayName=mysql_result($result,$i,"$c");
$address=mysql_result($result,$i,"address");
echo "<br>";
echo "<div id=townName><B>$currentTown<BR></b></div>";
echo "<br>";
echo "&nbsp;&nbsp;<b>Group Name: </b>$group_name";
echo "<br>";
echo "&nbsp;&nbsp;<b>Address: </b>$address";
echo "<br>";
echo "&nbsp;&nbsp;<b>$d: </b>$dayName";
echo "<br>";
echo "&nbsp;&nbsp;<b>Notes: </b>$note";
echo "<br>";
}
}
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You have duplication in several of the output lines. They could be moved and consolidated outside of the if().
Post Reply