Gossamer Forum: Products: DBMan: Customization: Grouping records by a field

I'm using dbman for a user database on my school's web site (http://www3.mhs.k12.oh.us/email/). I have everything working perfectly, except I would like to be able to group the records by year -- to see it illustrated, go to http://www3.mhs.k12.oh.us/email/default.html. On the page (which was created manually), the years are grouped together -- e.g. there are xx entries for xx year. What I would like to do is automate this page using the database -- so how do I search and group the results as shown in the web page mentioned?

Although the manual version works, I would like the system to be self-maintainable using dbman.

BTW: My record layout looks like this
first name,
last name,
email address,
graduation year,
residence,
etc...

Thanks for your time,
Jason

You'll probably want to do this in sub html_home so folks would see it when they first enter the database.

I'm not sure how you can get the "Faculty" and "Webmasters" listed. But if we can get the years first, we'll add those later. 'K?

Code:
$year_field = 3; #Change this to match the number of the field that holds the year

open (DB, "<$db_file_name") or &cgierr("error in counting records.  
  unable to open db file: $db_file_name.\nReason: $!"); 
  if ($db_use_flock) { flock(DB, 1); } 
  @lines = <DB>; 
close DB; 

LINE: foreach $line (@lines) { 
  if ($line =~ /^$/) { next LINE; } 
  if ($line =~ /^#/) { $output .= $line; next LINE; } 
  chomp ($line);			 
  @data     = &split_decode($line); 
  ++$count{$data[$year_field]}; 
}

Then when you want to print them out, first close off any previous print statement with a |;

(If you don't understand what that means, just ask and I'll explain it.)

Then add

Code:
my ($sec, $min, $hour, $day, $mon, $year, $dweek, $dyear, $daylight) = localtime(time()); 

print qq| 
<Table> 
  <TR><TDYear</TD> 
    <TDEntries</TD></TR> 
|; 

for ($years = $year + 1904; $years > 1935; --$years) { 
  if ($count{$years}) { 
    print qq| 
<TR><TD> 
<a href="$db_script_link_url&sb=2&view_records=1&Graduation=$years">$years</a></td> 
    <td>$count{$years}</td></tr> 
    |; 
  } 
} 
print qq|</table>|;

This takes the current year, adds four (to allow for current freshmen) and counts backwards to 1936, the earliest graduation date you have listed. You could take it back to 1900 if you wanted, or whenever the school was founded.

It only prints out the years for which there are current entries, though. If you want to list all the years, we can do that, too. It will not be able to print out things like "1949-41" though, without a whole lot more than I'm willing to think about tonight. Smile

I didn't add your table formatting in. You can do that, but there wasn't any point in typing it all in.

Try this and make sure it works. Then we'll work on the Faculty and Webmasters entries. I did test it for syntax errors, but nothing else, so I'm not sure that it will work. But I think it will.

------------------
JPD