#!/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;