comment on

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

Are you posting in the right place? Check out Where do I post X? to know for sure.
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
Want more info? How to link or How to display code and escape characters are good places to start.


Pathologically Eclectic Rubbish Lister
	PerlMonks