I am relatively green when it comes to Perl and thought I would ask if someone had done this before I tackle it. I have managed to get DBMan running to my satisfaction except that I would like the list results come out as one big table with the titles at the top and each record being a row in the table.

Has anyone done any modification like this or give me some pointers or guidance?

Without having done this myself, I think I can give you some things to start with. I would start the table in sub view_success:

print qq|<TABLE><TR>|;

Then do a loop to put in the field labels (if I understand what you're wanting to do correctly).

foreach $column (@db_cols) {
print qq|<TD>$column</TD>|;
}

then end the table row with

print qq|</TR>|;

In sub html_record, just put all your data in one table row:

<TR><TD>$rec{'field1'}</TD>
<TD>$rec{'field2'}</TD>...

and end it with </TR>

back at html_view_success, below

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

add

print qq|</TABLE>|;

and then whatever you want at the bottom of the page.

That should do it.

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


[This message has been edited by JPDeni (edited January 06, 1999).]

I did this by modifying the sub html_record area of html.pl as follows:

Remove <TR> from the beginning of all except the first line after the print qq statement, and removing the </TR> from all except the last line before the </TABLE> statement.
I then moved all the <TD ALIGN> lines into one location, and moved all the <TD> lines into one location. Then make sure that the last <TD ALIGN> line has a </TR> at the end.
Also, Add a <TR> to the first <TD> line.

Here is a sample of my code:
#############################################
# Set up table
print qq|
<TABLE WIDTH="475" CELLPADDING=0 CELLSPACING=0 BORDER=1 BGCOLOR="#FFFFCC">
<TR>
<TD ALIGN="Right" VALIGN="TOP" WIDTH="20%"><$font_color>ID:</FONT></TD>
<TD ALIGN="Right" VALIGN="TOP"><$font_color>First Name:</FONT></TD>
<TD ALIGN="Right" VALIGN="TOP"><$font_color>Last Name:</FONT></TD>
<TD ALIGN="Right" VALIGN="TOP"><$font_color>Maiden Name:</FONT></TD>
<TD ALIGN="Right" VALIGN="TOP"><$font_color>E-Mail Address:</FONT></TD>
<TD ALIGN="Right" VALIGN="TOP"><$font_color>URL: </FONT></TD>
<TD ALIGN="Right" VALIGN="TOP"><$font_color>Class Of: </FONT></TD>
<TD ALIGN="Right" VALIGN="TOP"><$font_color>Address:</FONT></TD>
<TD ALIGN="Right" VALIGN="TOP"><$font_color>City:</FONT></TD>
<TD ALIGN="Right" VALIGN="TOP"><$font_color>State:</FONT></TD>
<TD ALIGN="Right" VALIGN="TOP"><$font_color>Zip:</FONT></TD>
<TD ALIGN="Right" VALIGN="TOP"><$font_color>Telephone:</FONT></TD>
<TD ALIGN="Right" VALIGN="TOP"><$font_color>Occupation:</FONT></TD>
<TD ALIGN="Right" VALIGN="TOP"><$font_color>Employer:</FONT></TD>
<TD ALIGN="Right" VALIGN="TOP"><$font_color>Comments:</FONT></TD>
</TR>

<TR>
<TD WIDTH="80%"> <$font>$rec{'ID'}</Font></TD>
<TD> <$font>$rec{'First_Name'}</Font></TD>
<TD> <$font>$rec{'Last_Name'}</Font></TD>
<TD> <$font>$rec{'Maiden_Name'}</Font></TD>
<TD> <$font>$rec{'E_Mail_Address'}</Font></TD>
<TD> <$font><A HREF="$rec{'URL'}">$rec{'URL'}</A></Font></TD>
<TD> <$font>$rec{'Class_of'}</Font></TD>
<TD> <$font>$rec{'Address'}</Font></TD>
<TD> <$font>$rec{'City'}</Font></TD>
<TD> <$font>$rec{'State'}</Font></TD>
<TD> <$font>$rec{'Zip'}</Font></TD>
<TD> <$font>$rec{'Telephone'}</Font></TD>
<TD> <$font>$rec{'Occupation'}</Font></TD>
<TD> <$font>$rec{'Employer'}</Font></TD>
<TD> <$font>$rec{'Comments'}</Font></TD>
</TR>
</TABLE>
|;
}
############################################

I'm using DBMAN as an Alumni Database for my High School Alumni Association.


Jamie


------------------
James A. (Jamie) Dennis
*Certified AIX Administrator
*Certified Solaris Administrator
*Member - SAGE, USENIX

JP

Hey, the way you suggest on making the tables is great, and better than my idea, but I get some weird stuff when I try it.

The descriptions in the table all have <> before them, and the data is not in tabular
format.

My other problem is, I don't want all fields to print on the display, as the comments
field is quite large, and causes a table to look really screwy. I need to do something
different with the foreach, maybe initialize another array?

Jamie


Here is my code :
---------------------------------------------sub html_record {
# -----------
# How a record will be displayed. This is used primarily in
# returning search results and how it is formatted. The record to
# be displayed will be in the %rec hash.

my (%rec) = @_; # Load any defaults to put in the VALUE field.
($db_auto_generate and print &build_html_record(%rec) and return);

my $font_color = 'Font face="Verdana, Arial, Helvetica" Size=2 Color=#0
03399';
my $font = 'Font face="Verdana, Arial, Helvetica" Size=2';

# Set up table
########################################################################
# Moved this entire block in front of print qq
#<TABLE WIDTH="475" CELLPADDING=0 CELLSPACING=0 BORDER=1
BGCOLOR="#FFFFCC">
#<TR>
#<TD ALIGN="Right" VALIGN="TOP"
WIDTH="20%"><$font_color>ID:</FONT></TD>
#<TD ALIGN="Right" VALIGN="TOP"><$font_color>First
Name:</FONT></TD>
#<TD ALIGN="Right" VALIGN="TOP"><$font_color>Last
Name:</FONT></TD>
#<TD ALIGN="Right" VALIGN="TOP"><$font_color>Maiden
Name:</FONT></TD>
#<TD ALIGN="Right" VALIGN="TOP"><$font_color>E-Mail
Address:</FONT></TD>
#<TD ALIGN="Right" VALIGN="TOP"><$font_color>URL: </FONT></TD>
#<TD ALIGN="Right" VALIGN="TOP"><$font_color>Class Of:
</FONT></TD>
#<TD ALIGN="Right" VALIGN="TOP"><$font_color>Address:</FONT></TD>
#<TD ALIGN="Right" VALIGN="TOP"><$font_color>City:</FONT></TD>
#<TD ALIGN="Right" VALIGN="TOP"><$font_color>State:</FONT></TD>
#<TD ALIGN="Right" VALIGN="TOP"><$font_color>Zip:</FONT></TD>
#</TR>
########################################################################

# These are the fields I want to display

print qq|

<TR>
<TD><$font>$rec{'ID'}</Font></TD>
<TD><$font>$rec{'First_Name'}</Font></TD>
<TD><$font>$rec{'Last_Name'}</Font></TD>
<TD><$font>$rec{'Maiden_Name'}</Font></TD>
<TD><$font><A
HREF="mailto:$rec{'E_Mail_Address'}">$rec{'E_Mail_Address
'}</A></Font></TD>
<TD><$font><A HREF="$rec{'URL'}">$rec{'URL'}</A></Font></TD>
<TD><$font>$rec{'Class_of'}</Font></TD>
<TD><$font>$rec{'Address'}</Font></TD>
<TD><$font>$rec{'City'}</Font></TD>
<TD><$font>$rec{'State'}</Font></TD>
<TD><$font>$rec{'Zip'}</Font></TD>
</TR>
|;

sub html_view_success {
# --------------------------------------------------------
# This page displays the results of a successful search.
# You can use the following variables when displaying your
# results:
#
# $numhits - the number of hits in this batch of results.
# $maxhits - the max number of hits displayed.
# $db_total_hits - the total number of hits.
# $db_next_hits - html for displaying the next set of results.
#
my (@hits) = @_;
my ($numhits) = ($#hits+1) / ($#db_cols+1);
my ($maxhits); $in{'mh'} ? ($maxhits = $in{'mh'}) : ($maxhits = $db_max
_hits);

&html_print_headers;
print qq|
<html>
<head>
<title>$html_title: Search Results.</title>
</head>

<body bgcolor="#DDDDDD">
<blockquote>
<table border=1 bgcolor="#FFFFFF" cellpadding=5 cellspacing=3 width=500
valign=top>
<tr><td colspan=2 bgcolor="navy"><FONT FACE="MS Sans Serif,
arial,helvetica" size=1 COLOR="#FFFFFF">
<b>$html_title: Search Results</b>
</font></td></tr>
</table>
<p><$font>
Your search returned <b>$db_total_hits</b> matches.</font>
|;
if ($db_next_hits) {
print "<br><$font>Pages: $db_next_hits</font>";
}

# Attempt to table-ize output - JDennis

print qq|<TABLE WIDTH="475" CELLPADDING=0 CELLSPACING=0
BORDER=1 BGCOLOR="#FFFF
CC"><TR>|;

foreach $column (@db_cols) {
print qq|<TD><$font_color>$column</FONT></TD>|;
}
print qq|</TR>|;

# End of attempt - JDennis

# Go through each hit and convert the array to hash and send to
# html_record for printing.
for (0 .. $numhits - 1) {
# Hack for table-izing output - JDennis
print qq|</TABLE>|;
#print "<P>";
&html_record (&array_to_hash($_, @hits));
}
if ($db_next_hits) {
print "<br><$font>Pages: $db_next_hits</font>";
}

print qq|
<p>
<table border=0 bgcolor="#DDDDDD" cellpadding=5 cellspacing=3 w
idth=500 valign=top>
<tr><td>|; &html_footer; print qq|</td></tr>
</table>
</blockquote>
</body>
</html>
|;
}




------------------
James A. (Jamie) Dennis
*Certified AIX Administrator
*Certified Solaris Administrator
*Member - SAGE, USENIX

You can still use the foreach loop even if you don't want to display all the fields. Depending on how many fields you don't want to display, it might be easier just to name them all. See below for how to just eliminate one field.

The reason your table comes out weird is that you have the line:

print qq|</TABLE>|;

before the call to html_record. You don't want to close the table yet. Move that line to just before

if ($db_next_hits) {

(the bottom one!)

In html_record, just define the rows. Don't put any table tags in them.

Okay, now here's the really hairy part of all this. You will have problems in html_delete_form, html_add_success, html_modify_success -- maybe others.

What I would do is define two displays -- one with the full info and one in the "spreadsheet" format. I'd keep the full info display in html_record, so you wouldn't have to change things in a bunch of subroutines and make a new subroutine called, oh, html_record_spreadsheet. That's the one that would just have table rows.

So, the middle of your html_view_success would be:

print qq|<TABLE WIDTH="475" CELLPADDING=0 CELLSPACING=0 BORDER=1 BGCOLOR="#FFFFCC"><TR>|;

foreach $column (@db_cols) {
unless ($column eq 'Description') {
print qq|<TD><$font_color>$column</FONT></TD>|;
}
}
print qq|</TR>|;

# Go through each hit and convert the array to hash and send to
# html_record for printing.
for (0 .. $numhits - 1) {
&html_record_spreadsheet (&array_to_hash($_, @hits));
}
print qq|</TABLE>|;


sub html_record_spreadsheet {

my (%rec) = @_;
print qq|
<TR>
<TD><$font>$rec{'ID'}</Font></TD>
<TD><$font>$rec{'First_Name'}</Font></TD>
<TD><$font>$rec{'Last_Name'}</Font></TD>
<TD><$font>$rec{'Maiden_Name'}</Font></TD>
<TD><$font><A
HREF="mailto:$rec{'E_Mail_Address'}">$rec{'E_Mail_Address'}</A></Font></TD>
<TD><$font><A HREF="$rec{'URL'}">$rec{'URL'}</A></Font></TD>
<TD><$font>$rec{'Class_of'}</Font></TD>
<TD><$font>$rec{'Address'}</Font></TD>
<TD><$font>$rec{'City'}</Font></TD>
<TD><$font>$rec{'State'}</Font></TD>
<TD><$font>$rec{'Zip'}</Font></TD>
</TR>|;
}

If you have a number of fields you don't want to display, it would be better to substitute the foreach loop with the actual column labels.


Do you have a variable called $font_color defined? If you don't, that would account for this problem. Try changing it to just $font.



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

My thanks for the replies -- I haven't had time to try any of the suggestions yet.

Brian, the things JP said work great - it helped me out a great deal. If you need copies of the working code, let me know.

Jamie


------------------
James A. (Jamie) Dennis
*Certified AIX Administrator
*Certified Solaris Administrator
*Member - SAGE, USENIX

Carol,

The Thread is somewhere in the Discussion Forum. It was for someone who needed to have records for a library database printed in a text format for the administrator only.

Regards,

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us

I thought I saw a thread in this forum pertaining to a mod that would list records in an alternative table form.
This thread is close but not what I remember.

It would save a lot of time if it was possible to create an alternative record display for mailing labels.
(records 3 across and 10 down using only name and address fields)

If you have tried this, remember the thread in this forum, or know where I can turn for help
please post a reply

Thanks

I can't find it either, but I was the one who wrote it, so I can probably help you out.

The problem with printing mailing labels is that you have to be certain of the size of the print and the spacing between the fields. Browsers aren't very accurate when it comes to things like that.

But, if you'd like to give it a try, I can help you out.



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

Eliot, the one I was thinking of was asked by a guy with a database of plants and trees and he wanted 12 records to a page, 3 across and 4 down.


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

I still haven't found it,
but I sure appreciate the efforts, Carol and Elliot. You both add such great sanity and professionalism -- when people like me are floundering and tearing hair out.
Yes, Carol, I could use the help -- in spite of browser imprecision. Please take good care of yourself first, though.

Oh, I'm doing okay. But thanks for your concern.

One thing that we need to establish is how to tell the script to use the "mailing labels" display. I'm assuming that you don't want the mailing labels to be the default way of displaying search results.

How do you want to tell the script to print out mailing labels? By a checkbox on the search form? By a link?




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

A checkbox on the search form would provide the best flexibility and since it seems to me that it would allow some filtering and sorting.
(For example: filter by status=active or sort by zip.)
Thanks...
Ron

I wasn't feeling too well yesterday and didn't even turn on my computer. Today I have errands to run, so I don't have enough time to write the code for you this afternoon. When I get back from the errands, I'll write your code. Deal?


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

Whenever the time is right for you is fine with me. I am still glowing with dumb pride for getting a membership database to function in DBMan in the first place -- without any more 404's and "premature end of script headers" stuff.
Having a potential mailing label print feature could save some members a lot of time and frustration if we can get it to work. Otherwise we may need to maintain multiple databases. (That is what I a hopng to avoid.)
Thanks again
Ron

I started working on this, but came up against a snag I hadn't thought about before. If there are more than 30 records returned, I'm not sure how to go between pages. You won't want to have a link for each page that will print out on your labels.

I can set it up so that it will print all records on the same page. That's the only way I think it would work.


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

It could be done in the database and the search page.
Right now the mailing field is "Y" or "N".
It could be a number 1 through about 7 or "N", which would divide members (only about 200) by interest or zip code or something and give a smaller number (less than 30) for a mailing label page.
Just a thought and still better than multiple databases.
The other possibilty might be a simple export intended for <i>MS Works</i> and do labels from there.
What do you think?
Ron

You could certainly create a file to be exported. You would have to know the format necessary for your program. Then you could save a .txt file with the information formatted correctly.

Another thought (but this is beginning to get complicated) is to have the search results first give a list of the pages. Then you would click on the link to pull up each page in turn. For example, when you first do the search, you would get an html page that would look like:

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

Your search returned 150 records. Click the links below to display them.

1 2 3 4 5

After printing the page, use the "Back" button on your browser to return to this page.
-------------

When the user clicks the 1, he would get a text file displayed with the first set of labels on it. After printing that page, he would use the "Back" button on the browser and click the 2 to get the second page of labels.

Theoretically, this is possible. I'm not sure if I know how to do it as yet, though.


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

If it's complicated for you --
it's unfathomable (sp?) for me
I wouldn't mind losing the last row of labels for links stuff.
If you could get me started, I could polish it up and post the results here.
Ron

There's two very different ways of going with this. If you want to have it print out on an html page, with links, you can use tables to format the data. If you want it to print out as a text file, you can have more control over the spacing, but it's a little more complicated to print it out.

(I know you're just itching to get some code, but it's a whole lot better to define exactly what you want the final result to be before you start writing. Otherwise it's wasted effort.)

If you're using tables, you can use the

for (0 .. $numhits - 1) {

loop to start and end table rows with every 3 records.

If you want text, you'll have to take 3 records at a time, print out all the names, with appropriate spaces between them, then print out the addresses, then print out the cities. Text is the only way that you can really have any control over the size of the print, though.


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

ew my brain hurts from thinking
I think, think, think and nothing happens. The code snippet was still greek to me.
I must stop this thinking or I will turn into Winnie The Pooh.
If printing from HTML won't work, the it seems that an export of selected fields and filtered files (capability restricted to authorized users) might be the only way to go.
Now I will look for a thread in this forum to see if someone else has tried the export of selected fields and/or filtered files approach.
Thanks JP and Elliot for being there and for trying.
Ron

[This message has been edited by Reger (edited September 28, 1999).]