[RESOLVED] Grouping or Summarizing like data

[beandip]

I am trying to do something which I imagine is quite easy, but for the life of me I can't figure it out. I'm hoping someone here will take pity on me and point me in the right direction.

I have a table called employees. that table has 3 fields: dept, firstname, and lastname. I can easily extract the contents of those three fields and display them on screen. Doing so would look something like this:

Example 1:
Dept - First - Last
Plumbing Tom Lewis
Plumbing Cathy Lavine
Paint Bob Smith
Paint Steve Jones
Hardware Tina Bullock
Hardware Fred Schmidt

However, I need to format the report so it is a bit more readable. I would like it to summarize by dept. So the final output might look like this:

Example 2:
Plumbing:
  Tom Lewis
  Cathy Lavine
Paint:
  Bob Smith
  Steve Jones
Hardware:
  Tina Bullock
  Fred Schmidt

So my code looks like this right now. This will result in Example 1 above:

$sql = "SELECT * FROM employees ORDER BY dept";
$result = mysql_query($sql);
$num = mysql_numrows($result);

$i=0;

echo 'Dept - First - Last';
while ($i < $num)
           {
              $dept= mysql_result($result,$i,"dept");
              $first= mysql_result($result,$i,"first");
              $last= mysql_result($result,$i,"last");


              echo ''.$dept.' '.$first.' '.$last.'';

       ++$i;
       }

Conceptually I understand that somehow I need to loop through the array watching the dept field. When it changes, then I need to loop back and start with the next dept until it changes. I just don't have enough experience to pull this off.

I found this link that seems to describe what I want to do, but unfortunately I don't know how to use the example and tweak it to work in my scenario.

Thanks in advance for any assistance.

[Piranha]

You could do it the way below. Note that there is better ways, I just tried to do it from your code. Also note that you need to format with spaces, row changes and such stuff, I haven't done anything like that.

$sql = "SELECT * FROM employees ORDER BY dept";
$result = mysql_query($sql);
$num = mysql_numrows($result);

$i=0;

echo 'Dept - First - Last';
$olddept = ''; // Olddept will be used to see if it is still the same dept of if there 
               // is a new dept. We set it to nothing to start with, it will avoid problems
while ($i < $num)
           {
              $dept= mysql_result($result,$i,"dept");
              $first= mysql_result($result,$i,"first");
              $last= mysql_result($result,$i,"last");

          if ($dept != $olddept)  // If there is a new dept
          {
                echo $dept;
                $olddept = $dept; // Makes sure that the new dept won't be new next time
          }


              echo ''.$first.' '.$last.'';

       ++$i;
       }

[beandip]

Piranha,

Thank you SO much for your reply. That is exactly the functionality I was looking for. I will be able to fill in the rest.

Excellent!