מסדי נתונים / בחירת מסד הנתונים האידיאלי / מטפלים בטפסים עם שאילתות SQL
מטפלים בטפסים עם שאילתות SQL : selection.iphtml
<?
my $dbh = DBI->connect('DBI:mysql:test:localhost', '','',
{ PrintError => 0}) || die $DBI::errstr;
!>//
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">
<HTML>
<HEAD>
<TITLE>An ePerl Database Example: Fiddling with Albums</TITLE>
</HEAD>
<BODY>
<P>
Use the following form to query the contents of an
(admittedly limited) database of CD albums of mine.
<TABLE>
<TR>
<TD><?=${ \( search_by_band ( \$dbh ) ) }!></TD>
<TD><?=${ \( search_by_year ( \$dbh ) ) }!></TD>
</TR>
</TABLE>
<FORM ACTION=receive.iphtml METHOD=POST>
<B>Or, type in a title:</B> <INPUT NAME=title SIZE=20>
(for all titles, just put the cursor in this field and hit ENTER)
</FORM>
</BODY>
</HTML>
<?
$dbh->disconnect;
!>//
<?
sub search_by_band {
#
# Note that I passed a reference to the database handle dbh.
# This means that, in order to reference it within this
# subroutine, I'll have to refer to it as $$dbh.
#
my $dbh = shift;
my $return = '';
#
# The "distinct" keyword in SQL will only return one row for a set of
# identical matches. "Order by" will sort the returned set
alphabetically.
#
my $SQL = <<"EOT";
select distinct artist
from albums
order by artist
EOT
my $cursor = $$dbh->prepare($SQL);
$cursor->execute;
$return .= "<FORM ACTION=receive.iphtml METHOD=POST>\n";
$return .= "<B>Pick an artist:</B><BR>\n";
$return .= "<SELECT NAME=artist>\n";
my @fields;
while ( @fields = $cursor->fetchrow ) {
$return .= "<OPTION>$fields[0]\n";
}
$return .= "</SELECT><BR>\n";
$return .= "<INPUT TYPE=SUBMIT NAME=artist_submit
VALUE=\"Go Search on This Artist!\">\n";
$return .= "</FORM>\n";
}
sub search_by_year {
my $dbh = shift;
my $return = '';
#
# If COLUMN is defined as a date datum, then year(COLUMN)
will return only
# the year portion of the data in the column. "Order by COLUMN desc"
# will reverse the usual sort order.
#
my $SQL = <"EOT";
select distinct year(released)
from albums
order by released desc
EOT
my $cursor = $$dbh->prepare($SQL);
$cursor->execute;
$return .= "<FORM ACTION=receive.iphtml METHOD=POST>\n";
$return .= "<B>Or, pick a year:</B><BR>\n";
$return .= "<SELECT NAME=year>\n";
my @fields;
while ( @fields = $cursor->fetchrow ) {
$return .= "<OPTION>$fields[0]\n";
}
$cursor->finish;
$return .= "</SELECT><BR>\n";
$return .= "<INPUT TYPE=SUBMIT NAME=year_submit
VALUE=\"Go Search on This Year!\">\n";
$return .= "</FORM>\n";
}
!>//
עמוד הבא: מעבדים את תוצאות הטופס עם-CGI.pm >>
| 
