Skip to content

Instantly share code, notes, and snippets.

@karlwilbur

karlwilbur/movie-list.perl

Created December 11, 2019 16:28
Show Gist options
  • Save karlwilbur/825fb66bd266da44d9d50754c70ca0a2 to your computer and use it in GitHub Desktop.
Save karlwilbur/825fb66bd266da44d9d50754c70ca0a2 to your computer and use it in GitHub Desktop.
Download ZIP
A stupid simple dynamic web app written in perl
Raw
movie-list.perl
#!/usr/bin/perl
#use warnings;
use strict;
use CGI qw(:standard);
use DBI;
#=============================
# Database connection
my $host = "localhost";
my $database = "movie_db";
my $user = "movielist";
my $password = "password";
my $dbn = "DBI:mysql:$database:$host:mysql_client_found_rows";
my $database_handle = DBI->connect($dbn, $user, $password) || die "Couldn't connect to database: ".die($DBI::errstr)."\n";
my $cgi = new CGI;
#=============================
# HTML
sub PrintHeader {
print "<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.1//EN\" \"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd\">";
print "\n<html xmlns=\"http://www.w3.org/1999/xhtml\" xml:lang=\"en\" >";
print "\n\t<head>\n\t\t<title>Karl&apos;s Movie Database</title>\n\t\t<link href=\"/movies/movies.css\" rel=\"stylesheet\" type=\"text/css\" charset=\"ISO-8859-1\" />\n\t</head>";
print "\n\t<body>\n\t\t<div>\n\t\t\t<h1 class=\"center\">Karl&apos;s Movie Database</h1>";
}
sub PrintHome {
print "\n\t\t\t<h2 class=\"center\">Home Page</h2><hr />\n\t\t\t<table class=\"center\">\n\t\t\t\t<tr>\n\t\t\t\t\t<td class=\"list_left\" valign=\"middle\" align=\"right\" ><a href=\"/cgi-bin/movies.pl?action=browse\">Browse</a></td>\n\t\t\t\t\t<td class=\"list_right\" valign=\"middle\" align=\"left\" ><a href=\"/cgi-bin/movies.pl?action=add\">Add</a></td>\n\t\t\t\t</tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<td class=\"list_left\" valign=\"middle\" align=\"right\" ><a href=\"/cgi-bin/movies.pl?action=search\">Search</a></td>\n\t\t\t\t\t<td class=\"list_right\" valign=\"middle\" align=\"left\" ><a href=\"/cgi-bin/movies.pl?action=edit\">Edit</a></td>\n\t\t\t\t</tr>\n\t\t\t\t<tr>\n\t\t\t\t\t<td class=\"list_left\" valign=\"middle\" align=\"right\" >&nbsp;</td>\n\t\t\t\t\t<td class=\"list_right\" valign=\"middle\" align=\"left\"><a href=\"/cgi-bin/movies.pl?action=delete\">Delete</a></td>\n\t\t\t\t</tr>\n\t\t\t</table>";
}
sub PrintPageTitle {
my $page = shift;
print "\n\t\t\t<h2 class=\"center\">".$page."</h2>\n\t\t\<hr />";
}
sub PrintNavBar {
print "\n\t\t\t<table class=\"center\">";
print "\n\t\t\t\t<tr>\n\t\t\t\t\t<td><a href=\"/cgi-bin/movies.pl?action=browse\">Browse</a></td>\n\t\t\t\t\t<td valign=\"middle\" align=\"center\" ><a href=\"/cgi-bin/movies.pl?action=search\">Search</a></td>\n\t\t\t\t\t<td valign=\"middle\" align=\"center\" ><a href=\"/cgi-bin/movies.pl?action=add\">Add</a></td>\n\t\t\t\t\t<td valign=\"middle\" align=\"center\" ><a href=\"/cgi-bin/movies.pl?action=edit\">Edit</a></td>\n\t\t\t\t\t<td valign=\"middle\" align=\"center\"><a href=\"/cgi-bin/movies.pl?action=delete\">Delete</a></td>\n\t\t\t\t</tr>\n\t\t\t</table><hr />";
}
sub PrintSearch {
#present search form
print "\n\t\t\t<form id=\"search\" action=\"/cgi-bin/movies.pl\" method=\"get\" ENCTYPE=\"application/x-www-form-urlencoded\">\n\t\t\t<div><input type=\"hidden\" name=\"action\" value=\"search_results\" /></div>\n\t\t\t\t<table class=\"center\">";
print "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\"><a class=\"label\" id=\"search\">Search By:</a></td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"center\" class=\"label\">\n\t\t\t\t\t\t\t<select name=\"type\">\n\t\t\t\t\t\t\t\t<option value=\"movie\" selected>Movie Title\n\t\t\t\t\t\t\t\t<option value=\"actor\">Actor Name\n\t\t\t\t\t\t\t\t<option value=\"genre\">Genre\n\t\t\t\t\t\t\t\t<option value=\"year\">Year Released\n\t\t\t\t\t\t\t</select>\n\t\t\t\t\t\t\t</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"left\"><input type=\"text\" name=\"value\" maxlength=50 size=30 /></td>\n\t\t\t\t\t</tr>";
print "\n\t\t\t\t</table>\n\t\t\t\t<p><input type=\"submit\" value=\"Search\" /></p>\n\t\t\t</form>";
}
sub PrintAdd {
my $i;
#present a form for adding a new movie to the database
print "\n\t\t\t<form id=\"add_movie\" action=\"/cgi-bin/movies.pl\" method=\"get\" ENCTYPE=\"application/x-www-form-urlencoded\">";
print "\n\t\t\t\t<div><input type=\"hidden\" name=\"action\" value=\"add\" /></div>";
print "\n\t\t\t\t<div><input type=\"hidden\" name=\"insert\" value=\"1\" /></div>";
print "\n\t\t\t\t<table class=\"center\">";
print "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\"><a id=\"search\" class=\"label\">Add Movie:</a></td>\n\t\t\t\t\t\t<td class=\"label\" valign=\"middle\" align=\"right\">Movie <u>T</u>itle:</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"left\"><input type=\"text\" name=\"title\" maxlength=50 size=30 accesskey=\"t\" /></td>\n\t\t\t\t\t</tr>";
print "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\">&nbsp;</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\"><u>Y</u>ear:</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"left\"><input type=\"text\" name=\"year\" maxlength=4 size=6 accesskey=\"y\" /></td>\n\t\t\t\t\t</tr>";
print "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\">&nbsp;</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\"><u>G</u>enre:</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"left\"><input type=\"text\" name=\"genre\" maxlength=20 size=20 accesskey=\"g\" /></td>\n\t\t\t\t\t</tr>";
print "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\">&nbsp;</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\">R<u>u</u>ntime:</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"left\"><input type=\"text\" name=\"runtime\" maxlength=8 size=10 value=\"00:00:00\" accesskey=\"u\" /></td>\n\t\t\t\t\t</tr>";
print "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\">&nbsp;</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\">MPAA <u>R</u>ating:</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"left\"><input type=\"text\" name=\"mpaa_rating\" maxlength=5 size=6 accesskey=\"r\" /></td>\n\t\t\t\t\t</tr>";
print "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\">&nbsp;</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\"><u>I</u>MDB Rating:</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"left\"><input type=\"text\" name=\"imdb_rating\" maxlength=5 size=6 accesskey=\"i\" /></td>\n\t\t\t\t\t</tr>";
print "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\">&nbsp;</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\"><u>D</u>escription:</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"left\"><textarea type=\"text\" name=\"description\" cols=30 rows=5 accesskey=\"d\"></textarea></td>\n\t\t\t\t\t</tr>";
print "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\">&nbsp;</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\"><u>A</u>ctors:</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"left\"><input type=\"text\" name=\"actor1\" maxlength=50 size=30 accesskey=\"a\" /></td>\n\t\t\t\t\t</tr>";
for ($i=2; $i <= 10; $i++) {
print "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\">&nbsp;</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\">&nbsp;</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"left\"><input type=\"text\" name=\"actor".$i."\" maxlength=50 size=30 /></td>\n\t\t\t\t\t</tr>";
}
print "\n\t\t\t\t</table>\n\t\t\t\t<p><input type=\"submit\" value=\"Add Movie\" /></p>\n\t\t\t</form>";
}
sub PrintEdit {
my ($id,$title,$genre,$year,$runtime,$mpaa,$imdb,$description) = @_;
#present a form for editing a movie in the database
print "\n\t\t\t<form id=\"add_movie\" action=\"/cgi-bin/movies.pl\" method=\"get\" ENCTYPE=\"application/x-www-form-urlencoded\">";
print "\n\t\t\t\t<div><input type=\"hidden\" name=\"action\" value=\"edit\" /></div>";
print "\n\t\t\t\t<div><input type=\"hidden\" name=\"update\" value=\"1\" /></div>";
print "\n\t\t\t\t<div><input type=\"hidden\" name=\"id\" value=\"".$id."\" /></div>";
print "\n\t\t\t\t<table class=\"center\">";
print "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\"><a id=\"search\" class=\"label\">Edit Movie:</a></td>\n\t\t\t\t\t\t<td class=\"label\" valign=\"middle\" align=\"right\">Movie <u>T</u>itle:</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"left\"><input type=\"text\" name=\"title\" maxlength=50 size=30 accesskey=\"t\" value=\"".$title."\" /></td>\n\t\t\t\t\t</tr>";
print "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\">&nbsp;</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\"><u>Y</u>ear:</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"left\"><input type=\"text\" name=\"year\" maxlength=4 size=6 accesskey=\"y\" value=\"".$year."\" /></td>\n\t\t\t\t\t</tr>";
print "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\">&nbsp;</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\"><u>G</u>enre:</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"left\"><input type=\"text\" name=\"genre\" maxlength=20 size=20 accesskey=\"g\" value=\"".$genre."\" /></td>\n\t\t\t\t\t</tr>";
print "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\">&nbsp;</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\">R<u>u</u>ntime:</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"left\"><input type=\"text\" name=\"runtime\" maxlength=8 size=10 accesskey=\"u\" value=\"".$runtime."\" /></td>\n\t\t\t\t\t</tr>";
print "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\">&nbsp;</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\">MPAA <u>R</u>ating:</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"left\"><input type=\"text\" name=\"mpaa_rating\" maxlength=5 size=6 accesskey=\"r\" value=\"".$mpaa."\" /></td>\n\t\t\t\t\t</tr>";
print "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\">&nbsp;</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\"><u>I</u>MDB Rating:</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"left\"><input type=\"text\" name=\"imdb_rating\" maxlength=5 size=6 accesskey=\"i\" value=\"".$imdb."\" /></td>\n\t\t\t\t\t</tr>";
print "\n\t\t\t\t\t<tr>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\">&nbsp;</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"right\"><u>D</u>escription:</td>\n\t\t\t\t\t\t<td valign=\"middle\" align=\"left\"><textarea type=\"text\" name=\"description\" cols=30 rows=5 accesskey=\"d\">".$description."</textarea></td>\n\t\t\t\t\t</tr>";
print "\n\t\t\t\t</table>\n\t\t\t\t<p><input type=\"submit\" value=\"Apply Changes\" /></p>\n\t\t\t</form>";
}
sub PrintDetails {
my ($id) = @_;
my ($sql_query,$statement_handle,$result_handle);
my ($title,$year,$genre,$runtime,$mpaa,$imdb,$description);
$sql_query = "SELECT * FROM movies WHERE id='".$id."'";
$statement_handle = $database_handle->prepare($sql_query) || die "Couldn't prepare query '".$sql_query."': #".$database_handle->{'mysql_error'}."\n";
$statement_handle->execute() || die "Couldn't execute query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
while ($result_handle = $statement_handle->fetchrow_hashref()) {
$title = $result_handle->{'title'};
$genre= $result_handle->{'genre'};
$year = $result_handle->{'year'};
$runtime = $result_handle->{'runtime'};
$mpaa = $result_handle->{'mpaa_rating'};
$imdb = $result_handle->{'imdb_rating'};
$description = $result_handle->{'description'};
}
print "\n\t\t\t<table class=\"center\">\n\t\t\t\t";
print "<tr>\n\t\t\t\t\t<td class=\"list_left\" valign=\"middle\" align=\"right\" >Title:</td>\n\t\t\t\t\t<td class=\"list_right\" valign=\"middle\" align=\"left\" >$title</td>\n\t\t\t\t</tr>";
print "<tr>\n\t\t\t\t\t<td class=\"list_left\" valign=\"middle\" align=\"right\" >Genre:</td>\n\t\t\t\t\t<td class=\"list_right\" valign=\"middle\" align=\"left\" >$genre</td>\n\t\t\t\t</tr>";
print "<tr>\n\t\t\t\t\t<td class=\"list_left\" valign=\"middle\" align=\"right\" >Year Produced:</td>\n\t\t\t\t\t<td class=\"list_right\" valign=\"middle\" align=\"left\" >$year</td>\n\t\t\t\t</tr>";
print "<tr>\n\t\t\t\t\t<td class=\"list_left\" valign=\"middle\" align=\"right\" >Runtime:</td>\n\t\t\t\t\t<td class=\"list_right\" valign=\"middle\" align=\"left\" >$runtime</td>\n\t\t\t\t</tr>";
print "<tr>\n\t\t\t\t\t<td class=\"list_left\" valign=\"middle\" align=\"right\" >MPAA Rating:</td>\n\t\t\t\t\t<td class=\"list_right\" valign=\"middle\" align=\"left\" >$mpaa</td>\n\t\t\t\t</tr>";
print "<tr>\n\t\t\t\t\t<td class=\"list_left\" valign=\"middle\" align=\"right\" >IMDB Rating:</td>\n\t\t\t\t\t<td class=\"list_right\" valign=\"middle\" align=\"left\" >$imdb</td>\n\t\t\t\t</tr>";
print "<tr>\n\t\t\t\t\t<td class=\"list_left\" valign=\"middle\" align=\"right\" >Description:</td>\n\t\t\t\t\t<td class=\"list_right\" valign=\"middle\" align=\"left\" >$description</td>\n\t\t\t\t</tr>";
print "\n\t\t\t</table>";
}
sub PrintHomeLink {
print "\n\t\t\t<p><a href=\"/movies/\" class=\"underline\"><i>Home Page</i></a></p>\n\t\t</div>";
}
sub PrintFooter {
print "\n\t\t</div>\n\t\t<hr />\n\t\t<div class=\"logos\">\n\t\t\t<p><a href=\"http://validator.w3.org/check?uri=referer\"><img class=\"no_border\" src=\"/images/valid-xhtml11.png\" alt=\"Valid XHTML 1.1\" /></a><a href=\"http://jigsaw.w3.org/css-validator/\"><img class=\"no_border\" src=\"/images/vcss.png\" alt=\"Valid CSS\" height=\"31\" width=\"88\" /></a><a href=\"http://www.anybrowser.org/campaign/\"><img class=\"no_border\" src=\"/images/w3c-anybrowser.png\" alt=\"Valid in Any Browser\" height=\"31\" width=\"88\" /></a></p>\n\t\t\t<p><a href=\"http://use.perl.org/\"><img class=\"no_border\" src=\"/images/useperl.gif\" alt=\"Use PERL;\" /></a></p>\n\t\t\t<p>Real programmers can write assembly code in any language. :-) <br />&nbsp;&nbsp;&nbsp;&nbsp; --Larry Wall in &lt;8571\@jpl-devvax.JPL.NASA.GOV&gt;</p>\n\t\t</div>";
print "\n\t</body>\n</html>\n";
}
#=============================
# Database
sub AuditNewRecord {
my ($table,$row_id) = @_;
my ($sql_query, $statement_handle);
#global $user_id; #this value will be set into the user_id filed once user authentication and tracking is built
$sql_query = "INSERT INTO audit SET date=CURDATE(), time=CURTIME(), table_name='".$table."', row_id='".$row_id."', field_name='', old_value='', user_id=''";
$database_handle->do($sql_query) || die "Couldn't do query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
}
sub UpdateRecord {
my ($table,$field_name,$row_id,$newval) = @_;
my ($sql_query,$statement_handle);
my $result_handle;
#global $user_id; #this value will be set into the user_id filed once user authentication and tracking is built
$sql_query = "SELECT ".$field_name." FROM ".$table." WHERE ID='".$row_id."'";
$statement_handle = $database_handle->prepare($sql_query) || die "Couldn't prepare query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$statement_handle->execute() || die "Couldn't execute query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$result_handle = $statement_handle->fetchrow_hashref();
if($result_handle->{$field_name} ne $newval) {
$sql_query = "UPDATE ".$table." SET ".$field_name."='".$newval."' WHERE ID='".$row_id."'";
$database_handle->do($sql_query) || die "Couldn't do query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$sql_query = "INSERT INTO audit SET date=CURDATE(), time=CURTIME(), table_name='".$table."', row_id='".$row_id."', field_name='".$field_name."', old_value='".$result_handle->{$field_name}."'";
$database_handle->do($sql_query) || die "Couldn't do query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
}
}
#============================
# Movies
sub GetMovieById { #returns record hash for movie
my $movie_id = shift;
my ($sql_query,$statement_handle,$rows,$result_handle);
$sql_query = "SELECT * FROM movies WHERE id='".$movie_id."'";
$statement_handle = $database_handle->prepare($sql_query) || die "Couldn't prepare query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$statement_handle->execute() || die "Couldn't execute query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
if ($rows = $statement_handle->rows()) {
$result_handle = $statement_handle->fetchrow_hashref();
return($result_handle);
} else {
return(0);
}
}
sub GetMovieByTitle { #returns record hash for movie
my $title = shift;
my ($sql_query,$statement_handle,$rows,$result_handle);
$sql_query = "SELECT * FROM movies WHERE title='".$title."'";
$statement_handle = $database_handle->prepare($sql_query) || die "Couldn't prepare query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$statement_handle->execute() || die "Couldn't execute query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
if ($rows = $statement_handle->rows()) {
$result_handle = $statement_handle->fetchrow_hashref();
return($result_handle);
} else {
return(0);
}
}
sub GetMoviesByActorName { #returns a list of movie IDs
my $actor_name = shift;
my %actor = GetActorByName($actor_name);
my ($sql_query,$statement_handle,$rows,$result_handle);
$sql_query = "SELECT * FROM cast WHERE actor_id='".$actor{'id'}."'";
$statement_handle = $database_handle->prepare($sql_query) || die "Couldn't prepare query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$statement_handle->execute() || die "Couldn't execute query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
if ($rows = $statement_handle->rows()) {
$result_handle = $statement_handle->fetchrow_hashref();
return($result_handle);
} else {
return(0);
}
}
sub GetMoviesByActorId {
my $movie_id = shift;
my ($sql_query,$statement_handle,$rows,$result_handle);
$sql_query = "SELECT * FROM movies WHERE ID='".$movie_id."'";
$statement_handle = $database_handle->prepare($sql_query) || die "Couldn't prepare query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$statement_handle->execute() || die "Couldn't execute query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
if ($rows = $statement_handle->rows()) {
$result_handle = $statement_handle->fetchrow_hashref();
return($result_handle);
} else {
return(0);
}
}
sub DeleteMovie {
my $row_id = shift;
UpdateRecord("movies","deleted",$row_id,1);
}
#============================
# Actors
sub AddNewActor {
my ($actor_name) = shift;
my ($sql_query,$statement_handle,$newactorid);
#global $user_id; #this value will be set into the user_id filed once user authentication and tracking is built
$sql_query = "INSERT INTO actors SET name='".$actor_name."'";
$statement_handle = $database_handle->prepare($sql_query) || die "Couldn't prepare query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$statement_handle->execute() || die "Couldn't execute query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$newactorid = $database_handle->{'mysql_insertid'};
#print "\nDEBUG: New Actor ID is \"".$newactorid."\" from \"Add New Actor\".\n";
AuditNewRecord("actors",$newactorid);
}
sub GetActorByID {
my $movie_id = shift;
my ($sql_query,$statement_handle,$rows,$result_handle);
$sql_query = "SELECT * FROM movies WHERE id='".$movie_id."'";
$statement_handle = $database_handle->prepare($sql_query) || die "Couldn't prepare query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$statement_handle->execute() || die "Couldn't execute query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
if ($rows = $statement_handle->rows()) {
$result_handle = $statement_handle->fetchrow_hashref();
return($result_handle);
} else {
return(0);
}
}
sub GetActorByName {
my $actor_name = shift;
my ($sql_query,$statement_handle,$rows,$result_handle);
$sql_query = "SELECT * FROM actors WHERE name='".$actor_name."'";
$statement_handle = $database_handle->prepare($sql_query) || die "Couldn't prepare query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$statement_handle->execute() || die "Couldn't execute query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
if ($rows = $statement_handle->rows()) {
$result_handle = $statement_handle->fetchrow_hashref();
#print "\nDEBUG: Actor's Name is: \"".$result_handle->{'name'}."\" from \"Get Actor By Name\".\n";
return($result_handle);
} else {
#print "\nDEBUG: Returning 'False' from \"Get Actor By Name\". \n";
return(0);
}
}
sub DeleteActor {
my $row_id = shift;
UpdateRecord("actors","deleted",$row_id,1);
}
#============================
# Cast
sub AddToCast {
my ($actor_id, $movie_id) = @_;
my ($sql_query,$statement_handle,$id);
$sql_query = "INSERT INTO cast SET actor_id='".$actor_id."', movie_id='".$movie_id."'";
$statement_handle = $database_handle->prepare($sql_query) || die "Couldn't prepare query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$statement_handle->execute() || die "Couldn't execute query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$id = $actor_id."-".$movie_id;
#print "\nDEBUG: Added \"".$id."\" from \"Add To Cast\". \n";
AuditNewRecord("cast",$id);
}
#============================
# MAIN
#Print HTML header line
print header;
#Create add'l broswe options fromv "browse" page:
#genre
#actors
#year(s)[range of years]
#director
#producer
#
#
if(param("action") eq "browse") {
my ($result_handle, $movie_id, $title);
my ($sql_query,$statement_handle);
PrintHeader();
PrintPageTitle('Complete Movie List');
PrintNavBar();
$sql_query = "SELECT * FROM movies WHERE (deleted IS NULL OR deleted='0')";
$statement_handle = $database_handle->prepare($sql_query) || die "Couldn't prepare query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$statement_handle->execute() || die "Couldn't execute query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
print "\n\t\t\t<table class=\"center\">";
while ($result_handle = $statement_handle->fetchrow_hashref()) {
$movie_id = $result_handle->{'id'};
$title = $result_handle->{'title'};
print "\n\t\t\t\t<tr>";
print "\n\t\t\t\t\t<td class=\"list_left\" valign=\"middle\" align=\"right\" >".$title."</td>\n\t\t\t\t\t<td class=\"list_right\" valign=\"middle\" align=\"left\" >[ <a href=\"/cgi-bin/movies.pl?action=details&amp;id=".$movie_id."\">details</a> | <a href=\"/cgi-bin/movies.pl?action&equalsedit&amp;id=".$movie_id."\">edit</a> | <a href=\"/cgi-bin/movies.pl?action=delete&amp;id&euqals;".$movie_id."\">delete</a> ]</td>";
print "\n\t\t\t\t</tr>";
}
print "\n\t\t\t</table>";
PrintHomeLink();
PrintFooter();
} elsif(param("action") eq "search") {
PrintHeader();
PrintPageTitle("Search Page");
PrintNavBar();
# Include a pertial text search:
# Radio Buttoms for "Exact match"; "Begins with..."; Ends with..." or "Contains..."
# SECLECT * FROM [Table] WHERE [Field] LIKE '%[contains value]%'
#
# SECLECT * FROM [Table] WHERE [Field] LIKE '[beginning value]%'
#
# SECLECT * FROM [Table] WHERE [Field] LIKE '%[ending value]'
#
# SECLECT * FROM [Table] WHERE [Field] = '[exact value]'
#
#
# Search by alphabet:
# SECLECT * FROM [Table] WHERE [Field] LIKE 'a%'
#
# SECLECT * FROM [Table] WHERE [Field] LIKE 'b%'
#
# SECLECT * FROM [Table] WHERE [Field] LIKE 'c%'
#
PrintSearch();
PrintHomeLink();
PrintFooter();
} elsif(param("action") eq "search_results") {
#print "\nDEBUG: Action = '".param("action")."' from 'search_results' section. [line 316]\n";
#print "\nDEBUG: Type = '".param("type")."' from 'search_results' section. [line 316]\n";
#print "\nDEBUG: Value = '".param("value")."' from 'search_results' section. [line 317]\n";
if (param("value") eq "" or param("value") eq " ") { #if "value" is empty
my $type;
PrintHeader();
PrintPageTitle('Search Page');
PrintNavBar();
if (param("type") eq "movie") {
$type = "Movie Title"
} elsif (param("type") eq "actor") {
$type = "Actor Name"
} elsif (param("type") eq "year") {
$type = "Year Released"
} elsif (param("type") eq "genre") {
$type = "Genre"
}
print "\n\t\t\<span class=\"status_error\">The search value for \"".$type."\" cannot be blank.</span><br /><br />";
PrintSearch();
PrintHomeLink();
PrintFooter();
} else { #if "value" is not empty
my ($sql_query,$statement_handle,$result_handle,$rh,$sth,$sql);
my ($actorid,$movieid,$value,$title);
$value = join ' ', map { ucfirst lc } split / /, param("value");
PrintHeader();
PrintPageTitle("Search Results for ".$value);
PrintNavBar();
print "\n\t\t\t<table class=\"center\">";
if (param("type") eq "movie") {
#title case the title to match entry in database
$value=~s/([\w']+)/\u\L$1/g;
$value=~s/(\sa |an |the |and |if |then |else |when |up |at |from |by |on |at |for |is |in |of |to \s)/\L$1/ig;
$value=~s/^(\w)/\u$1/;
$sql_query = "SELECT * FROM movies WHERE title='".$value."' AND (deleted IS NULL OR deleted='0')";
$statement_handle = $database_handle->prepare($sql_query) || die "Couldn't prepare query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$statement_handle->execute() || die "Couldn't execute query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
if ($statement_handle->rows()) { #if result found
while ($result_handle = $statement_handle->fetchrow_hashref()) {
$movieid = $result_handle->{'id'};
$title = $result_handle->{'title'};
print "\n\t\t\t\t<tr>";
print "\n\t\t\t\t\t<td class=\"list_left\" valign=\"middle\" align=\"right\" >".$title."</td>\n\t\t\t\t\t<td class=\"list_right\" valign=\"middle\" align=\"left\" >[ <a href=\"/cgi-bin/movies.pl?action=details&amp;id=".$movieid."\">details</a> | <a href=\"/cgi-bin/movies.pl?action=edit&amp;id=".$movieid."\">edit</a> | <a href=\"/cgi-bin/movies.pl?action=delete&amp;id=".$movieid."\">delete</a> ]</td>";
print "\n\t\t\t\t</tr>";
}
}else{ #if no result
print "\n\t\t\t<h2 class=\"status_error\">No Search Results for ".$value."</h2>";
}#endif for "title"
} elsif (param("type") eq "actor") {
#get actor id from database
$sql_query = "SELECT id FROM actors WHERE name='".$value."' AND (deleted IS NULL OR deleted='0')";
$statement_handle = $database_handle->prepare($sql_query) || die "Couldn't prepare query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$statement_handle->execute() || die "Couldn't execute query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$result_handle = $statement_handle->fetchrow_hashref();
if ($statement_handle->rows()) { #if result found
$actorid = $result_handle->{'id'};
#get movie ids for this actor id
$sql_query = "SELECT movie_id FROM cast WHERE actor_id='".$actorid."' AND (deleted IS NULL OR deleted='0')";
$statement_handle = $database_handle->prepare($sql_query) || die "Couldn't prepare query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$statement_handle->execute() || die "Couldn't execute query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
if ($statement_handle->rows()) { #if result found
while ($result_handle = $statement_handle->fetchrow_hashref()) {
$movieid = $result_handle->{'movie_id'};
$sql = "SELECT * FROM movies WHERE id='".$movieid."' AND (deleted IS NULL OR deleted='0')";
$sth = $database_handle->prepare($sql) || die "Couldn't prepare query '".$sql."': ".$database_handle->{'mysql_error'}."\n";
$sth->execute() || die "Couldn't execute query '".$sql."': ".$database_handle->{'mysql_error'}."\n";
if ($statement_handle->rows()) { #if result found
while ($rh = $sth->fetchrow_hashref()) {
$movieid = $rh->{'id'};
$title = $rh->{'title'};
print "\n\t\t\t\t<tr>";
print "\n\t\t\t\t\t<td class=\"list_left\" valign=\"middle\" align=\"right\" >".$title."</td>\n\t\t\t\t\t<td class=\"list_right\" valign=\"middle\" align=\"left\" >[ <a href=\"/cgi-bin/movies.pl?action=details&amp;id=".$movieid."\">details</a> | <a href=\"/cgi-bin/movies.pl?action=edit&amp;id=".$movieid."\">edit</a> | <a href=\"/cgi-bin/movies.pl?action=delete&amp;id=".$movieid."\">delete</a> ]</td>";
print "\n\t\t\t\t</tr>";
}#end while for movie_id from movie
}else{
print "\n\t\t\t<h2 class=\"status_error\">No Search Results for Movie ID:".$movieid."</h2>";
}#end if for movie search results from movies
}#end while for movie_id from cast
}else{
print "\n\t\t\t<h2 class=\"status_error\">No Search Results for ".$value.". Not found in cast list for current movies in database.</h2>";
}#end if for search results for actor_id from cast
}else{
print "\n\t\t\t<h2 class=\"status_error\">No Search Results for ".$value.". Actor not found in database.</h2>";
}#end if for search results for actor name from actors
} elsif (param("type") eq "year") {
$sql_query = "SELECT * FROM movies WHERE year='".$value."' AND (deleted IS NULL OR deleted='0')";
$statement_handle = $database_handle->prepare($sql_query) || die "Couldn't prepare query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$statement_handle->execute() || die "Couldn't execute query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
if ($statement_handle->rows()) { #if result found
while ($result_handle = $statement_handle->fetchrow_hashref()) {
$movieid = $result_handle->{'id'};
$title = $result_handle->{'title'};
print "\n\t\t\t\t<tr>";
print "\n\t\t\t\t\t<td class=\"list_left\" valign=\"middle\" align=\"right\" >".$title."</td>\n\t\t\t\t\t<td class=\"list_right\" valign=\"middle\" align=\"left\" >[ <a href=\"/cgi-bin/movies.pl?action=details&amp;id=".$movieid."\">details</a> | <a href=\"/cgi-bin/movies.pl?action=edit&amp;id=".$movieid."\">edit</a> | <a href=\"/cgi-bin/movies.pl?action=delete&amp;id=".$movieid."\">delete</a> ]</td>";
print "\n\t\t\t\t</tr>";
}
}else{ #if no result
print "\n\t\t\t<h2 class=\"status_error\">No Search Results for ".$value."</h2>";
}#endif for "title"
} elsif (param("type") eq "genre") {
my $value = join ' ', map { ucfirst lc } split / /, param("value");
$sql_query = "SELECT * FROM movies WHERE genre='".$value."' AND (deleted IS NULL OR deleted='0')";
$statement_handle = $database_handle->prepare($sql_query) || die "Couldn't prepare query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$statement_handle->execute() || die "Couldn't execute query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
if ($statement_handle->rows()) { #if result found
while ($result_handle = $statement_handle->fetchrow_hashref()) {
$movieid = $result_handle->{'id'};
$title = $result_handle->{'title'};
print "\n\t\t\t\t<tr>";
print "\n\t\t\t\t\t<td class=\"list_left\" valign=\"middle\" align=\"right\" >".$title."</td>\n\t\t\t\t\t<td class=\"list_right\" valign=\"middle\" align=\"left\" >[ <a href=\"/cgi-bin/movies.pl?action=details&amp;id=".$movieid."\">details</a> | <a href=\"/cgi-bin/movies.pl?action=edit&amp;id=".$movieid."\">edit</a> | <a href=\"/cgi-bin/movies.pl?action=delete&amp;id=".$movieid."\">delete</a> ]</td>";
print "\n\t\t\t\t</tr>";
}
}else{ #if no result
print "\n\t\t\t<h2 class=\"status_error\">No Search Results for ".$value."</h2>";
}#endif for "title"
}#endif for "type"
print "\n\t\t\t</table>";
PrintHomeLink();
PrintFooter();
}#endif for "value"
} elsif(param("action") eq "details") {
my ($id, $sql_query,$statement_handle,$result_handle);
PrintHeader;
PrintPageTitle("Movie Details");
PrintNavBar();
# get one movie and show all the data about this one movie.
$id = param("id");
PrintDetails($id);
PrintHomeLink();
PrintFooter();
} elsif(param("action") eq "add") {
if (param("insert") == 1) {
# create the database record
my ($sql_query,$statement_handle);
my ($i, $actor,$actor_record_handle,$movie_record_handle,$newmovieid,$actorid);
#movie varables
# using map function to apply ucfirst and lc to all words (determined by split) from params
my $title = join ' ', map { ucfirst lc } split / /, param("title");
my $year = param("year");
my $genre = join ' ', map { ucfirst lc } split / /, param("genre");
my $runtime = param("runtime");
my $mpaa_rating = uc(param("mpaa_rating"));
my $imdb_rating = param("imdb_rating");
my $description = param("description");
#title case the title
$title=~s/([\w']+)/\u\L$1/g;
$title=~s/(\sa |an |the |and |if |then |else |when |up |at |from |by |on |at |for |is |in |of |to \s)/\L$1/ig;
$title=~s/^(\w)/\u$1/;
#add movie to database
$sql_query = "INSERT INTO movies SET title='".$title."', genre='".$genre."', year='".$year."', runtime='".$runtime."', mpaa_rating='".$mpaa_rating."', imdb_rating='".$imdb_rating."', description='".$description."'";
$statement_handle = $database_handle->prepare($sql_query) || die "Couldn't prepare query '".$sql_query."': #".$database_handle->{'mysql_error'}."\n";
$statement_handle->execute() || die "Couldn't execute query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$newmovieid = $database_handle->{'mysql_insertid'};
AuditNewRecord("movies",$newmovieid);
#now search the actor fields
for ($i = 1;$i <= 10 ; $i++) {
#pull datafrom field and set to proper case
$actor = join ' ', map { ucfirst lc } split / /, param("actor".$i);
#check for a name in the current field and run following code only if there is a name
unless ($actor eq "") {
#search for existing actor in the database
#print "\nDEBUG: Getting actor_record_handle from \"Get Actor By Name\" for \"".$actor."\" actor#".$i.".[add section]\n";
$actor_record_handle = GetActorByName($actor);
#If no existing actor, then add the actor to the database and get the id
#Get the id for existing actor
#print "\nDEBUG: Getting actor_record_handle \"".$actor_record_handle."\" from \"Get Actor By Name\" for \"".$actor."\" actor#".$i.".[add section]\n";
if ($actor_record_handle == 0) {
#print "\nDEBUG: Adding new actor and getting actorid from \"Add New Actor\" for \"".$actor."\" actor#".$i.".[add section]\n";
$actorid = AddNewActor($actor);
#print "DEBUG: Actor id = ".$actorid.". [add section]\n";
} else {
#print "DEBUG: Actor name is \"".$actor_record_handle->{'name'}."\" from \"Get Actor By Name\".[add section]\n";
#print "DEBUG: Actor id is \"".$actor_record_handle->{'id'}."\" from \"Get Actor By Name\".[add section]\n";
$actorid = $actor_record_handle->{'id'};
}
#add the actor to the movie's cast
AddToCast($actorid,$newmovieid);
}
}
$movie_record_handle = GetMovieById($newmovieid);
PrintHeader();
PrintPageTitle('Add Movie');
PrintNavBar();
print "\n\t\t\<span class=\"status_info\">Movie '".$movie_record_handle->{'title'}."' added.</span><br /><br />";
PrintAdd();
PrintHomeLink();
PrintFooter();
} else {
PrintHeader();
PrintPageTitle('Add Movie');
PrintNavBar();
PrintAdd();
PrintHomeLink();
PrintFooter();
}#endif
} elsif(param("action") eq "edit") {
my ($movie_id,$sql_query,$statement_handle,$result_handle);
PrintHeader();
#present a form for adding a new movie to the database
if (param('id')) {
if (param("update") == 1) {
$sql_query = "SELECT * FROM movies WHERE id='".param('id')."'";
$statement_handle = $database_handle->prepare($sql_query) || die "Couldn't prepare query '".$sql_query."': #".$database_handle->{'mysql_error'}."\n";
$statement_handle->execute() || die "Couldn't execute query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
while ($result_handle = $statement_handle->fetchrow_hashref()) {
unless ($result_handle->{'title'} eq param('title')) {
UpdateRecord("movies","title",$result_handle->{'id'},param('title'));
}
unless ($result_handle->{'genre'} eq param('genre')) {
UpdateRecord("movies","genre",$result_handle->{'id'},param('genre'));
}
unless ($result_handle->{'year'} eq param('year')) {
UpdateRecord("movies","year",$result_handle->{'id'},param('year'));
}
unless ($result_handle->{'runtime'} eq param('runtime')) {
UpdateRecord("movies","runtime",$result_handle->{'id'},param('runtime'));
}
unless ($result_handle->{'mpaa_rating'} eq param('mpaa_rating')) {
UpdateRecord("movies","mpaa_rating",$result_handle->{'id'},param('mpaa_rating'));
}
unless ($result_handle->{'imdb_rating'} eq param('imdb_rating')) {
UpdateRecord("movies","imdb_rating",$result_handle->{'id'},param('imdb_rating'));
}
unless ($result_handle->{'description'} eq param('description')) {
UpdateRecord("movies","description",$result_handle->{'id'},param('description'));
}
}
PrintPageTitle("Search Page");
PrintNavBar();
print "\n\t\t\t<h2 class=\"status_info\">Movie '".param('title')."' has been updated.</h2>";
PrintSearch();
PrintHomeLink();
PrintFooter();
}else{
PrintPageTitle("Edit Movie");
PrintNavBar();
my ($result_handle, $movie_id);
my ($sql_query,$statement_handle);
$movie_id = param('id');
$sql_query = "SELECT * FROM movies WHERE id='".$movie_id."'";
$statement_handle = $database_handle->prepare($sql_query) || die "Couldn't prepare query '".$sql_query."': #".$database_handle->{'mysql_error'}."\n";
$statement_handle->execute() || die "Couldn't execute query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
while ($result_handle = $statement_handle->fetchrow_hashref()) {
my ($id,$title,$genre,$year,$runtime,$mpaa,$imdb,$description);
$id = $result_handle->{"id"};
$title = $result_handle->{"title"};
$year = $result_handle->{"year"};
$genre = $result_handle->{"genre"};
$runtime = $result_handle->{"runtime"};
$mpaa = $result_handle->{"mpaa_rating"};
$imdb = $result_handle->{"imdb_rating"};
$description = $result_handle->{"description"};
PrintEdit($id,$title,$genre,$year,$runtime,$mpaa,$imdb,$description);
}
PrintHomeLink();
PrintFooter();
}#endif
#if no movie id, present a form selecting a movie from the database to delete
} else {
PrintPageTitle("Search Page");
PrintNavBar();
print "\n\t\t\t<h2 class=\"status_error\">Please search for the movie that you would like to edit.</h2>";
PrintSearch();
PrintHomeLink();
PrintFooter();
}
} elsif(param("action") eq "delete") {
my ($movie_id,$sql_query,$statement_handle,$result_handle);
PrintHeader;
#if we have the movie id, confirm delete
if (param('id')) {
if (param("delete") == 1) {
DeleteMovie(param('id'));
$sql_query = "SELECT * FROM movies WHERE id='".param('id')."'";
$statement_handle = $database_handle->prepare($sql_query) || die "Couldn't prepare query '".$sql_query."': #".$database_handle->{'mysql_error'}."\n";
$statement_handle->execute() || die "Couldn't execute query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
$result_handle = $statement_handle->fetchrow_hashref();
PrintPageTitle("Search Page");
PrintNavBar();
print "\n\t\t\t<h2 class=\"status_info\">'".$result_handle->{"title"}."' has been deleted.</h2>";
PrintSearch();
PrintHomeLink();
PrintFooter();
}else{
PrintPageTitle("Confirm Delete");
PrintNavBar();
my ($result_handle, $movie_id, $title);
my ($sql_query,$statement_handle);
$movie_id = param('id');
$sql_query = "SELECT * FROM movies WHERE id='".$movie_id."'";
$statement_handle = $database_handle->prepare($sql_query) || die "Couldn't prepare query '".$sql_query."': #".$database_handle->{'mysql_error'}."\n";
$statement_handle->execute() || die "Couldn't execute query '".$sql_query."': ".$database_handle->{'mysql_error'}."\n";
while ($result_handle = $statement_handle->fetchrow_hashref()) {
$title = $result_handle->{'title'};
print "\n\t\t\t<h2 class=\"status_error\">Are you sure that you want to delete '".$title."' from the database?</h2>\n\t\t\t<form id=\"add_movie\" action=\"/cgi-bin/movies.pl\" method=\"get\" ENCTYPE=\"application/x-www-form-urlencoded\">";
print "\n\t\t\t\t<div><input type=\"hidden\" name=\"action\" value=\"delete\" /></div>";
print "\n\t\t\t\t<div><input type=\"hidden\" name=\"delete\" value=\"1\" /></div>";
print "\n\t\t\t\t<div><input type=\"hidden\" name=\"id\" value=\"".$movie_id."\" /></div>";
print "<input type=\"submit\" value=\"Confirm Delete\" />";
}
PrintHomeLink();
PrintFooter();
}#endif
#if no movie id, present a form for selecting a movie from the database to delete
} else {
PrintPageTitle("Search Page");
PrintNavBar();
print "\n\t\t\t<h2 class=\"status_error\">Please search for the movie that you would like to delete.</h2>";
PrintSearch();
PrintHomeLink();
PrintFooter();
}
} else {
PrintHeader();
PrintHome();
PrintFooter();
}
$database_handle->disconnect();
#print redirect('http://www.karlwilbur.net/cgi-bin/movies.pl');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment