1: #!/usr/bin/perl -w 2: 3: # 4: # This is my first useful piece of code, and I would like 5: # comments from people in the know, and anyone else. 6: # Specifically, what have I don't wrong, what have I done 7: # well? Is there a better way to do it, without using a 8: # database? 9: # 10: # I know about some problems, like I should probably be 11: # using html::template, rather than having the first and 12: # second half of the pages sitting in different files 13: # Also, it doesn't find all the results. I'll post 14: # some sample data below, for you to look at. 15: # Thank you in advance. 16: # Once all the changes have been made, should I update 17: # this post to show the improvements you've suggested? 18: # This was my first go with CGI, databases, and SQL. 19: # I will be grateful for any suggestions. 20: 21: #### 22: #Data Sample 23: #Here are the first three lines of the CSV file 24: #"H0001-12","0810827085",$40.00,"FUNCTIONAL SINGING VOICE",,"MUSI" 25: #"H0001-13","0921513097",$5.00,"DIGNITY OF DUST - FREIBERG",,"ENGL" 26: #"H0001-14","0919626726",$5.00,"HDOGRAM","PK PAGE","ENGL" 27: 28: #!/usr/bin/perl -w 29: 30: use strict; 31: use DBI; 32: use CGI; 33: 34: $|++; 35: 36: my @names; 37: my $connectstr; 38: my $dbh; 39: my $sql; 40: my $sth; 41: my $count=0; 42: my $q; 43: my $search; 44: my $criteria; 45: 46: $connectstr="DBI:CSV:f_dir=/home/httpd/data;" . 47: "csv_eol=\n;" . 48: "csv_sep_char=,;" . 49: "csv_quote_char=\""; 50: @names=qw(Consign ISBN Price Title Author Subject); 51: 52: $q=CGI->new; 53: print $q->header(-expires=>"-1d"); 54: 55: open HTML, "startpage" or die "opening startpage: $!\n"; 56: print while(<HTML>); 57: close HTML or warn "closing startpage: $!\n"; 58: 59: $search=$1 if ($q->param('search') =~ /^(Title|Author|ISBN|Subject)$/); 60: die "from bad input!\n" unless ($search); 61: 62: $criteria=$1 if($q->param('criteria') =~ /(\w*)/); 63: die "from bad input!\n" unless ($criteria); 64: $criteria =~ tr/a-z/A-Z/; 65: 66: print $q->p("Searching for $search matching $criteria"); 67: 68: $dbh=DBI->connect($connectstr) 69: or die "opening connection: $DBI::errstr; stopped\n"; 70: $dbh->{'csv_tables'}->{'onshelf'} = {'col_names' => [@names]}; 71: 72: $sql="SELECT * FROM onshelf WHERE $search like ?"; 73: 74: $sth=$dbh->prepare($sql) 75: or die "preparing $sql: $DBI::errstr stopped\n"; 76: 77: $count=$sth->execute("%$criteria%") 78: or die "executing $sql: $DBI::errstr stopped\n"; 79: 80: $sth->bind_columns(\my ($consign, $isbn, $price, $title, $author, $subject)); 81: 82: print $q->p("Found $count results"); 83: 84: print $q->start_table({-border=>"1"}); 85: while($sth->fetch()) 86: { 87: print $q->start_Tr(), 88: $q->td({-width=>'90', -valign=>"top"}, $consign), 89: $q->td({-width=>'100', -valign=>"top"}, $isbn), 90: $q->td({-width=>'180', -valign=>"top"}, $title), 91: $q->td({-width=>'150', -valign=>"top"}, $author), 92: $q->td({-width=>'50', -valign=>"top"}, $subject), 93: $q->td({-width=>'60', -align=>"right", -valign=>"top"},$price), 94: $q->end_Tr(); 95: } 96: print $q->end_table(); 97: 98: $dbh->disconnect(); 99: 100: open HTML, "endpage" or die "opening end page: $!\n"; 101: print while(<HTML>); 102: close HTML or warn "closing HTML: $!\n"; 103: 104: 105: # 106: #Updated March 27, as per tye's suggestions. Thanks Tye. 107: #Updated March 27th, again, as per dkubb's suggestions. 108: #
|
---|
Replies are listed 'Best First'. | |
---|---|
(dkubb) Re: (2) A Little review for a little DBI and CGI?
by dkubb (Deacon) on Mar 28, 2001 at 10:08 UTC | |
by coolmichael (Deacon) on Mar 28, 2001 at 13:38 UTC | |
by tye (Sage) on Mar 28, 2001 at 23:01 UTC | |
by coolmichael (Deacon) on Mar 29, 2001 at 00:45 UTC | |
by marius (Hermit) on Mar 28, 2001 at 22:00 UTC | |
(tye)Re: A Little review for a little DBI and CGI?
by tye (Sage) on Mar 28, 2001 at 05:31 UTC | |
by coolmichael (Deacon) on Mar 28, 2001 at 13:46 UTC | |
by tye (Sage) on Mar 28, 2001 at 20:38 UTC | |
Re: A Little review for a little DBI and CGI?
by andye (Curate) on Mar 28, 2001 at 18:48 UTC |
Back to
Craft