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:
Some Good Tutorials and Examples

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

/usr/local/apache2/htdocs/u/css/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.  If you were unable to complete the lab during the lab today, complete it by Friday Nov. 9 and submit a complete file that includes all the activities.

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   -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)

Note that this lab assumes that you have already populated your database as part of the post-lab for Lab (2).  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:
/usr/local/apache2/htdocs/u/css/YourID/

I don't know how many of you have a web page already on student.  If you do, I hope you have an index.html file in your default directory.  Having an index.html keeps people from viewing the content of your web directory, thus keeping them from moving through your directories and even viewing your files.  Open an screen in a browser of your choice  (for example Internet Explorer).  Type the following for the URL and press Enter:

http://student.cs.appstate.edu/~YourID/

If you don't have an index.html file, you will get a screen  that says:

Index of /~YourID

This is not good.  Let's quickly create a file index.html in your home directory, before we move to the next activitiy.  Open a new Putty session and log into student. Change your directory to:
/usr/local/apache2/htdocs/u/css/YourID/
Use an editor of your choice (vim, vi, pico, ...) and type this in the file and save it.


<html>
<body>
Empty File --
</body>
</html>

Save the file, and make sure to change permission on it (chmod 744 index.html).

Try http://student.cs.appstate.edu/~YourID/  again.  So, you took the first step to make your web directory a bit more secure.

In that directory, create a new one called php (mkdir php) and for now change permission on it to be all access for you, read permission for the group and outside world (chmod 744 php), then move to that directory (cd php).  Make sure you are in the correct directory, type pwd and see what your working directory is.  We will work here to complete the lab.  To be safe, cp the index.html from to this directory as well,   cp -p  ../index.html  .

The last . is very important. 

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.
http://student.cs.appstate.edu/~YourID/php/myfirstphp.php


<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 : DayoftheWeek, MonthoftheYear dd, yyyy

I am sure by looking at this script, you immediately can 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 -  Viewing or Manipulating a Database Using php
Connecting to MySQL via 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). Now, open a file called functions.php and include these lines in that file. 

<?php
    $mydb = mysql_connect("localhost","YourID","YourPassword");
    mysql_select_db("YourID",$mydb);
?>

Svae and exit this file.  Change the file permission
>chmod  744  functions.php  (you may need to change this file permission to 755 if it didn't work with 744, try 744 first)

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 by creating a file, empAccess.php containing the script below.  This script also displays one record from the employee table.

<html>
<body>
<?php
      include("functions.php");
      mysql_select_db("YorDataBaseName",$mydb);
      $result = @mysql_query("SELECT * FROM employee",$mydb);
      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>

You can now view this file on your web page. 
http://student.cs.appstate.edu/~YourID/php/empAccess.php

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?

What if you wanted to display all the records in that table?  You need to use a looping machanism, perhaps a while loop as you do not know how many recrords are in that file.

By making the following change, we can display all records.  Find out what MYSQL_ASSOC means.

$result = @mysql_query("SELECT * FROM employee",$mydb);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
          echo "First Name: {$row['fname']}<br>  "."Last Name: {$row['lname']} <br>  ".
               "SSN: {$row['ssn']}<br>  "."Address: {$row['address']}<br><br>" ;

}

Make the change and reload the page again. 

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

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

      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

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 displayed the record, delete that row.