#!/usr/bin/perl -w push (@INC,"/home/master/applications/mcpl/public_html/cgi-bin"); require ("cgi-lib.pl"); require "header_footer.lib"; &ReadParse(\%input); use DBI(); # Connect to the database. my $dbh = DBI->connect("DBI:mysql:database=yrbook;mysql_socket=/var/lib/mysql/mysql.sock", "root", "mobob26", {'RaiseError' => 1}); # we never reach this point if we wanted a redirect print &PrintHeader; print " Yearbook Search | Monroe County Public Library, Indiana "; #This finds the searched for yearbook data. if ($input{'isdata'} || $input{'records'} =~ /^0/) { # Now retrieve data from the table. #The new SELECT statement grabs from three tables. See A SECTION of the corresponding .sql file #-----------------------------BEGIN A SECTION my $sthcount = $dbh->prepare("SELECT p.lastname, p.firstname, p.middlename, p.maidenname, pisy.status, s.name, s.yearbook, y.yearincluded, y.page, y.url FROM person p, school s, yearandpage y, personinschoolyearbook pisy WHERE p.id=pisy.personid AND s.id=y.schoolid AND y.yearandpageid=pisy.yearandpageid AND lastname LIKE '%$input{'lastname'}%' AND firstname LIKE '%$input{'firstname'}%' AND middlename LIKE '%$input{'middlename'}%' AND maidenname LIKE '%$input{'maidenname'}%' AND name LIKE '%$input{'name'}%' ORDER BY p.lastname, p.firstname, p.middlename, p.maidenname, s.name, y.yearincluded"); #-----------------------------END A SECTION $sthcount->execute(); my $refcount = $sthcount->rows; # no search was entered unless (($input{'lastname'}) || ($input{'firstname'}) || ($input{'middlename'}) || ($input{'maidenname'}) || ($input{'name'})) { print "
You need to enter a search term. Please try again.
\n"; exit 0; } unless ($refcount) { # no queries matched print "

No yearbook entries matched your search query. Please try a variant spelling or a new search.

\n"; } else { $pages = sprintf "%1d\n", (($refcount / 50) + 0.99999); if ($pages > 1) { print "Pages: "; foreach $pagecount (0..($pages - 1)) { $records = 50 * $pagecount; $pagedisplay = $pagecount + 1; $recordscount = "$records"; $next = 50; if ($pagecount =~ '^0$') { print "$pagedisplay "; } else { print "$pagedisplay "; } } print "next "; } } #This SELECT grabs from all three tables, and includes the LIMIT 0, 50 at the end that was in the original code. See B SECTION of the corresponding .sql file #---------------------------------------------BEGIN B SECTION my $sth = $dbh->prepare("SELECT p.lastname, p.firstname, p.middlename, p.maidenname, pisy.status, s.name, s.yearbook, y.yearincluded, y.page, y.url FROM person p, school s, yearandpage y, personinschoolyearbook pisy WHERE p.id=pisy.personid AND s.id=y.schoolid AND y.yearandpageid=pisy.yearandpageid AND lastname LIKE '%$input{'lastname'}%' AND firstname LIKE '%$input{'firstname'}%' AND middlename LIKE '%$input{'middlename'}%' AND maidenname LIKE '%$input{'maidenname'}%' AND name LIKE '%$input{'name'}%' ORDER BY p.lastname, p.firstname, p.middlename, p.maidenname, s.name, y.yearincluded LIMIT 0, 50"); #---------------------------------------------END B SECTION $sth->execute(); my $ref = $sth->fetchrow_hashref(); # no search was entered unless (($input{'lastname'}) || ($input{'firstname'}) || ($input{'middlename'}) || ($input{'maidenname'}) || ($input{'name'})) { print "
You need to enter a search term. Please go back and try again.
\n"; exit 0; } unless ($ref) { # no queries matched print ""; } else { print "

These entries match your search criteria.

Search Again

\n"; $i = 0; #initialize a loop counter in order to label table rows as even or odd while($ref){ $i++; if($i%2 == 1){ print ""; } else { print ""; } #This if statement makes it so if the data entry has a URL, it will become a hyperlink on the entry's lastname value if ($ref->{'url'}) { print " \n"; $ref = $sth->fetchrow_hashref(); } else { print " \n"; $ref = $sth->fetchrow_hashref(); } } $sth->finish(); } print"
Last name First name Middle name Maiden name Status
Key to Abbreviations		 School Yearbook Year Page
$ref->{'lastname'} $ref->{'firstname'} $ref->{'middlename'} $ref->{'maidenname'} $ref->{'status'} $ref->{'name'} $ref->{'yearbook'} $ref->{'yearincluded'} $ref->{'page'}
$ref->{'lastname'} $ref->{'firstname'} $ref->{'middlename'} $ref->{'maidenname'} $ref->{'status'} $ref->{'name'} $ref->{'yearbook'} $ref->{'yearincluded'} $ref->{'page'}
\n"; } if ($input{'records'}) { #---------------------------------------BEGIN A SECTION my $sthcount = $dbh->prepare("SELECT p.lastname, p.firstname, p.middlename, p.maidenname, pisy.status, s.name, s.yearbook, y.yearincluded, y.page, y.url FROM person p, school s, yearandpage y, personinschoolyearbook pisy WHERE p.id=pisy.personid AND s.id=y.schoolid AND y.yearandpageid=pisy.yearandpageid AND lastname LIKE '%$input{'lastname'}%' AND firstname LIKE '%$input{'firstname'}%' AND middlename LIKE '%$input{'middlename'}%' AND maidenname LIKE '%$input{'maidenname'}%' AND name LIKE '%$input{'name'}%' ORDER BY p.lastname, p.firstname, p.middlename, p.maidenname, s.name, y.yearincluded"); #---------------------------------------END A SECTION $sthcount->execute(); my $refcount = $sthcount->rows; $pages = sprintf "%1d\n", (($refcount / 50) + 0.99999); $prev = $input{'records'} - 50; print "Pages: prev "; foreach $pagecount (0..($pages -1)) { $records = 50 * $pagecount; $pagedisplay = $pagecount + 1; $recordscount = "$records"; $next = $input{'records'} + 50; if ($input{'records'} =~ "^$records\$") { print "$pagedisplay "; } else { print "$pagedisplay "; } } if ($input{'records'} =~ "^$records\$") { print ""; } else { print "next "; } #Similar to the other select statements, but includes the LIMIT $input{'records'}, 50 found in the original code. See C SECTION of the corresponding .sql file #--------------------------------------------BEGIN C SECTION my $sth = $dbh->prepare("SELECT p.lastname, p.firstname, p.middlename, p.maidenname, pisy.status, s.name, s.yearbook, y.yearincluded, y.page, y.url FROM person p, school s, yearandpage y, personinschoolyearbook pisy WHERE p.id=pisy.personid AND s.id=y.schoolid AND y.yearandpageid=pisy.yearandpageid AND lastname LIKE '%$input{'lastname'}%' AND firstname LIKE '%$input{'firstname'}%' AND middlename LIKE '%$input{'middlename'}%' AND maidenname LIKE '%$input{'maidenname'}%' AND name LIKE '%$input{'name'}%' ORDER BY p.lastname, p.firstname, p.middlename, p.maidenname, s.name, y.yearincluded LIMIT $input{'records'}, 50"); #--------------------------------------------END C SECTION $sth->execute(); my $ref = $sth->fetchrow_hashref(); print "

These entries match your search criteria.

Search Again

\n"; $i = 0; #initialize a loop counter in order to label table rows as even or odd while($ref){ $i++; if($i%2 == 1){ print ""; } else { print ""; } #This if statement makes it so if the data entry has a URL, it will become a hyperlink on the entry's lastname value if ($ref->{'url'}) { print " \n"; $ref = $sth->fetchrow_hashref(); } else { print " \n"; $ref = $sth->fetchrow_hashref(); } } $sth->finish(); } if ($input{'isdata'} || $input{'records'}) { print"
Last name First name Middle name Maiden name Status School Yearbook Year Page
$ref->{'lastname'} $ref->{'firstname'} $ref->{'middlename'} $ref->{'maidenname'} $ref->{'status'} $ref->{'name'} $ref->{'yearbook'} $ref->{'yearincluded'} $ref->{'page'}
$ref->{'lastname'} $ref->{'firstname'} $ref->{'middlename'} $ref->{'maidenname'} $ref->{'status'} $ref->{'name'} $ref->{'yearbook'} $ref->{'yearincluded'} $ref->{'page'}
\n"; } print <

Last name:
First name:
Middle name:
Maiden name:
School:
You may search any of the fields individually or in combination.
Type as much of the name as you know. For example, the search term Jim would locate Jim, Jimmy, etc.
You may insert the percent sign ( % ) in place of individual unknown letters. For example, the search term Sm%th would locate Smith, Smyth, etc.

Key to Abbreviations

The index includes names of students who attended area middle and high schools in Monroe County and who were included in their yearbook. See also Ancestry and Unionville High School\'s Memory page with Jordannus Yearbooks for more yearbook information and photos.

Guidelines for requesting copies of yearbook pages

Due to the level of demand for this service we restrict the number of names requested at one time to four (4).

Please allow 2-4 weeks for delivery.

Send request to:

Indiana Room
Monroe County Public Library
303 E. Kirkwood Ave.
Bloomington, IN 47408

or

e-mail via Ask Indiana Room

Be sure to include the name of the student, grade, school, and date along with your name and mailing address.

EOCHUNK # Disconnect from the database. $dbh->disconnect(); exit 0;