Apache::clickable

software development

#1

I found something in CPAN that looks like something I can use, but I’m not sure how. I haven’t used anything called a filter. It’s Apache::clickable.

What I’m currently working on is a Perl search application to find technical publications from our group. One of the table’s items is a url of the publication. (http://www.ykfp.org/dh_phpmyadmin/mysql.ykfp.org/, user name selectonly, password satus2, table tmpImportWebSpecs for example.) If I build an html table of the search results, am I going to have to set this clickable filter on right after the html header to make the returned url fields hyperlinked and clickable. Or do I build the result html table, then pass it through the filter.


#2

Uh, why not just include the HTML at the time you retrieve the results?

[code]my $html = ‘’;
my $statement = ‘SELECT uri,title FROM publications WHERE 1’;
my $sth = $dbh->prepare($statement);
while ($row = $sth->fetchrow_hashref) {
$html .= p(a{href=>$$row{uri}}, $$row{title}));
}

print $html;[/code]That filter is not for you to use in your CGI, it is for the web server to use on all your pages.

:cool: Perl / MySQL / HTML+CSS


#3

But of course! href=> !

I was mistakenly thinking the only way to use the filter would be to write out the result html to a file, then run it through this filter, then send it back to the user somehow.

Thanks for the help.


#4

To display the url as a hyperlink, would you make an exception with an if inside the foreach to wrap the url string in href=> ? Or would you loop through the rows @col_name - 1 times, then treat the last item differently with a href=> ? ykfp.org/cgi-bin/litsearch2.pl

sub display_listing
{
my $ref = shift;
my @row; # array to hold display table rows
my @col_name = # columns to display, in the order they should be displayed
(
“Year”, “Category” , “Title”, “Author”, “RptPhyAddress”
);
my %label = # labels for each column
(
“Year” => “Year”,
“Category” => “Category”,
“Title” => “Title”,
“Author” => “Author”,
“RptPhyAddress” => “URL”
);

Generate table rows; each one contains a label and a value

foreach my $col_name (@col_name)
{
push (@row, Tr (
td ($label{$col_name} . “:”), # label
td (escapeHTML ($ref->{$col_name})) # value
));
}

print hr (), table ({-border => 1}, @row);
}


#5

Yup.

my $value; foreach my $col_name (@col_name) { $value = escapeHTML($ref->{$col_name}); if ($col_name eq 'RptPhyAddress') { $value = a({href=>$value}, $value); } push (@row, Tr ( td ($label{$col_name} . ":"), # label td ($value) # value )); } :cool: Perl / MySQL / HTML+CSS


#6

In sub display_listing above, I can’t figure out why my Author and RptPhyAddress are blank in the output html, even before I try to use href. Do you see anything wrong? ( Try http://ykfp.org/cgi-bin/litsearch3.pl, year = 1999 for a short list.)

I’m having a hard time using Perl debugger on these scripts that take input from a submit on an html form. I tried to strip the main logic down to just a hard wired query to test the subroutine but I can’t get the test script to run. Is that the debug approach you’d use?


#7

I’ve been sticking print statements into this perl code to try to figure out why my output html page can’t list past Year, why Author and RptPhysAddress always come out blank. As soon as I try to add href->Author to the end of line 52, I get two error lines “Use of uninitialized value in print at testsearch2.pl line 54.” I tried initializing Author in line 28, tried to override the case. What is it that I don’t understand about fetchrow_hashref?

27 my $count;
28 my $Author;
29
30 #@ CONSTRUCT_QUERY
31 $col_list = “*”;#
32 #
33 print $col_list
34 # WHERE clause listing the conditions
35 $where = “WHERE " . join (” AND ", @condition) if @condition;
36 $where = “” unless $where;
37 # print “where statment = $where\n\n”;
38 # complete query
39 # $stmt = "SELECT $col_list FROM tmpImportWebSpecs WHERE Year = 1999"
40 $stmt = “SELECT Category, Year, Title, Author, RptPhysAddress FROM tmpImportWebSpecs WHERE Year = 1999"
41 . " ORDER BY Year LIMIT 100”;
42 # print “where statment = $where\n\n”;
43 print “select statment = $stmt\n”;
44 #@ CONSTRUCT_QUERY
45 #
46
47 #@ EXECUTE_QUERY
48 $sth = $dbh->prepare ($stmt);
49 $sth->execute (@placeholder);
50 $count = 0;
51 while (my $ref = $sth->fetchrow_hashref (‘NAME_lc’))
52 {
53 # display_listing ($ref);
54 print $ref->{category}, $ref->{year}, $ref->{author};
55 ++$count;
56 }
57 $sth->finish ();


#8

Try this; outputs a HTML table where odd rows are the column names and even rows are the column values.

[code]# need
use CGI qw(:standard);
use DBI;

my $statement =
'SELECT Category, Year, Title, Author, RptPhysAddress ’ .
'FROM tmpImportWebSpecs WHERE Year = 1999 ’ .
‘ORDER BY Year LIMIT 100’;
my $sth = $dbh->prepare($statement);
if (defined $sth and $sth->execute) {
while ($row = $sth->fetchrow_hashref(‘NAME_lc’)) {

output column names

$table .= Tr(th([keys %$row])) . “\n”;

output column values

$table .= Tr(td([values %$row])) . “\n”;
}
print table({border=>‘1’}, $table);
}
else {
print 'MySQL error: ’ . DBI->errstr . “\n”;
}[/code]
I’m not sure what you wanted to do in your code.

  1. Retrieve rows where Year=1999. Uhm, if Year is always 1999, isn’t ORDER BY Year pointless?
  2. For each row, show the values of Category, Year, and Author. So far, so good.

I don’t know what you are talking about with regard to the $Author variable.

Here’s another way to do what you want. Put the names of the columns in the order you want in @names, and the while loop uses that in a hash slice.

my $sth = $dbh->prepare($statement); if (defined $sth and $sth->execute) { my @names = ('category', 'year', 'author'); my @values = (); $table .= Tr(th(\@names)); while ($row = $sth->fetchrow_hashref('NAME_lc')) { @values = @$row{@names}; $table .= Tr(td(\@values)) . "\n"; } print table({border=>'1'}, $table); } else { print 'MySQL error: ' . DBI->errstr . "\n"; }

:cool: Perl / MySQL / HTML CSS


#9

What I was trying to do with my code was to figure out why my output would only print out blanks for the Author and RptPhyAddress items in the hash from fetchrow_hashref. (See http://ykfp.org/cgi-bin/litsearch5.pl try year = 1999 for a short result list). So I stripped out all the input form and the output form, typed in a set SELECT statement with Year = 1999 ( so, yes, ORDER By Year is pointless), and started testing this in the debugger and inserted print statements here and there so I could see what values were in the hash. If I just had category and year in line 54, the script would run without complaining but as soon as I added $ref->{author} to the end of 54 I got this complaint:

select statment = SELECT Category, Year, Title, Author, RptPhysAddress FROM tmpImportWebSpecs WHERE Year = 1999 ORDER BY Year LIMIT 100
Use of uninitialized value in print at testsearch2.pl line 54.
Use of uninitialized value in print at testsearch2.pl line 54.
Species Interactions1999Bird Predation1999

But it printed out the first two items in the first two lines of the hash. I bumbled around, and tried to declare Author in line 28 and stuck in more print statements to see what the query statement was.

But then I looked at the my table tmpImportWebSpecs with phpMySql at http://mysql.ykfp.org (user selectonly, password satus2) I found out that all the values for Author are NULL in this table. I forgot about that. Because most of the titles have more than one author, I was going to eventually figure out how to bring them in from another related table. I probably don’t need to format the output to display author, but maybe keep them in the input form and add a branch to create a SQL select statement using the author table if author is not eq to “Any”. But is that what’s happening to the fetchrow and the $ref->{author} ? They’re bombing because of the NULL values?


#10

It’s working better. http://ykfp.org/cgi-bin/litsearch6.pl I don’t know why the hash item Author was a problem, or whether the problem was that it was null, but I decided that I don’t want it in the output. Author would have to come from another table with a many to one relation. Also found a typo on the url hash item name. Still need to increase the items in the input form and the result form and dress it up with images borders and such.


#11

I wanted to see how your alternative output looks like but I’m having trouble figuring what to do about $sth. In your code it’s used right in the fetchrow line right where the output is generated, while in my code it’s set in a different subroutine perform_search. How can I rectify the two approaches?

120 #@ PERFORM_SEARCH
121 sub perform_search
122 {
123 my $dbh = shift;
124 my $tbl_info;
125 my $val;
126 my @condition; # conditions for WHERE clause
127 my @placeholder; # values for placeholders
128 my ($sth, $stmt, $col_list, $where);
129 my $count;
.
.
.
205 #@ EXECUTE_QUERY
204 $sth = $dbh->prepare ($stmt);
205 $sth->execute (@placeholder);
206 $count = 0;
207 while (my $ref = $sth->fetchrow_hashref ())
208 {
209 display_listing ($ref);
210 ++$count;
211 }
212 $sth->finish ();
213
214 print (“Sorry, no qualifying listings were found.”) if !$count;
215 #@ EXECUTE_QUERY
216 }
217 #@ PERFORM_SEARCH
218
219 #@ DISPLAY_LISTING
220 sub display_listing
221 {
222 #my $ref = shift;
223 my $row; # array to hold display table rows
224 my $table;
225 #my @col_name = # columns to display, in the order they should be displa yed
226 #(
227 # “Year”, “Category”, “Title”, "RptPhysAddress"
228 #);
229 # my %label = # labels for each column
230 #(
231 # “Year” => “Year”,
232 # “Category” => “Category”,
233 # “Title” => “Title”,
234 # “RptPhysAddress” => "URL"
235 #);
236
237 # Generate table rows; each one contains a label and a value
238
239 #my $value;
240 #foreach my $col_name (@col_name) {
241 # $value = escapeHTML($ref->{$col_name});
242 # if ($col_name eq ‘RptPhysAddress’) {
243 # $value = a({href=>$value}, $value);
244 # }
245 # push (@row, Tr (
246 # td ($label{$col_name} . “:”), # label
247 # td ($value) # value
248 # ));
249 # }
250 # print hr (), table ({-border => 1}, @row);
251 #}
252 #@ DISPLAY_LISTING
253 if (defined $sth and $sth->execute) {
254 my @names = (‘category’, ‘year’, ‘author’);
255 my @values = ();
256 $table .= Tr(th(@names));
257 while ($row = $sth->fetchrow_hashref(‘NAME_lc’)) {
258 @values = @$row{@names};
259 $table .= Tr(td(@values)) . “\n”;
260 }
261 print table({border=>‘1’}, $table);
262 }
263 else {
264 print 'MySQL error: ’ . DBI->errstr . “\n”;
265 }
266 }
267 #@ DISPLAY_LISTING


#12

Perhaps it would help to write what you want to do in English first. I’m taking a stab at starting from scratch:

perform_search

  1. Build SQL statement
  2. Prepare SQL statement handle
  3. Execute SQL statement handle
  4. Pass SQL statement handle to display_listing
  5. Print return value from display_listing

display_listing

  1. Build output string from rows using statement handle
  2. Return output string

[code]sub perform_search($) {
my ($dbh) = @_;
my ($statement, $sth, $output_string);

$statement = ‘SELECT * FROM reports WHERE 1’;
$sth = $dbh->prepare($statement);
if (defined $sth and $sth->execute) {
$output_string = display_listing($sth);
print $output_string;
}
else {
print p('MySQL error: ’ . DBI->errstr);
}

return;
}[/code]perform_search() is simple enough - if the search can be performed, print the results, otherwise print an error message.

[code]sub display_listing($) {
my ($sth) = @_;
my ($output_string, $count, $row, @rows);

retrieve all rows into an array

@rows = ();
while ($row = $sth->fetchrow_hashref) {
push(@rows, { %$row });
}

count the rows

$count = scalar(@rows);

where there any rows?

yes, lets return a table

if ($count > 0) {
my @names = (‘Year’, ‘Category’, ‘Title’, ‘RptPhysAddress’);
$output_string = Tr(th(@names));
my %values = ();
my $value;
foreach $row (@rows) {
%values = %$row;
$value = escapeHTML($values{‘RptPhysAddress’});
$values{‘RptPhysAddress’} = a({href=>$value}, $value);
$output_string .= Tr(td([@values{@names}]));
}

$output_string = table({border=>‘1’}, caption(‘Listing’) . $output_string);

}

no, lets return a message

else {
$output_string = p(‘Sorry, no qualifying listings were found.’);
}

return $output_string;
}[/code]OK, your code wanted to know how many rows were returned, so in order to do that, we have to fetch the rows. But we don’t want to output anything just yet, so we put the rows on an array. Since the size of the array is the number of rows, we can avoid having to increment a counter.

Then, if there were any rows returned, we generate HTML table source.
If there were no rows returned, we generate our message saying the search was unsuccessful.
Either one becomes the return value of display_listing(), and its caller, perofrm_search(), uses the return value to actually output the data.

:cool: Perl / MySQL / HTML CSS


#13

I can’t figure out what’s causing this error:

perl -cw litsearch9.pl
main::perform_search() called too early to check prototype at litsearch9.pl line 27.
litsearch9.pl syntax OK

#! /usr/bin/perl -w
#@ MAIN_PROGRAM

litsearch.pl

use strict;
use lib qw(./);
use CGI qw(:standard escapeHTML);
use WebDB;
use WebDB::TableInfo;

print header (),
start_html (-title => “Search YKFP Publications”, -background => “…/watback.jpg”);

my $dbh = WebDB::connect ();

Dispatch to proper action based on user selection

my $choice = lc (param (“choice”)); # get choice, lowercased

if ($choice eq “”) # initial invocation
{
display_form ($dbh);
}
elsif ($choice eq “search”) # perform search
{
display_form ($dbh); # redisplay the form
perform_search ($dbh); # present the results
}
else
{
print p (escapeHTML (“Logic error, unknown choice: $choice”));
}

$dbh->disconnect ();

print end_html ();

exit (0);
#@ MAIN_PROGRAM

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

#@ DISPLAY_FORM
sub display_form
{
my $dbh = shift;
my $tbl_info;
my ($year, $category, $author, $title, $url);

$tbl_info = WebDB::TableInfo->get ($dbh, “tmpImportWebSpecs”);

Generate the popup menus for the option lists. Add an “Any” item

to the head of each list to serve as the default value.

unshift (@style, “Any”);

Unique values for publication category, year, title, author and url

are found in database tables.

$year = WebDB::get_lookup_values (
$dbh,
“SELECT DISTINCT Year FROM tmpImportWebSpecs ORDER BY Year”);
unshift (@{$year}, “Any”);

$category = WebDB::get_lookup_values (
$dbh,
“SELECT DISTINCT Category FROM tmpImportWebSpecs ORDER BY Category”);
unshift (@{$category}, “Any”);

$author = WebDB::get_lookup_values (
$dbh,
“SELECT DISTINCT Author FROM tmpImportWebSpecs ORDER BY Author”);
unshift (@{$author}, “Any”);

Get additional features list from the features column definition

@features = $tbl_info->members (“features”);

print start_form (-action => url ()),
p (strong (escapeHTML (“Search YKFP Publications”))),
p (“Please select the data characteristics in\n”
. “which you’re interested, then select the Search button.”),
table ({-border => 1},
Tr (
td (“Year published:”),
td (popup_menu (-name => “year”,
-values => $year))
),
Tr (
td ("Category: "),
td (popup_menu (-name => “category”,
-values => $category))
),
Tr (
td ("Author: "),
td (popup_menu (-name => “author”,
-values => $author))
),
Tr (
td (“Title :”),
td (popup_menu (-name => “title”,
-values => $title))
),
),
br (), br (),
submit (-name => “choice”, -value => “Search”),
end_form ();
}
#@ DISPLAY_FORM

#@ PERFORM_SEARCH
sub perform_search($) {
my ($dbh) = @_;
my ($statement, $sth, $output_string);

$statement = ‘SELECT * FROM tmpImportWebSpecs WHERE Year eq 1999’;
$sth = $dbh->prepare($statement);
if (defined $sth and $sth->execute) {
$output_string = display_listing($sth);
print $output_string;
}
else {
print p('MySQL error: ’ . DBI->errstr);
}

return;
}

#@ DISPLAY_LISTING
sub display_listing($) {
my ($sth) = @_;
my ($output_string, $count, $row, @rows);

retrieve all rows into an array

@rows = ();
while ($row = $sth->fetchrow_hashref) {
push(@rows, { %$row });
}

# count the rows

$count = scalar(@rows);

where there any rows?

yes, lets return a table

if ($count > 0) {
my @names = (‘Year’, ‘Category’, ‘Title’, ‘RptPhysAddress’);
$output_string = Tr(th(@names));
my %values = ();
my $value;
foreach $row (@rows) {
%values = %$row;
$value = escapeHTML($values{‘RptPhysAddress’});
$values{‘RptPhysAddress’} = a({href=>$value}, $value);
$output_string .= Tr(td([@values{@names}]));
}

$output_string = table({border=>‘1’}, caption(‘Listing’) . $output_string);

}

no, lets return a message

else {
$output_string = p(‘Sorry, no qualifying listings were found.’);
}

return $output_string;
}

#@ DISPLAY_LISTING