These are the notes for Fri 02/27/2009 A348/A548 lab. 

I am using the sample project notes posted today to write two scripts

http://www.cs.indiana.edu/classes/a348/fall2007/blog.html

that connect into the Homework Three database and extract the information
and post on the web. One of them will be in CGI/Perl and the other one in
PHP. Here are the direct links to the model scripts: 

http://www.cs.indiana.edu/classes/a290-web/proj004.html

Perl: 

http://www.cs.indiana.edu/classes/a290-web/fall2008/whatsnew/project/extract.txt

PHP: 

http://www.cs.indiana.edu/classes/a290-web/fall2008/whatsnew/project/extract.phps.txt

So now we need to get started. 

So I first choose a query: 

14. List all universities and the number of games they bought tickets to.

(How would you identify the universities that bought tickets to ALL/NONE of the games?) 

Here's the SQL for it: 

    select spectators.customer, count(tickets) 
    from spectators left join enrollment on spectators.customer = enrollment.customer 
    group by spectators.customer;

I first want to see this running if I type it in MySQL: 

-bash-3.2$ cd /nobackup/dgerman/mysql-5.0.22/
-bash-3.2$ ./connect_aslbird 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 111
Server version: 5.0.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use HomeworkThree
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------------+
| Tables_in_HomeworkThree |
+-------------------------+
| enrollment              | 
| matches                 | 
| participation           | 
| players                 | 
| spectators              | 
| venues                  | 
+-------------------------+
6 rows in set (0.00 sec)

mysql> edit
   UW PICO(tm) 4.10                             File: /tmp/sqlFRbkgJ                                 
Modified   

    select spectators.customer, count(tickets)
    from spectators left join enrollment on spectators.customer = enrollment.customer
    group by spectators.customer;

                                               [ Wrote 5 lines ]

    -> ;
+-----------+----------------+
| customer  | count(tickets) |
+-----------+----------------+
| Illinois  |              4 | 
| Indiana   |              4 | 
| Iowa      |              3 | 
| Michigan  |              3 | 
| Minnesota |              4 | 
| Purdue    |              4 | 
| UCLA      |              0 | 
+-----------+----------------+
7 rows in set (0.01 sec)

mysql> 

So now I am ready to make this available on-line. 

I have the query, now I need a template. I start with Perl: 

#!/usr/bin/perl

use DBI; 
use CGI; 

$q = new CGI; 

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

$DB        = "dbi:mysql:HomeworkThree:silo.cs.indiana.edu:port=47066"; 
$username  = "lbird";             
$password  = "dribl"; 

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

$query = qq{    
    select spectators.customer, count(tickets) 
    from spectators left join enrollment on spectators.customer = enrollment.customer 
    group by spectators.customer;
}; 

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

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

print qq{
  <blockquote>
    <table border cellpadding=2>
      <tr><th> 
          <th> Customer
          <th> Games Seen
  </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; 

I place this in /u/apache/cgi-bin/0227/query and I access it from the command
line and from the web. It works well and it prints: 

-bash-3.2$ ./query
Content-type: text/html


  <blockquote>
    <table border cellpadding=2>
      <tr><th> 
          <th> Customer
          <th> Games Seen
  </blockquote> 
<tr> <th> 1 <td align=center>Illinois<td align=center>4
<tr> <th> 2 <td align=center>Indiana<td align=center>4
<tr> <th> 3 <td align=center>Iowa<td align=center>3
<tr> <th> 4 <td align=center>Michigan<td align=center>3
<tr> <th> 5 <td align=center>Minnesota<td align=center>4
<tr> <th> 6 <td align=center>Purdue<td align=center>4
<tr> <th> 7 <td align=center>UCLA<td align=center>0
</table>-bash-3.2$ 

So now if I make some changes to the database:

mysql> show tables;
+-------------------------+
| Tables_in_HomeworkThree |
+-------------------------+
| enrollment              | 
| matches                 | 
| participation           | 
| players                 | 
| spectators              | 
| venues                  | 
+-------------------------+
6 rows in set (0.00 sec)

mysql> select * from spectators;
+-----------+---------+---------+------+-------+
| customer  | logourl | address | city | state |
+-----------+---------+---------+------+-------+
| Illinois  |         |         |      | IL    | 
| Iowa      |         |         |      | IO    | 
| Indiana   |         |         |      | IN    | 
| Michigan  |         |         |      | MI    | 
| Purdue    |         |         |      | IN    | 
| UCLA      |         |         |      | CA    | 
| Minnesota |         |         |      | MN    | 
+-----------+---------+---------+------+-------+
7 rows in set (0.02 sec)

mysql> insert into spectators (customer, state) values ('USC', 'CA'); 
Query OK, 1 row affected (0.03 sec)

mysql> select * from spectators;
+-----------+---------+---------+------+-------+
| customer  | logourl | address | city | state |
+-----------+---------+---------+------+-------+
| Illinois  |         |         |      | IL    | 
| Iowa      |         |         |      | IO    | 
| Indiana   |         |         |      | IN    | 
| Michigan  |         |         |      | MI    | 
| Purdue    |         |         |      | IN    | 
| UCLA      |         |         |      | CA    | 
| Minnesota |         |         |      | MN    | 
| USC       | NULL    | NULL    | NULL | CA    | 
+-----------+---------+---------+------+-------+
8 rows in set (0.00 sec)

mysql> select * from enrollment;
+-----------+---------+---------+
| customer  | matchid | tickets |
+-----------+---------+---------+
| Illinois  | dumi    |      40 | 
| Illinois  | joba    |     120 | 
| Illinois  | kuba    |     180 | 
| Illinois  | mibi    |     140 | 
| Iowa      | bijo    |     200 | 
| Iowa      | dubi    |     120 | 
| Iowa      | kubi    |     180 | 
| Indiana   | bijo    |     150 | 
| Indiana   | joba    |     100 | 
| Indiana   | joku    |      60 | 
| Indiana   | mijo    |     210 | 
| Michigan  | duba    |     300 | 
| Michigan  | joku    |     240 | 
| Michigan  | kudu    |     220 | 
| Purdue    | dubi    |      80 | 
| Purdue    | miba    |     200 | 
| Purdue    | mibi    |      60 | 
| Purdue    | mijo    |     120 | 
| Minnesota | duba    |      80 | 
| Minnesota | dumi    |     120 | 
| Minnesota | joku    |      60 | 
| Minnesota | kudu    |      20 | 
+-----------+---------+---------+
22 rows in set (0.00 sec)

mysql> insert into enrollment values ('USC', 'dumi', 120);
Query OK, 1 row affected (0.00 sec)

mysql> insert into enrollment values ('USC', 'joba', 20);
Query OK, 1 row affected (0.01 sec)

mysql>

... the script actually produces this output now: 

-bash-3.2$ ./query
Content-type: text/html


  <blockquote>
    <table border cellpadding=2>
      <tr><th> 
          <th> Customer
          <th> Games Seen
  </blockquote> 
<tr> <th> 1 <td align=center>Illinois<td align=center>4
<tr> <th> 2 <td align=center>Indiana<td align=center>4
<tr> <th> 3 <td align=center>Iowa<td align=center>3
<tr> <th> 4 <td align=center>Michigan<td align=center>3
<tr> <th> 5 <td align=center>Minnesota<td align=center>4
<tr> <th> 6 <td align=center>Purdue<td align=center>4
<tr> <th> 7 <td align=center>UCLA<td align=center>0
<tr> <th> 8 <td align=center>USC<td align=center>2
</table>-bash-3.2$ 

So changes in the database are immediately reflected in the query output. 

Now let's do it in PHP: 

1. Choose a query: 

I choose 31. List the standings after the fourth round. 

2. Play with the query: 

-bash-3.2$ pwd
/nobackup/dgerman/mysql-5.0.22
-bash-3.2$ ./connect_aslbird 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 137
Server version: 5.0.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use HomeworkThree
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------------+
| Tables_in_HomeworkThree |
+-------------------------+
| enrollment              | 
| matches                 | 
| participation           | 
| players                 | 
| spectators              | 
| venues                  | 
+-------------------------+
6 rows in set (0.00 sec)

mysql> edit
   UW PICO(tm) 4.10                        File: /tmp/sql6D4x1U                            
Modified   

select username, sum(points) 
from participation inner join matches on matches.matchid = participation.matchid 
 and round in ('round one', 'round two', 'round three', 'round four')    
group by username 
order by sum(points) desc;
                                          [ Wrote 6 lines ]

    -> ;
+----------+-------------+
| username | sum(points) |
+----------+-------------+
| lbird    |           4 | 
| mjordan  |         2.5 | 
| rmiller  |         1.5 | 
| tduncan  |         1.5 | 
| tkukoc   |         1.5 | 
| cbarkley |           1 | 
+----------+-------------+
6 rows in set (0.06 sec)

mysql> 

3. Write the PHP to execute this query, in /u/[username]/apache/htdocs/0227/one.php this file:

<?

       $conn = mysql_connect('silo.cs.indiana.edu:47066', 'lbird', 'dribl');

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

           $query = "select username, sum(points) from participation inner join matches on matches.matchid = participation.matchid and round in ('round one', 'round two', 'round three', 'round four') group by username order by sum(points) desc;"; 

           $result = @mysql_query($query); 
 ?>
<blockquote>
  <table border cellpadding=3>
    <tr><th> <th>Player <th>No. of Points
<?
           $i = 0; 
           while ($row = mysql_fetch_row($result)) {
             $i += 1; 
             echo "<tr><th> $i. <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>"; 
       } 

 ?> 

End of lab.