Lab (3) - DBMS
php-MySQL LAB

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

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

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

In that directory, you may want to create another directory and name it anything you wish, I called mine php.  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.  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_tst3430 |
+-------------------+
| 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 the 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
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/OtherDirectoryYouMayHave/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 : Wednesday, November 19, 2004

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 in the class web page or find the answer on the web.

Activity 2 -  View or Manipulating a Database Using php
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( !$mydb)
      {
        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>

Exercise 2:
Cut and paste the above script and save it in a file access.php and try to view it.  You will get an error.  Look at the above script and find the cause of this error. Hint: There is a missmatch between the database you are connecting to and the one you are trying to use.  One simple change will fix the problem. 

What is the output?

Exercise 3:
After you fixed the error and ran the above program, make a change in the program and view the php file and write down the results.  Here is the change you have to make, in the following four lines, replace 0 in red with 1.

      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?

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 as you have the files on can view the contents of this file and find your password.  In order to fix this problem we create a file 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. 
Here is an example.  Copy the main php file to your directory (MySecurePhp.php) and a second file that controls access (functions.inc).  Notice that this file is included at the top of your php file.  Once copied, set your password, ID, and database name in the functions.inc file.  Then load the MySecurePhp.php file in a browser.  It is important that the file that contains your password have only permission to you.  So, make sure you have that:

%  ls -la functions.inc 

You should see  -xxx------

If permission is incorrect run:
% chmod 700 functions.inc

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 list the First name, Last name, SSN, and Salary of all employees.  You can modify it to do what you want.
<html>
<body>
<?php
      $db = mysql_connect("localhost", "YourID","YourPassword");
      if( !$db)
      {
        echo( "Connection to databse server failed <br>");
        exit( );
      }
      mysql_select_db("tst3430",$db);
      $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>

Her is the output of the above script:

James Borg 888665555 55000.00
Franklin Wong 333445555 40000.00
Jennifer Wallace 987654321 43000.00
Ahmad Jabbar 987987987 25000.00
John Smith 123456789 30000.00
Ramesh Narayan 666884444 38000.00
Joyce English 453453453 25000.00
Alicia Zelaya 999887777 25000.00

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.