Homework 5: MySQL and PHP magic

Part 1:MySQL

1. Install MySQL: For a personal installation, you may find this page useful: Installation Guide or Official MySQL installation guide . If you would rather not deal with installation dependencies and such, you may just want to use your OCF account. The OCF already has MySQL and PHP installed. Just type 'makemysql' command at the command line. For help, refer here .
2. Load your database: When you have mysql loaded up so what you see is: "mysql> ", type 'use dbname' in order to load your db.
3. Create a table: You can have multiple tables within a db so that you can link certain fields to each other. By having this linkage, you can run useful queries. For example, if you had a friends table with all of the contact info for your friends, and then had a classmates table with a list of all the people that you have class with, you can then run a query to find out which of your friends share the same classes with you. In order to create this functionality, you just need to specify certain attributes when you create a table. The primary source would have the attribute of being a primary key,and the table that links to the source will have the attribute of being a foreign key . For example, in our case, the name field of your friend in the friends table would be a primary key, and the name field of your friend in the classmates table would just be a foreign key that refers to the primary key in your friends table.
   CREATE TABLE tblname (columnName columnType, columnName columnType, ...);
   If you wanted to use the primary and foreign key attributes, just write it after all of your column declarations. Here are some examples of how I setup the db for this class:
   CREATE TABLE students (sid int, name char(30), email char(50), rateUnix int, rateProg int, rateHTML int, grp int, PRIMARY KEY (sid)) ;
   CREATE TABLE hw (student int, hw1 bool, hw2 bool, ..., FOREIGN KEY (student) REFERENCES students(sid));
   For more column types refer here, and for more contraints refer to the ones they have listed with the create table command here.
   For the assignment, create 3 columns.
4. Insert rows (aka tuples):
   INSERT INTO tblname VALUES (blah, blah, ...);
   For example:
   INSERT INTO students VALUES (00000000, "Randy Chung", "sysadmin- decal@ocf.berkeley.edu", 5, 5, 5, 0);
   For the assigment, insert 5 rows.
5. Make a query:
   SELECT * FROM tblname WHERE conditions...;
   For example:
   SELECT * FROM students; //This will return all the rows you have entered already.
   SELECT * FROM students WHERE name="Randy Chung"; //This returns the row that Randy appears in.
   SELECT grp FROM students WHERE sid=00000000; //This returns the group number that Randy's in.
6. Extras: There are some things you need to be aware of when you're using mysql, like:
   
   • Make sure you have a semicolon (;) at the end of all your mysql statements. If you forget, you'll notice that it shows this:
     mysql> whatever you typed...
     ->
   • show tables will show you all the table names that you created.
   • describe tblname will list out all the attributes of a table(columnNames, columnTypes, their default values, etc.).
   • quit to exit mysql.
   • For this assignment, after you have tested stuff and pretty much know what commands do what, run a script in order to record what you type. You can start the script with 'script hw5.script' and stop it with 'exit'. What do you need to show? Create a database, a table with 3 columns and 5 rows with at least 2 different data types, then query for a row and a value.
   • You don't have to type mysql commands in all caps, it's just convention here to show you what the commands are and what variables you enter and modify.
   • Also, with the tables that you create, you can make them about anything you want except for students, sids, etc. In order words, create your own data. Don't just bindly copy the examples I gave you here.

Part 2:PHP

1. Install PHP, refer to The Official PHP manual or just use your OCF account which already has PHP installed.
2. Take a look at the manual and for the assignment, write in the body of the email:
   • What version of PHP you have installed
   • A feature that the version you installed supports/ doesn't support
   • The php line that shows which browser your viewer is using to access your page.