Lab (1) - php
php-MySQL LAB

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

This is a good resource for MySQL/Php:
A php Tutorial Page

Preparations:
You are required to submit one file on ASULearn containing three queries q3.php, q4.php, and q5.php seperated by ******* line mark. you will create these queries at the end of the lab. You need to log into the student machine on two different screens, 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). By the way you didn't have to call that directory 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. Before we move on make sure you are the php (or whatever you call it) directory.

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. You will submit this file that contains all parts of the lab on ASULearn by Friday.  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 and submit a complete file that includes all the activities on ASULearn.

Introduction:
In the first MySQL 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 that lab. .  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 directory 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 755 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
date_default_timezone_set('EST');

$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 fetch 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 Lab 1. Now, open a file called something you like (but keep that file name a secret). For illustration only, I called mine myaccess.txt and type or cut and paste these lines in that file save and exit. 

<?php
$mydb = @mysql_connect("localhost", "YourMySQLID", "YourMySQLPassword");
if( !$mydb)
{
    echo( "Connection to database server failed <br>");
    exit( );
}


Save and exit this file.  Change the file permission
>chmod  744  YourAccessFileName (mine was myaccess.txt) 

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("YourAccessFileName");
      mysql_select_db("YorDataBaseName",$mydb);
      $result = @mysql_query("SELECT * FROM employee",$mydb);
      printf("First Name: %s<br>\n", mysql_result($result,0,"first_name"));
      printf("Last Name: %s<br>\n", mysql_result($result,0,"last_name"));
      printf("Address: %s<br>\n", mysql_result($result,0,"address"));
      printf("SSN: %s<br>\n", mysql_result($result,0,"employee_ssn"));
?>
</body>
</html>

You can save the above script in a file called q3.php and view the file on your web page if you have the correct file permission. 
http://student.cs.appstate.edu/YourID/php/q3.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,"first_name"));
      printf("Last Name: %s<br>\n", mysql_result($result,0,"last_name"));
      printf("Address: %s<br>\n", mysql_result($result,0,"address"));
      printf("SSN: %s<br>\n", mysql_result($result,0,"employee_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. 

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

}

Make the change in the empAccess.php file 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("YourAccessFileName");
      mysql_select_db("company",$mydb);
      $result = @mysql_query("SELECT * FROM employee",$mydb);

      while( $row = mysql_fetch_array($result) )
      {
            echo( $row["first_name"]);
            echo("    ");
            echo( $row["last_name"]);
            echo("    ");
            echo( $row["employee_ssn"]);
            echo("      ");
            echo( $row["salary"]);
            echo("<br>");
      }
?>
</body>
</html>

Save this in a file called q3.php.  Then upload it in a browser http://student.cs.appstate.edu/~YourID/php/q3.php to see the result. If the results are accurate, cut and paste the conent of q3.php and put it in the file that you will submit on on AsuLearn at the end. Note: you have to include two more queries in that file for the final submission.

Exercise 4:
Modify the above query, in a new file q4.php 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. It may take some work in html to make it look like the table below, but as long as the data is in a tablular form, it is fine. Something like the example output shown below (Note it doesn't have to line up this way, but if does great).

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


Include q4.php also in the file for submission on AsuLearn.

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

Use a php file, q5.php to insert the following record into the employee table.

    First Name: James
    M.I.: E.
    Last Name: Thompson
    SSN: 888665554
    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 successfully inserted the record and displayed that, delete that record using the MySQL command prompt or phpMyAdmin page that you access using student.appstate.edu/phpmyadmin and your MySQL and ID and Password.
Include q5.php in the file and now submit one single file containing the queries separated by ******, i.e. q3.php, q4.php, and q5.php on AsuLearn.