Go into MySQL and create a database as described in the notes for this assignment:

http://www.cs.indiana.edu/classes/a202/sum2009/HomeworkThree.html

Then exit, and create three programs: 

a) in cgi-bin:----------------------------------------------------------------------------------------------

#!/usr/bin/perl

use DBI; 
use CGI; 

$q = new CGI; 

print "Content-type: text/html\n\n"; 

$DB        = "dbi:mysql:demoOne:silo.cs.indiana.edu:port=16248"; 
$username  = "dgerman";             
$password  = "sp00n"; 

$dbh = DBI->connect($DB, 
                    $username, 
                    $password, {PrintError => 0}) || 
       die "Couldn't open database: ", $DBI::errstr; 

$query = "select movieNum, avg(rating), count(rating) from projectRatings group by movieNum order by avg(rating) desc;"; 

$sth = $dbh->prepare($query) || die $dbh->errstr;

$sth->execute() || die $sth->errstr;

print qq{
  <blockquote>
    <table border cellpadding=2>
      <tr><th> 
          <th> Movie Title 
          <th> Average Rating 
          <th> Number of Votes
  </blockquote> 
}; 

$i = 0; 

while (my $row = $sth->fetch) {
  my(@values) = @$row; 
  $i += 1; 
  print "<tr> <th> $i "; 
  foreach $value (@values) {
    print "<td align=center>$value"; 
  } 
  print "\n";
} 

print "</table>"; 

$sth->finish; 

b) in cgi-bin:----------------------------------------------------------------------------------------------------------------

#!/usr/bin/python

import MySQLdb

# Create a connection object and create a cursor
Con = MySQLdb.Connect(host="silo.cs.indiana.edu", port=14298, user="dgerman", passwd="sp00n", db="demoOne")
Cursor = Con.cursor()

# Make SQL string and execute it
sql = "SELECT * FROM students"
Cursor.execute(sql)

# Fetch all results from the cursor into a sequence and close the connection
Results = Cursor.fetchall()

print Results

Con.close()

c) in htdocs:----------------------------------------------------------------------------------------------------------------

<?

       $conn = mysql_connect('silo.cs.indiana.edu:16248', 'dgerman', 'sp00n');

       if ($conn) { 
         if (mysql_select_db("demoOne")) { 
           // echo "I can select the database. <p>"; 

           $query = "select movieNum, avg(rating) as ratings from projectRatings group by movieNum order by ratings desc "; 

           $result = @mysql_query($query); 
 ?>
<blockquote>
  <table border cellpadding=3>
    <tr><th>Movie <th>Average Rating
<?
           while ($row = mysql_fetch_row($result)) {
             echo "<tr><td align=center>" . $row[0] . "<td align=center>" . $row[1]; 
           } 
 ?>
  </table>
</blockquote>
<?
         } else { 
           echo "I can connect but I can't select the database. <p>"; 
         }
       } else {
         echo "I cannot connect. <p>"; 
       } 

 ?> 

---------------------------------------------------------------------------------------------------------------------------------

Your task is to modify these programs so that each one of them returns the answer:

Which player was the most watched player in the tournament? 

The answer for the query is summarized here: 

http://www.cs.indiana.edu/classes/a348/spr2009/0217-class.txt

Include the query in the scripts above by making the necessary modifications.