Some notes have been posted for the remainder of this week. 

Today we want to discuss: 

a) making server-side state programs immune to accidental reloads

b) setting up and querying a database, from PHP, Perl and Python

So let's start from a program we developed yesterday:

<? session_start(); 
if ($message) {  
  if ($answer == $n1 + $n2) { 
    $m1 = $m1 + 1;   
  } 
  $m2 = $m2 + 1; 
  $message = "The score is " . $m1 . " out of " . $m2 . " currently";
  $n1 = rand(-50, 50); 
  $n2 = rand(-50, 50); 
} else { 
  session_register("message"); 
  session_register("n1"); 
  session_register("n2"); 
  session_register("m1"); 
  session_register("m2"); 
  $message = "Welcome";
  $n1 = rand(-50, 50);
  $n2 = rand(-50, 50);
  $m1 = 0;
  $m2 = 0;
}
?> 

<form>
  <?=$message?> <p>  

  What is <?=$n1?> + <?=$n2?> ? 
  Answer: <input type=text name=answer size=4> <p>

  Press <input type="submit" value="Proceed"> to submit your 
answer. 

</form> 

Let's copy this program in ~/apache/htdocs/thu0702/six.php

This is the server side state version of the program.

Access it from

http://silo.cs.indiana.edu:44063/thu0702/six.php

Ideas:

 -- somehow delete your previous answer

 -- keep track how many times an answer has been sent

 -- name your submit button and check for it in the submission

 -- copy your url and compare it with the past 

So we end up with this code:

<? session_start(); 
if ($message && ! $reset) {  

if ($month == $monthClient) {

  if ($answer == $n1 + $n2) { 
    $m1 = $m1 + 1;   
  } 
  $m2 = $m2 + 1; 
  $message = "The score is " . $m1 . " out of " . $m2 . " currently";
  $n1 = rand(-50, 50); 
  $n2 = rand(-50, 50); 

  $month += 1; 

} else {
  $message = "I can't allow reloads. Score still $m1 out of $m2";
}

} else { 
  session_register("month");
  $month = 1;
  session_register("message"); 
  session_register("n1"); 
  session_register("n2"); 
  session_register("m1"); 
  session_register("m2"); 
  $message = "Welcome";
  $n1 = rand(-50, 50);
  $n2 = rand(-50, 50);
  $m1 = 0;
  $m2 = 0;
}
?> 

<form>
  <?=$message?> <p>  

  What is <?=$n1?> + <?=$n2?> ? 
  Answer: <input type=text name=answer size=4> <p>

  <input type="hidden" name="monthClient" value="<?=$month?>"> 

  Press <input type="submit" name="stanley" value="Proceed"> to submit your answer. <p> 

  Press <input type="submit" name="reset" value="Reset"> to restart the game. 

</form> 

Now we want to do Homework Three. 

I start by going into /nobackup/username/mysql-5.0.22

We remember the files we developed there thus far: 

-bash-3.2$ ls -ld step*
-rwxr-xr-x 1 dgerman faculty  63 Jun 29 15:55 step006
-rwxr-xr-x 1 dgerman faculty 103 Jun 29 16:07 step007
-rwxr-xr-x 1 dgerman faculty 349 Jun 29 16:20 step008

-bash-3.2$ cat step008
/nobackup/dgerman/mysql/bin/mysqld_safe \
  --user=dgerman \
  --pid-file=/nobackup/dgerman/mysql/mysqld.pid \
  --log=/nobackup/dgerman/mysql/mysqld.log \
  --socket=/nobackup/dgerman/mysql/mysql.sock \
  --log-error=/nobackup/dgerman/mysqld-error.log \
  --basedir=/nobackup/dgerman/mysql \
  --datadir=/nobackup/dgerman/mysql \
  --port=44089 &

-bash-3.2$

If your MySQL server is not running start it. 

Try to see if it's running with ps -ef | grep username. 

If it is not running please start it: ./step008 and hit Enter. 

Verify that it's running with ps -ef | grep username then go in.

In your /nobackup/username/mysql-5.0.22 create a file connect_asroot.

pico connect_asroot

then inside write:

mysql --socket=/nobackup/username/mysql/mysql.sock \
      --port=44yyy -u root -p

Change username to your username and the port to your MySQL port
also make sure there's no space after the backslash, then save and
exit and make the file executable and run it.

./connect_asroot

Hit Enter when prompted for password, you're in.

As root (super user) you will issue three commands:

create user '[username]'@'silo.cs.indiana.edu' IDENTIFIED BY '[password]';

I type edit, then copied this in and changed it, then exit, ;, Enter.

mysql> create user 'lbird'@'silo.cs.indiana.edu' IDENTIFIED BY 'sp00n';ERROR 1396 (HY000): Operation CREATE USER failed for 'lbird'@'silo.cs.indiana.edu'

mysql> drop user 'lbird'@'silo.cs.indiana.edu';
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'lbird'@'silo.cs.indiana.edu' IDENTIFIED BY 'sp00n';Query OK, 0 rows affected (0.00 sec)

Next we issue this command: 

create database homeworkThree

Finally give the user access: 

grant all on databaseName.* to 'username'@'silo.cs.indiana.edu';

Replace databaseName with homeworkThree and username with the one you chose.

For me this is:

grant all on homeworkThree.* to 'lbird'@'silo.cs.indiana.edu';

Now exit.

I create a file connect_aslbird with the contents: 

mysql --socket=/nobackup/dgerman/mysql/mysql.sock \
  --port=44089 --host=silo.cs.indiana.edu -u lbird -p

Save, make executable and run it to get in. 

-bash-3.2$ ls -ld conn*
-rw-r--r-- 1 dgerman faculty 107 Jul  2 18:39 connect_aslbird
-rwxr-xr-x 1 dgerman faculty  83 Jul  2 18:17 connect_asroot
-bash-3.2$ chmod +x connect_aslbird 
-bash-3.2$ ls -ld conn*
-rwxr-xr-x 1 dgerman faculty 107 Jul  2 18:39 connect_aslbird
-rwxr-xr-x 1 dgerman faculty  83 Jul  2 18:17 connect_asroot
-bash-3.2$ cat connect_aslbird 
mysql --socket=/nobackup/dgerman/mysql/mysql.sock \
  --port=44089 --host=silo.cs.indiana.edu -u lbird -p

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

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

mysql> use homeworkThree
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql>

Now we're as (lbird) ready to define a database.

Now type edit, Enter and in the empty editor window paste this: 

create table enrollment (
   customer varchar(25),
   matchid  varchar(30) ,
   tickets  decimal(4,0) ,
   primary key (customer, matchid)
)

Exit, save, and when you get the -> prompt type ; followed by Enter.

If you type show tables again you get enrollment.

Now type edit again, and paste this command in:

insert into enrollment values 
('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)

Exit, save, type ; at -> hit Enter. 

Run this query:

mysql> select * from enrollment where customer = 'Purdue';
+----------+---------+---------+
| customer | matchid | tickets |
+----------+---------+---------+
| Purdue   | dubi    |      80 | 
| Purdue   | miba    |     200 | 
| Purdue   | mibi    |      60 | 
| Purdue   | mijo    |     120 | 
+----------+---------+---------+
4 rows in set (0.00 sec)

If you get this result you're done. 

Exit. 

Go to ~/apache/htdocs/ and create homeworkThree.php: 

<?

       $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>"; 
       } 

 ?> 

Use pico -w homeworkThree.php to avoid broken lines. 

Then adjust it: 

<?

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

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

           $query = "select * from enrollment where customer = 'Purdue'"; 

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

 ?> 

Then save it and call it from:

http://silo.cs.indiana.edu:44063/homeworkThree.php

You get what you expect, what you saw in MySQL from the command line. 

--