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