Lab (3) - DBMS
php-MySQL LAB

Objectives:
To connect to a MySQL database via php and run some SQL scripts via php

This is an excellent resource for MySQL/Php:
Lycus Tutorial

Preparations:
You need to log into the student machine, then go to directory:  

/usr/local/apache2/htdocs/u/YourID

In that directory, create another directory and name it php.  Change the file permission to 755 for the php directory (chmod 755 php). You can call that directory anything you wish.  You need to complete your work there in order to be able to view your file on the web.

What to submit for this lab:
As you will go through this lab, you will create several queries and will run them to see if they really work over the web.  Create a file and cut and paste the query and the result of running each query. E-mail me one file that contains all parts of the lab.  I will check your .php file on the web to grade you.

Introduction:
In the last lab, you used MySQL to create all tables for the COMPANY database. That database had several tables and you populated them. We want to use that database again here.  So, open a new putty screen on student, login and start mysql.  
> mysql  --user  YourID  -p

mysql> use  <NameofTheDatabaseYouCreatedLastTime>;    I think you called your database YourID.

You got the list of all tables at the MySQL prompt by typing:
mysql> show tables;

This would generate a screen that looks like the following:
+-------------------+
| Tables_in_YourID  |
+-------------------+
| department             |
| dependent              |
| dept_locations        |
| employee               |
| project                   |
| works_on              |
+-------------------+
6 rows in set (0.05 sec)

The query that created the above list was executed at the mysql prompt.  We now want to view or manipulate the contents of a database using a browser.  Thus, we need to be able to connect to the database through a browser.  We will use php for this purpose.  Let's start with a few simple things that we can do with php, then go through an example.

Activity 1 -  A Simple php Script
All users on student have a default web directory.  In the second screen that you have, make sure you are in the:
/usr/local/apache2/htdocs/u/YourID/php  directory.

Now, create a file called myfirstphp.php and copy and paste the following script in that file.  Then try to view the file in a browser using the address of that page.

<html>
<body>
<?php

$myvar = "Hello World, This is my 1st php script.";
echo $myvar;
echo "<br>";
echo "Today's Date is :";
echo (date("l, F d, Y"));
echo "<br>";
?>
</body>
</html>

To view this file in your browser, use the address as:
http://student.cs.appstate.edu/~YourID/php/myfirstphp.php

If you got an error that the file was not found, check the permission of your file.  You need to give the read and execute permission to everyone.  To do this at the Unix prompt type:
% chmod 755 myfirstphp.php

Then view the file in your browser.

This will display:
Hello World, This is my 1st php script.
Today's Date is :Saturday, November 19, 2005

I am sure by looking at this script, you immediately see some of the things that are done to display the message in your browser.

Exercise 1:
Modify the above program to display the current time as well.  You can use the php references that I have on the class web page or find the answer on the web (here is a good place to look: http://us3.php.net/manual/en/function.date.php).

Activity 2 -  View or Manipulating a Database Using php
Insecure Connection - Please do not try this

In order to extract data from a database, you first need to connect to that database.  This is how you can connect to the database that you have created in the previous lab (Lab 1).

mysql_connect("localhost", "YourID", "Yourpassword");

It may be a good practice to display a message when the connection fails. Here is how you can do this:
$mydb = @mysql_connect("localhost", "YourID", "Yourpassword");
if( !$mydb)
{
    echo( "Connection to database server failed <br>");
    exit( );
}

By the way, if you don't put the @ in front of mysql_connect, then the system also displays an standard error message.  Having @ will force a silent fail.

Here is how I attempted to connect to and display a record from the COMPANY database.
<html>
<body>
<?php
      $db = mysql_connect("localhost", "YourID","YourPassword");
      if( !$db)
      {
        echo( "Connection to database server failed <br>");
        exit( );
      }
      mysql_select_db("YourID",$db);
      $result = @mysql_query("SELECT * FROM employee",$db);
      printf("First Name: %s<br>\n", mysql_result($result,0,"fname"));
      printf("Last Name: %s<br>\n", mysql_result($result,0,"lname"));
      printf("Address: %s<br>\n", mysql_result($result,0,"address"));
      printf("SSN: %s<br>\n", mysql_result($result,0,"ssn"));
?>
</body>
</html>

This is an insecure way to connect to your database because you have to put your ID and password in the file.  You can cut and paste the above script in a file, let's say insecure_access.php, and try to view it as a web page.  

Exercise 2:
In the above script, what will happen if you replace the 0's in the segment that is shown below, with 1's.  

      printf("First Name: %s<br>\n", mysql_result($result,0,"fname"));
      printf("Last Name: %s<br>\n", mysql_result($result,0,"lname"));
      printf("Address: %s<br>\n", mysql_result($result,0,"address"));
      printf("SSN: %s<br>\n", mysql_result($result,0,"ssn"));

What is the output?

Secure Connection
The above method is not secure because you have hardcoded the ID and password in the php file.  We want to make this more secure.
     $db = mysql_connect("localhost", "YourID","YourPassword");

This file should have read or execute permission to everyone so it can be loaded in a browser.  Thus, someone with access to the same machine can view its contents and find your password.  In order to fix this problem we create a secure file (with permission to the user ONLY) to keep the ID and password and set its permission such that other users cannot view its content.  Then we include that file in the php file.  In theory, this should work fine.  But due to a configuration with our web server on student, at this time this will not work with permission 700.  So, your functions.inc file still need 744 permission.

Open this php file (MySecurePhp.txt) and cut and paste ints content into a file MySecurePhp.php (Pay attention to the .php extension).   Then open the following file (functions.inc) and cut and paste its content in a file with the same name.  Notice that this file is included at the top of your secure php file.  Once you copied both files, open the file functions.inc and place your password, ID, and the database name in the functions.inc file.  Then exit the file and make sure the file permission is set to 744:

> chmod  744  functions.inc

Then load the MySecurePhp.php file in a browser. 

Exercise 3:
Here is a query that displays the first name, last name, ssn, and salary of all employees.

<html>
<body>
<?php
include("functions.inc");
      $result = @mysql_query("SELECT * FROM employee",$db);

      while( $row = mysql_fetch_array($result) )
      {
            echo( $row["fname"]);
            echo("  ");
            echo( $row["lname"]);
            echo("  ");
            echo( $row["ssn"]);
            echo("  ");
            echo( $row["salary"]);
            echo("<br>");
      }
?>
</body>
</html>

Save this in a file call is q1.php.  Then load it in a browser to see the result.

Exercise 4:
Modify the above query such that it displays the First name and Last name of employees who are working in department 5 and making more than $25000. Your output should have the attribute name at the heading and an appropriate format, i.e., spacing between fields.
Something like the example output shown below.

Employee of department 5 with salaries higher than $25000.
First Name        Last Name      SSN                Salary
Franklin             Wong             333445555     40000.00
John                   Smith             123456789     30000.00
Ramesh              Narayan         666884444     38000.00

Hint:
The following script lists the First name, Last name, SSN, and Salary of all employees.  You can modify it to do what you want.

Exercise 5:
I let you find the answer to this one on your own.

Insert the following record into the employee table.

    First Name: James
    M.I.: E.
    Last Name: Thompson
    SSN: 888665555
    Born: Nov 12 1963
    Address:  450 Kings St., Boone, NC
    Sex: Male
    Salary: $55000.00
    Supervisor's SSN: 888665555
    Department Number: 1

Then do a select on the first name or last name to make sure you have really created the row.  After you display the record, delete that row.