Gossamer Forum: Products: DBMan: Customization: Partial display of multiple records

Sep 21, 2006, 9:43 AM

jgold723

Novice (48 posts)

Sep 21, 2006, 9:43 AM

Post #1 of 3

Shortcut

Partial display of multiple records

I'm using DBMan to search and display (only) a fairly simple database file (exported from an Access database) that tracks dump usage by citizen. Each citizen has a permit number that is used to track what categories of stuff the leave at the dump. There are multiple records for each citizen (one per category). So the database looks like this:

permit #|citizen name|category|amount
00234|Fred Smith|Metal|2 yards
00234|Fred Smith|Glass|1 yard
00234|fred Smith|Paper|4 yards

So if Fred searches the database to see how much he's dumped, he gets three records:

Permit: 00234
Citizen: Fred Smith
Caregory: Metal
Amount: 2 yards

Permit: 00234
Citizen: Fred Smith
Caregory: Glass
Amount: 1 yard

Permit: 00234
Citizen: Fred Smith
Caregory: Paper
Amount: 4 yards

We'd like the results to appear as:
Permit: 00234
Citizen: Fred Smith
Category: Metal
Amount: 2 yards
Category: Glass
Amount: 1 yard
Category: Paper
Amount: 4 yards

Short of redesigning the original Access database to put all of this data into a single record, is there anything I can do to achieve the above result?

Thanks,

John

Sep 21, 2006, 10:52 AM

JPDeni

Veteran / Moderator (8669 posts)

Sep 21, 2006, 10:52 AM

Post #2 of 3

Shortcut

Re: [jgold723] Partial display of multiple records In reply to

Redesigning it would be the ideal, but I understand your not wanting to do that.

You're probably going to want to have other search results formats for other things, like to be able to see how much glass has been dumped or something, so we'll make this conditional and you can probably work out other search results formats as needed.

In sub html_view_success, change

Code:

 for (0 .. $numhits - 1) { 
  print "<P>"; 
  &html_record (&array_to_hash($_, @hits)); 
 }

Code:

if ($in{'permitnumber'}) // or whatever the field is that Fred used for his search 
{ 
  %rec = &array_to_hash(0, @hits) 
  print qq| 
    Permit: $rec{'permitnumber'}<br> 
    Citizen: $rec{'citizen'}<br> 
  |; 
  for (0 .. $numhits - 1) { 
    $rec = &array_to_hash($_, @hits)); 
    print qq| 
      Category: $rec{'category'}<br> 
      Amount: $rec{'amount'}<br> 
    |; 
 } 

} 
else 
{ 
  for (0 .. $numhits - 1) { 
    print "<P>"; 
    &html_record (&array_to_hash($_, @hits)); 
 } 

}

Naturally, you'll change the field names to match your setup.

Basically, it just takes the identifying info from the first record and then pulls the rest of the info from all the records.

You could get fancy with it and have it look like:

Fred Smith (permit #000234) has dumped the following:
2 yards of Metal
1 yard of Glass
4 yards of Paper

You can put those variables in any order you want, as long as they're between the qq| and |; delimiters.

(This is a lovely candidate for a relational database. :) )

JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.

Sep 21, 2006, 12:54 PM

jgold723

Novice (48 posts)

Sep 21, 2006, 12:54 PM

Post #3 of 3

Shortcut

Re: [JPDeni] Partial display of multiple records In reply to

Many thanks -- this is terrific!

John