Page 1 of 1

How to group results???

Posted: Wed Aug 02, 2006 11:11 pm
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]

Posted: Wed Aug 02, 2006 11:18 pm
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.

Posted: Thu Aug 03, 2006 10:43 am
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

Posted: Thu Aug 03, 2006 11:33 am
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.

Posted: Tue Aug 08, 2006 4:54 am
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>";
}
}

Posted: Tue Aug 08, 2006 8:26 am
by feyd
You have duplication in several of the output lines. They could be moved and consolidated outside of the if().