#!/usr/bin/perl -w # # This is my first useful piece of code, and I would like # comments from people in the know, and anyone else. # Specifically, what have I don't wrong, what have I done # well? Is there a better way to do it, without using a # database? # # I know about some problems, like I should probably be # using html::template, rather than having the first and # second half of the pages sitting in different files # Also, it doesn't find all the results. I'll post # some sample data below, for you to look at. # Thank you in advance. # Once all the changes have been made, should I update # this post to show the improvements you've suggested? # This was my first go with CGI, databases, and SQL. # I will be grateful for any suggestions. #### #Data Sample #Here are the first three lines of the CSV file #"H0001-12","0810827085",$40.00,"FUNCTIONAL SINGING VOICE",,"MUSI" #"H0001-13","0921513097",$5.00,"DIGNITY OF DUST - FREIBERG",,"ENGL" #"H0001-14","0919626726",$5.00,"HDOGRAM","PK PAGE","ENGL" #!/usr/bin/perl -w use strict; use DBI; use CGI; $|++; my @names; my $connectstr; my $dbh; my $sql; my $sth; my $count=0; my $q; my $search; my $criteria; $connectstr="DBI:CSV:f_dir=/home/httpd/data;" . "csv_eol=\n;" . "csv_sep_char=,;" . "csv_quote_char=\""; @names=qw(Consign ISBN Price Title Author Subject); $q=CGI->new; print $q->header(-expires=>"-1d"); open HTML, "startpage" or die "opening startpage: $!\n"; print while(); close HTML or warn "closing startpage: $!\n"; $search=$1 if ($q->param('search') =~ /^(Title|Author|ISBN|Subject)$/); die "from bad input!\n" unless ($search); $criteria=$1 if($q->param('criteria') =~ /(\w*)/); die "from bad input!\n" unless ($criteria); $criteria =~ tr/a-z/A-Z/; print $q->p("Searching for $search matching $criteria"); $dbh=DBI->connect($connectstr) or die "opening connection: $DBI::errstr; stopped\n"; $dbh->{'csv_tables'}->{'onshelf'} = {'col_names' => [@names]}; $sql="SELECT * FROM onshelf WHERE $search like ?"; $sth=$dbh->prepare($sql) or die "preparing $sql: $DBI::errstr stopped\n"; $count=$sth->execute("%$criteria%") or die "executing $sql: $DBI::errstr stopped\n"; $sth->bind_columns(\my ($consign, $isbn, $price, $title, $author, $subject)); print $q->p("Found $count results"); print $q->start_table({-border=>"1"}); while($sth->fetch()) { print $q->start_Tr(), $q->td({-width=>'90', -valign=>"top"}, $consign), $q->td({-width=>'100', -valign=>"top"}, $isbn), $q->td({-width=>'180', -valign=>"top"}, $title), $q->td({-width=>'150', -valign=>"top"}, $author), $q->td({-width=>'50', -valign=>"top"}, $subject), $q->td({-width=>'60', -align=>"right", -valign=>"top"},$price), $q->end_Tr(); } print $q->end_table(); $dbh->disconnect(); open HTML, "endpage" or die "opening end page: $!\n"; print while(); close HTML or warn "closing HTML: $!\n"; # #Updated March 27, as per tye's suggestions. Thanks Tye. #Updated March 27th, again, as per dkubb's suggestions. #