Plan for today: 

a) build a database, query it

b) install PHP, introduce it 

1. Implementing the database we designed last time

I first check to see if my server is running:

  ps -ef | grep dgerman

I knew it wasn't, and I can see that, so I start it:

  /nobackup/dgerman/mysql-5.0.22/step008

Following instructions I've created a file step010: 

-bash-3.2$ ls -ld /nobackup/dgerman/mysql-5.0.22/step010
-rwxr--r-- 1 dgerman faculty 134 Feb  6 11:55 /nobackup/dgerman/mysql-5.0.22/step010

The contents looks like this, it sets the root password: 

-bash-3.2$ cat /nobackup/dgerman/mysql-5.0.22/step010
/nobackup/dgerman/mysql/bin/mysqladmin \
  --port=47066 \
  --socket=/nobackup/dgerman/mysql/mysql.sock \
  -u root password 'sp00n'

-bash-3.2$ 

I already run this file so my root password is what you see. 

I also have a file connect_asroot: 

-bash-3.2$ ls -ld /nobackup/dgerman/mysql-5.0.22/connect_asroot 
-rwxr--r-- 1 dgerman faculty 82 Feb  6 11:47 /nobackup/dgerman/mysql-5.0.22/connect_asroot

Its content allows me to get in as root: 

-bash-3.2$ cat /nobackup/dgerman/mysql-5.0.22/connect_asroot 
mysql --socket=/nobackup/dgerman/mysql/mysql.sock \
      --port=47066 -u root -p
-bash-3.2$

I was able to get in with no password originally, after step010 I am using the new password. 

I go in and type the following commands (also found in the .pdf text, customized here): 

a) create a new user: 

  create user 'lbird'@'silo.cs.indiana.edu' IDENTIFIED BY 'dribl';

b) create a new database: 

  create database awards;

c) I give lbird full rights to it: 

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

Now I need to exit as root and get in as lbird, work on the awards database. 

So I create a file called connect_aslbird: 

-bash-3.2$ ls -ld /nobackup/dgerman/mysql-5.0.22/connect_aslbird
-rwxr--r-- 1 dgerman faculty 106 Feb 10 13:55 /nobackup/dgerman/mysql-5.0.22/connect_aslbird

The contents (for me) is like this: 

-bash-3.2$ cat /nobackup/dgerman/mysql-5.0.22/connect_aslbird
mysql --socket=/nobackup/dgerman/mysql/mysql.sock \
  --port=47066 --host=silo.cs.indiana.edu -u lbird -p
-bash-3.2$

I run it and enter mysql as lbird. Once I'm in: 

a) I select the awards database: 

  use awards;

b) I take a look at what tables we have there (none): 

  show tables;

c) I create a table called performers: 

  create table performers (
    username char(8) primary key, 
    lastName char(32), 
    firstName char(32)  
  );

d) I take a look at the tables again, this time I see my table: 

  mysql> show tables;
  +------------------+
  | Tables_in_awards |
  +------------------+
  | performers       | 
  +------------------+
  1 row in set (0.00 sec)

e) I can also type describe performers to see the definition of the table

f) I then select all the records in performers and show them: 

  mysql> select * from performers;
  Empty set (0.00 sec)

Since the table has just been created it is empty (no records). 

g) I type edit and hit return, I get an editor window in which I type: 

  insert into performers 
    (firstName, lastName, username) 
  values
    ("Jordan",   "Crawford",  "jcrawfor"),
    ("D.J.",     "White",     "djwhite"),
    ("Deandre",  "Thomas",    "dthomas"), 
    ("A.J.",     "Ratliff",   "ajratlif"),
    ("Eric",     "Gordon",    "egordon"),
    ("Mike",     "White",     "mwhite"),  
    ("Armon",    "Bassett",   "abassett"),
    ("Jamarcus", "Ellis",     "jmellis"), 
    ("Lance",    "Stemler",   "lstemler");

Exit, save (leave name unchanged) type ; and then hit return. We have a table. 

h) Try the query at f) once again. 

i) Create a table of judges. 

  mysql> create table judges (username char(8) primary key, lastName char(32), firstName char(32));
  Query OK, 0 rows affected (0.01 sec)

  mysql> describe judges;
  +-----------+----------+------+-----+---------+-------+
  | Field     | Type     | Null | Key | Default | Extra |
  +-----------+----------+------+-----+---------+-------+
  | username  | char(8)  | NO   | PRI | NULL    |       | 
  | lastName  | char(32) | YES  |     | NULL    |       | 
  | firstName | char(32) | YES  |     | NULL    |       | 
  +-----------+----------+------+-----+---------+-------+
  3 rows in set (0.01 sec)

  mysql> insert into judges values ('lbird', 'Bird', 'Larry');
  Query OK, 1 row affected (0.00 sec)

  mysql> insert into judges values ('mjordan', 'Jordan', 'Michael'), ('tkukoc', 'Kukoc', 'Toni');
  Query OK, 2 rows affected (0.00 sec)
  Records: 2  Duplicates: 0  Warnings: 0

  mysql> select * from judges;
  +----------+----------+-----------+
  | username | lastName | firstName |
  +----------+----------+-----------+
  | lbird    | Bird     | Larry     | 
  | mjordan  | Jordan   | Michael   | 
  | tkukoc   | Kukoc    | Toni      | 
  +----------+----------+-----------+
  3 rows in set (0.00 sec)

  mysql> 

j) Create the table of scores. 

  create table ratings (
    player char(8) , 
    judge char(8), 
    rating int, 
    primary key (player, judge)
  ); 

  insert into ratings 
    (judge, player, rating) 
  values
    ('lbird'  , 'egordon' ,    9 ),
    ('lbird'  , 'djwhite' ,   10 ),
    ('lbird'  , 'dthomas' ,    7 ),
    ('lbird'  , 'ajratlif',    8 ),
    ('mjordan', 'egordon' ,   10 ),
    ('mjordan', 'mwhite'  ,    8 ),
    ('mjordan', 'abassett',    7 ),
    ('tkukoc' , 'jmellis' ,   10 ),
    ('tkukoc' , 'lstemler',    5 );

k) Run the following queries: 

 select all players and the number of votes they have received:

  mysql> select player, count(*) from ratings group by player;
  +----------+----------+
  | player   | count(*) |
  +----------+----------+
  | abassett |        1 | 
  | ajratlif |        1 | 
  | djwhite  |        1 | 
  | dthomas  |        1 | 
  | egordon  |        2 | 
  | jmellis  |        1 | 
  | lstemler |        1 | 
  | mwhite   |        1 | 
  +----------+----------+
  8 rows in set (0.00 sec)

  select all judges with how many votes they cast:

  mysql> select judge, count(*) from ratings group by judge;
  +---------+----------+
  | judge   | count(*) |
  +---------+----------+
  | lbird   |        4 | 
  | mjordan |        3 | 
  | tkukoc  |        2 | 
  +---------+----------+
  3 rows in set (0.00 sec)

  select the judges, and their average ratings:

  mysql> select judge, avg(rating) from ratings group by judge order by avg(rating) desc;
  +---------+-------------+
  | judge   | avg(rating) |
  +---------+-------------+
  | lbird   |      8.5000 | 
  | mjordan |      8.3333 | 
  | tkukoc  |      7.5000 | 
  +---------+-------------+
  3 rows in set (0.00 sec)

Read this: 

http://www.cs.indiana.edu/classes/a348/spr2008/notes/weekThree/three.html

Finally, install PHP and try the following: 

1. http://www.cs.indiana.edu/classes/a290-web/spr2008/caleb000.txt

The page above describes the creation of a simple database. 

The step below describes a PHP script that summarizes the contents of that database. 

2. http://www.cs.indiana.edu/classes/a290-web/spr2008/caleb001.txt

The script below is collecting data from the user and adding it to the database:

3. http://www.cs.indiana.edu/classes/a290-web/spr2008/caleb002.txt

We will develop a few more examples in class, in PHP. 

--