Lab Session 7 - Building Database-Web Applications in PHP

By Mitchell Welch

University of New England

Reading

PHP Documentation: http://php.net/manual/en/book.pgsql.php

Summary

Introduction

Introduction

Todays practical session is all about building database driven web applications using the scripting language PHP.
We will be building a simple web application for browsing our company database.
Todays practical is more of a demonstration as you may not be familiar with the PHP scripting language.
The idea of this one is to give you a basic understanding of the operation of web-based applications.

House Keeping

In order to connect up to your application up to the database, you will need to specify the username and password of the user you want to connect to the database as. (I.e. We will be using the same approach that we used in practical number 4.)
To avoid having to specify your UNE username and password in plain-text within your java programs, we have created a new user account in the PosgreSQL sever on turing for each student dedicated for use in your applications.
- The username for your apps account with by in form: ‘\<your_une_username>_apps’
- The password for the account will be you UNE Student Number
For example, if my UNE username was studen1 and my student number was 122334455, my apps account username will by ‘studen1_apps’ and the password for the account will be ‘122334455’
You can create databases and log into your apps account using the createdb application and ‘psql’ client, however you will need to explicitly specify the username to stop the client from automatically logging in using you UNE account credentials.
This is done using the -U, -W -h options:
- -U Specifies the account username that you will be logging in with
- -W Forces the psql client to prompt for the password
- -h Sets the host - 127.0.0.1 is turing.une.edu.au
For todays practical session, you will need to create a new database using you apps account called <your_username>_apps_prac_7
Like this (Sub in your username and enter your student number as the password when prompted)


[mwelch8@turing ~]$ createdb -U mwelch8_apps -W -h 127.0.0.1 mwelch8_apps_prac_7
Password: 
[mwelch8@turing ~]$ psql -U mwelch8_apps -W -h 127.0.0.1 mwelch8_apps_prac_7
Password for user mwelch8_apps: 
psql (9.4.4)
Type "help" for help.

mwelch8_apps_prac_7=> \dt
No relations found.
mwelch8_apps_prac_7=>

Once you have successfully logged into to your newly created prac_07 database using your apps account, you will need to build your COMPANY database.




mwelch8_apps_prac_7=> \i ~/prac_01.sql

...

mwelch8_apps_prac_7=> \dt
             List of relations
 Schema |      Name      | Type  |  Owner  
--------+----------------+-------+---------
 public | department     | table | mwelch8
 public | dependent      | table | mwelch8
 public | dept_locations | table | mwelch8
 public | employee       | table | mwelch8
 public | project        | table | mwelch8
 public | works_on       | table | mwelch8
(6 rows)

NOTE Running the sql script in this way may produce some errors when you run this script. These errors are from the commands that attempt to change the owner of the relations to account under which they were originally created.
These can be ignored as we want the owner of these relations to default to the current account.
Now you have your database for todays practical session set up.

Exercises

PHP is a very popular Web scripting language that allows the programmers to rapidly develop Web applications. In particular, PHP is suited to develop Web applications that access a PostgreSQL database. In this section, we illustrate the ease of programming with PHP and provide several examples of Web access to PostgreSQL databases.

Example 1: Consider the problem of finding employee names given their social security number. To implement this problem as a Web application, we can design two Web pages:

The first Web page would contain a HTML form that contains a select list of social security numbers of employees and a submit button.
Upon choosing a social security number and submitting the form in the first Web page produces the second Web page that lists the name of the employee.

center-aligned image

Both these Web pages contain dynamic information (obtained from the database) and therefore can easily be produced by PHP scripts. The PHP script (p1post.php) that produces the first Web page is shown below.


<html>

<head>
    <title>Simple Databse Access</title>
</head>

<body>
    <?php

    $pw = "/* Your Apps database Password*/";
    $user = "/*username*/_apps";
    $db = "/*Your apps database name*/";

    $conn_string = "host=127.0.0.1 port=5432 dbname=".$db." user=".$user." password=".$pw;
    $dbconn = pg_connect($conn_string);
    //connect to a database named "test" on the host "sheep" with a username and password


    // Performing SQL query
    $query = 'SELECT ssn FROM employee';
    $result = pg_query($query) or die('Query failed: ' . pg_last_error());

    ?>
        <h4>Employee Details for:</h4>
        <form method="post" action="p1.php">
        <select name="ssn">

    <?php
        while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
            foreach ($line as $col_value) {
            echo "\t\t<option>$col_value</option>\n";
            }

         }

    ?>

    </select>
    <input type="submit" value="Get Employee Details">
    </form>
</body>

</html>

Copy this code to a file name ‘p1post.php’ using a text editor and save the file to your public_html directory.

[mwelch8@turing mwelch8]$ cd public_html/
[mwelch8@turing public_html]$ ls -l

-rw-------  1 mwelch8 mwelch8     978 Sep  6 12:53 companyBrowse.php
-rw-------  1 mwelch8 mwelch8    2869 Sep  6 13:57 deptView.php
-rw-------  1 mwelch8 mwelch8    3278 Sep  6 13:41 empView.php
-rw-------  1 mwelch8 mwelch8     588 Sep  7 09:54 p1.php
-rw-------  1 mwelch8 mwelch8     881 Sep  7 09:53 p1post.php
-rw-------  1 mwelch8 mwelch8     943 Sep  4 16:31 p2.php
-rw-------  1 mwelch8 mwelch8    2093 Sep  6 13:50 projView.php

You will need to change the file permissions so that the php interpreter can access the files. This is done using the chmod command.


[mwelch8@turing public_html] chmod og+r *.php
[mwelch8@turing public_html]$ ls -l

-rw-r--r--  1 mwelch8 mwelch8     978 Sep  6 12:53 companyBrowse.php
-rw-r--r--  1 mwelch8 mwelch8    2869 Sep  6 13:57 deptView.php
-rw-r--r--  1 mwelch8 mwelch8    3278 Sep  6 13:41 empView.php
-rw-r--r--  1 mwelch8 mwelch8     588 Sep  7 09:54 p1.php
-rw-r--r--  1 mwelch8 mwelch8     881 Sep  7 09:53 p1post.php
-rw-r--r--  1 mwelch8 mwelch8     943 Sep  4 16:31 p2.php
-rw-r--r--  1 mwelch8 mwelch8    2093 Sep  6 13:50 projView.php

You can then access the page via a web browser:

http://turing.une.edu.au/~/p1post.php

for example:

http://turing.une.edu.au/~mwelch8/p1post.php

A PHP script typically consists of HTML code to display “static” parts of the Web page interspersed with procedural PHP statements that produce “dynamic” parts of the Web page. The dynamic content may come from a database such as PostgreSQL and hence most of the PHP procedural code involves connecting to the database, running queries and using the query result to produce parts of the Web page.

In the above example script, a simple HTML page is produced which has:

Some static content such as text headers and a HTML form with a submit button. The HTML form when submitted invokes the second PHP script called “p1.php”.
A dynamic “select” GUI element within the HTML form which contains a list of employee social security numbers for the users to choose from.

PHP statements are enclosed within <?php And >?. HTML code can be produced within PHP code using the “echo” command as is seen in several places in the code. As can be seen, there are two blocks of PHP code in the example: one to connect to the database and execute a query and the second to use the results of the query to produce the HTML “select” list options.

The PHP script (p1.php) to produce the second Web page is shown next.


<html>

<head>
<title>Simple Database Access</title>
</head>
    <body>
    <h3>Employee Information</h3>

    <?php



        $ssn=$_POST['ssn'];

        $pw = "/* Your Apps database Password*/";
        $user = "/*username*/_apps";
        $db = "/*Your apps database name*/";

        $conn_string = "host=127.0.0.1 port=5432 dbname=".$db." user=".$user." password=".$pw;
        $dbconn = pg_connect($conn_string);

        $query="SELECT * FROM employee where ssn='$ssn'";
        $result = pg_query($query) or die('Query failed: ' . pg_last_error());

        if($data = pg_fetch_object($result)) {
            echo "<b>$data->fname $data->minit, $data->lname</b>";

        }
    ?>

    </body>
</html>

In this script, the employee social security number posted in the first Web page is retrieved usingthe PHP statement

$ssn=$_POST['ssn'];

This social security number is then used in an SQL query to retrieve employee name. The script contains one block of PHP code surrounded by some HTML code.

Copy the code for these to a set of appropriately named files and run these pages so see the results. Remember to update the file permissions.

Example 2: In this example, a PHP script that lists all employees in a given department is shown. The script takes as input the department number as a “GET” parameter in the URL itself as follows:

turing.une.edu.au/~/p2.php?dno=4

The Web page produced by the script is shown in the figure below.

center-aligned image


    <html>
    <head>
    <title>Simple Database Access</title>
    </head>
    <body>

    <?php


        $dno=$_GET['dno'];
        $pw = "/* Your Apps database Password*/";
        $user = "/*username*/_apps";
        $db = "/*Your apps database name*/";

        $conn_string = "host=127.0.0.1 port=5432 dbname=".$db." user=".$user." password=".$pw;
        $dbconn = pg_connect($conn_string);

        $query="SELECT lname,salary FROM employee where dno=$dno";
        $result = pg_query($query) or die('Query failed: ' . pg_last_error());


    ?>

    <table border="2" cellspacing="2" cellpadding="2">
        <tr>
            <th><font face="Arial,Helvetica,sans-serif">Last Name</font></th>
            <th><font face="Arial,Helvetica,sans-serif">Salary</font></th>
        </tr>

    <?php

        echo "<h4>Employees in Department $dno</h4>";

        while ($data = pg_fetch_object($result)) {
            $lname=$data->lname;
            $salary=$data->salary;

    ?>

        <tr>
            <td><font face="Arial, Helvetica, sans-serif">

            <?php echo $lname; ?>

            </font></td>
            <td><font face="Arial, Helvetica, sans-serif">
            <?php echo $salary;}?>

            </font></td>

        </tr>


    </table>

    </body>

    </html>

The PHP script performs an SQL query to retrieve employee names and salaries who work for the given department. This information is then formatted neatly into an HTML table for display. This example illustrates more intricate embedding of PHP code within HTML code as is seen in the “while” loop towards the end of the script.

Example 3: A COMPANY database browser application is shown in this example. The initial web page in this application lists all the departments in the company. By following hyperlinks, the user may see more details of departments, employees, and projects in three separate Web pages.

The browser program is implemented using four PHP scripts:

companyBrowse.php: This script lists all the departments in the company in a tabular form
deptView.php.
empView.php.
projectView.php.

The code for companyBrowse script is shown below:


    <html>
    <head>

        <title>All Departments</title>

    </head>
    <body>

    <?php


        $pw = "/* Your Apps database Password*/";
        $user = "/*username*/_apps";
        $db = "/*Your apps database name*/";

        $conn_string = "host=127.0.0.1 port=5432 dbname=".$db." user=".$user." password=".$pw;
        $dbconn = pg_connect($conn_string);

        $query="SELECT dnumber,dname FROM department order by dnumber";
        $result = pg_query($query) or die('Query failed: ' . pg_last_error());

    ?>

    <h4>Departments of Company</h4>
    <table border="2" cellspacing="2" cellpadding="2">
        <tr>
            <th><font face="Arial, Helvetica, sans-serif"> Department Number</font></th>
            <th><font face="Arial, Helvetica, sans-serif">Department Name</font></th>

        </tr>

    <?php

        while ($data = pg_fetch_object($result)) {
                $dno=$data->dnumber;
                $dname=$data->dname;

    ?>

    <tr>
        <td><font face="Arial, Helvetica, sans-serif">
            <a href="deptView?dno=<?php echo $dno; ?>"> <?php echo $dno; ?></a></font></td>
        <td><font face="Arial, Helvetica, sans-serif"><?php echo $dname; }?></font></td>
    </tr>


    </table>
    </body>
    </html>

The script performs a simple query on the DEPARTMENT table and outputs the list of department numbers and names formatted as an HTML table as shown in the figure below.

center-aligned image

The department numbers in this list are formatted as HTML hyperlinks that, when traversed by the user, will produce a Web page containing more details of the chosen department. The detailed department view Web page is shown in the next figure.

center-aligned image

The PHP script (deptView.php) that produces the detailed department view is shown below.


    <html>

    <head>

    <title>Department View</title>

    </head>

    <body>

    <?php


        $dno=$_GET['dno'];

        $pw = "/* Your Apps database Password*/";
        $user = "/*username*/_apps";
        $db = "/*Your apps database name*/";

        $conn_string = "host=127.0.0.1 port=5432 dbname=".$db." user=".$user." password=".$pw;
        $dbconn = pg_connect($conn_string);

        $query="SELECT dname,mgrssn,mgrstartdate,lname,fname FROM department,employee where dnumber=$dno and mgrssn=ssn";
        $result = pg_query($query) or die('Query failed: ' . pg_last_error());
        $data = pg_fetch_object($result);


        $dname=$data->dname;
        $mssn=$data->mgrssn;
        $mstart=$data->mgrstartdate;
        $mlname=$data->lname;
        $mfname=$data->fname;

        echo "<b>Department: </b>", $dname;
        echo "<P>Manager: <a href=\"empView.php?ssn=", $mssn, "\">", $mlname, ", ", $mfname, "</a></BR>";
        echo "Manager Start Date: ", $mstart;

        echo "<h4>Department Locations:</h4>";

        $query="SELECT dlocation FROM dept_locations where dnumber=$dno";
        $result = pg_query($query) or die('Query failed: ' . pg_last_error());


        while ($data = pg_fetch_object($result)) {
                    $dloc=$data->dlocation;
                    echo $dloc, "<BR>\n";
        }

        echo "<h4>Employees:</h4>";

        $query="SELECT ssn,lname,fname FROM employee where dno=$dno";
        $result = pg_query($query) or die('Query failed: ' . pg_last_error());

    ?>

    <table border="2" cellspacing="2" cellpadding="2">

    <tr>

        <th><font face="Arial, Helvetica, sans-serif">Employee SSN</font></th>
        <th><font face="Arial, Helvetica, sans-serif">Last Name</font></th>
        <th><font face="Arial, Helvetica, sans-serif">First Name</font></th>

    </tr>

    <?php


        while ($data = pg_fetch_object($result)) {

            $essn=$data->ssn;
            $elname=$data->lname;
            $efname=$data->fname;

    ?> 

    <tr>

        <td><font face="Arial, Helvetica, sans-serif"><a href="empView.php?ssn=<?php echo $essn;?>"><?php echo $essn;?> </a></td>
        <td><font face="Arial, Helvetica, sans-serif"><?php echo $elname; ?></font></td>
        <td><font face="Arial, Helvetica, sans-serif"><?php echo $efname;} ?></font></td>

    </tr>

    </table>

    <?php

        echo "<h4>Projects:</h4>";

        $query="SELECT pnumber,pname,plocation FROM project where dnum=$dno";
        $result = pg_query($query) or die('Query failed: ' . pg_last_error());


    ?>

    <table border="2" cellspacing="2" cellpadding="2">

    <tr>

        <th><font face="Arial, Helvetica, sans-serif">Project Number</font></th>
        <th><font face="Arial, Helvetica, sans-serif">Project Name</font></th>
        <th><font face="Arial, Helvetica, sans-serif">Location</font></th>

    </tr>

    <?php


        while ($data = pg_fetch_object($result)) {

         $pnum=$data->pnumber;
         $pname=$data->pname;
         $ploc=$data->plocation;

    ?>

    <tr>
        <td><font face="Arial, Helvetica, sans-serif"><a href="projView.php?pnum=<?php echo $pnum; ?>"><?php echo $pnum;?></a></font></td>
        <td><font face="Arial, Helvetica, sans-serif"><?php echo $pname; ?></font></td>
        <td><font face="Arial, Helvetica, sans-serif"><?php echo $ploc; }?></font></td>
    </tr>


    </body>
    </html>

The deptView script executes the following four queries and formats the results of the queries as shown in the Web page.

SELECT dname,mgrssn,mgrstartdate,lname,fname
FROM department,employee
WHERE dnumber=$dno and mgrssn=ssn

SELECT dlocation
FROM dept_locations
WHERE dnumber=$dno

SELECT ssn,lname,fname
FROM employee
WHERE dno=$dno

SELECT pnumber,pname,plocation
FROM project
WHERE dnum=$dno

Each of these queries uses the PHP variable $dno containing the department number for which the view is generated.

The department view also contains hyperlinks for employees and projects which when traversed by the user produces detailed employee and project Web pages. The code for PHP scripts that produce these Web pages are similar to the deptView script.

projView.php:


    <html>
    <head>
    <title>Project View</title>
    </head>
    <body>

    <?php
        $pw = "/* Your Apps database Password*/";
        $user = "/*username*/_apps";
        $db = "/*Your apps database name*/";

        $pnum=$_GET['pnum'];

        $conn_string = "host=127.0.0.1 port=5432 dbname=".$db." user=".$user." password=".$pw;

        $dbconn = pg_connect($conn_string);

        $query="SELECT pname,plocation,dnum,dname FROM project,department where pnumber=$pnum and dnum=dnumber";

        $result = pg_query($query) or die('Query failed: ' . pg_last_error());
        $data = pg_fetch_object($result);

        $pname=$data->pname;
        $ploc=$data->plocation;
        $pdnum=$data->dnum;
        $pdname=$data->dname;

        echo "<b>Project: </b>", $pname, "<br>";
        echo "Project Location: ", $ploc, "<br>";
        echo "Controlling Department: <a href=\"deptView.php?dno=", $pdnum, "\">", $pdname, "</a>";

        echo "<h4>Employees working in project:</h4>";
        $query="SELECT ssn,lname,fname,hours FROM employee,works_on where pno=$pnum and essn=ssn";

        $result = pg_query($query) or die('Query failed: ' . pg_last_error());
        $num = pg_num_rows($result);
        if ($num > 0) {
    ?>
      <table border="2" cellspacing="2" cellpadding="2">
      <tr>
      <th><font face="Arial, Helvetica, sans-serif">Employee SSN</font></th>
      <th><font face="Arial, Helvetica, sans-serif">Employee Last Name</font></th>
      <th><font face="Arial, Helvetica, sans-serif">Employee First Name</font></th>
      <th><font face="Arial, Helvetica, sans-serif">Hours</font></th>
      </tr>
    <?php

          while ($data = pg_fetch_object($result)) {
            $essn=$data->ssn;
            $elname=$data->lname;
            $efname=$data->fname;
            $hours=$data->hours;
    ?>  
      <tr>
      <td><font face="Arial, Helvetica, sans-serif">
      <a href="empView.php?ssn=<?php echo $essn; ?>"><?php echo $essn; ?></a></font></td>
      <td><font face="Arial, Helvetica, sans-serif"><?php echo $elname; ?></font></td>
      <td><font face="Arial, Helvetica, sans-serif"><?php echo $efname; ?></font></td>
      <td><font face="Arial, Helvetica, sans-serif"><?php echo $hours; ?></font></td>
      </tr>
    <?php
          }
        } else
            echo "Project has no employees<br>";
        echo "</table>";

    ?>

    <P>
    <a href="./">Return to main page</a>

    </body>
    </html>

empView.php:


    <html>
    <head>
    <title>Employee View</title>
    </head>
    <body>

    <?php
        $pw = "/* Your Apps database Password*/";
        $user = "/*username*/_apps";
        $db = "/*Your apps database name*/";

        $essn=$_GET['ssn'];
        $conn_string = "host=127.0.0.1 port=5432 dbname=".$db." user=".$user." password=".$pw;
        $dbconn = pg_connect($conn_string);

        $query="SELECT lname,fname,bdate,address,salary,dno,dname FROM employee,department where ssn='$essn' and dno=dnumber";
        $result = pg_query($query) or die('Query failed: ' . pg_last_error());
        $data = pg_fetch_object($result);

        $elname=$data->lname;
        $efname=$data->fname;
        $ebdate=$data->bdate;
        $eaddress=$data->address;
        $esalary=$data->salary;
        $edno=$data->dno;
        $edname=$data->dname;

        echo "<b>Employee: </b>", $elname, ", ", $efname, "<br>";
        echo "Birth Date: ", $ebdate, "<br>";
        echo "Address: ", $eaddress, "<br>";
        echo "Salary: ", $esalary, "<br>";
        echo "Department: <a href=\"deptView.php?dno=", $edno, "\">", $edname, "</a>";

        echo "<h4>Projects Involved In:</h4>";
        $query="SELECT pnumber,pname,hours FROM project,works_on where pno=pnumber and essn='$essn'";
        $result = pg_query($query) or die('Query failed: ' . pg_last_error());

        $num = pg_num_rows($result);

        if ($num > 0) {
    ?>
    <table border="2" cellspacing="2" cellpadding="2">
    <tr>
    <th><font face="Arial, Helvetica, sans-serif">Project Number</font></th>
    <th><font face="Arial, Helvetica, sans-serif">Project Name</font></th>
    <th><font face="Arial, Helvetica, sans-serif">Hours</font></th>
    </tr>
    <?php

          while ($data = pg_fetch_object($result)) {
            $pno=$data->pnumber;
            $pname=$data->pname;
            $hours=$data->hours;
    ?>  
    <tr>
    <td><font face="Arial, Helvetica, sans-serif">
    <a href="projView.php?pnum=<?php echo $pno; ?>"><?php echo $pno; ?></a></font></td>
    <td><font face="Arial, Helvetica, sans-serif"><?php echo $pname; ?></font></td>
    <td><font face="Arial, Helvetica, sans-serif"><?php echo $hours; ?></font></td>
    </tr>
    <?php
          }
        } else
            echo "Employee works for no project<br>";
        echo "</table>";

        echo "<h4>Dependents:</h4>";

        $query="SELECT dependent_name,sex,bdate,relationship FROM dependent where essn='$essn'";
        $result = pg_query($query) or die('Query failed: ' . pg_last_error());

        $num = pg_num_rows($result);

        if ($num > 0) {
    ?>
    <table border="2" cellspacing="2" cellpadding="2">
    <tr>
    <th><font face="Arial, Helvetica, sans-serif">Dependent Name</font></th>
    <th><font face="Arial, Helvetica, sans-serif">Sex</font></th>
    <th><font face="Arial, Helvetica, sans-serif">Birth Date</font></th>
    <th><font face="Arial, Helvetica, sans-serif">Relationship</font></th>
    </tr>
    <?php
        while ($data = pg_fetch_object($result)) {
            $depname=$data->dependent_name;
            $depsex=$data->sex;
            $depbdate=$data->bdate;
            $deprelationship=$data->relationship;
    ?>  
    <tr>
    <td><font face="Arial, Helvetica, sans-serif"><?php echo $depname; ?></font></td>
    <td><font face="Arial, Helvetica, sans-serif"><?php echo $depsex; ?></font></td>
    <td><font face="Arial, Helvetica, sans-serif"><?php echo $depbdate; ?></font></td>
    <td><font face="Arial, Helvetica, sans-serif"><?php echo $deprelationship; ?></font></td>
    </tr>
    <?php

          }
        } else
            echo "Employee has no dependents<br>";
        echo "</table>";
    ?>

    <P>
    <a href="./">Return to main page</a>

    </body>
    </html>

Copy the code foe deptView.php, projView.php and empView.php into appropriately named files in your public_html directory and browse through the applicatin. Remember to adjust the file permissions so that the php interpreter can access your files.