Lab Assignment8: PHP and Oracle

CMSC4003

Lab Assignment8: PHP and Oracle

Name:

Due: See the due date in D2L calendar.

Preparation

You may download lab7_schema.sqland run it in SQL*Plus. We will use some of the tables in the schema as examples.

Hello World!

(a) Create a file called helloworld.php using the following content:

<HTML>

<HEAD>

<TITLE>Example 1</TITLE>

</HEAD>

<BODY>

echo ("<H1>Hello World!</H1>");

</BODY>

</HTML>

(b) Put the file into the public_html directory of your Web server account (

Note that “gqxxx” in the above URL should be replaced with your own account name.

(d) PHP code can be embedded in HTML code as we see in this example. PHP code is enclosed by “<?” and “?>”. Each PHP statement ends with a semicolon (;).

Note the PHP built-in function echo(). It is used to output text strings into the resulting HTML code that is sent out by the Web Server to browsers. Therefore, the actual HTML code received by your browser does not include any PHP code. You may click on the menu item “View  Source Code” of your Web Browser to check the HTML code. It should be the same as the follows:

<HTML>

<HEAD>

<TITLE>Example 1</TITLE>

</HEAD>

<BODY>

<H1>Hello World!</H1>

</BODY>

</HTML>

Variables, Strings and Data Types

(a) Create a file called variable.php in the public_html directory using the following content:

<HTML>

<HEAD>

<TITLE>Example 2</TITLE>

</HEAD>

<BODY>

$website = "

echo ("<BR>Surf to: $website");

echo ('<BR>Surf to: $website');

</BODY>

</HTML>

(b) Test the file in your Web browser.

(c) A variable is defined by a proceeding $ sign in PHP. Unlike C++ or Java, we do not need to declare a variable in PHP.

Note how strings are represented in PHP. They can be enclosed by either single quote marks (')or double quote marks ("). The semantics of the two representations are different. In single quote marks ('), a string is interpreted as is. When double quote marks (") are used, if there are variables embedded in the string, the values of the variables will replace the variables in the string.

In PHP, values can be strings, integers and floating point numbers.

Now consider the following PHP code segment:

$x = 1;

$y = "This is $x.";

$z = 'This is $x.';

Write down the values of variables y and z.

Answer:

For Loop

(a) Create a file called forloop.php in the public_html directory using the following content:

<HTML>

<HEAD>

<TITLE>Example of For Loop</TITLE>

</HEAD>

<BODY>

echo ("<TABLE ALIGN=CENTER BORDER=1>");

for ($j=1;$j<=4;$j++) {

echo ("<TR>");

for ($k=1;$k<=2;$k++)

echo ("<TD> Line $j, Cell $k </TD>");

echo("</TR>");

}

echo ("</TABLE>");

</BODY>

</HTML>

The code creates a 42 table.

(b) Test the file in your Web browser.

(c) The for-loop syntax of PHP is almost the same as that of C++. Pay special attention to how the PHP code puts a table together. Compare the PHP code with the source code in your Web browser(Menu: View  Source Code).

While Loop

Write a PHP script using while-loop to draw a 42 table the same way as the for-loop example. The syntax of a while-loop is as follows.

while (condition) {

// loop body;

}

Name your PHP code as whileloop.php. Test your code in your account.

Submit the code along with this lab assignmentthrough D2L email.

PHP also provides other control structures such as the if-statement. You can also define arrays and functionsin PHP. We will see more in other examples later.

References:

Official PHP Website:

(a)

(b) A list of all PHP functions:

Tutorial:

(a)

(b) A hardcopy of the tutorial (PDF format) is available in D2L.

To prepare for the course project, you need to read the whole tutorial carefully and try examples outside the lab.

Insertion in Oracle Tables using PHP

Starting from this step, we discuss the use of PHP for Oracle. We will learna database concept called cursor. A cursor contains a set of tuples as the result of a query statement (select). The tuples in a cursor can be accessed one at a time. You may consider a cursor as a pointer that goes through the query result one by one sequentially.

(a) Run the following SQL statement in SQL*Plus to check the current content of the table faculty.

select * from faculty;

(b) Create a file called orainsert.php using the following content in directory public_html. Note that you need to replace the account test/test with your own Oracle username and password

// set up environment

//putenv("ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1");

//putenv("ORACLE_SID=orcl");

// setup connection with Oracle

$connection = oci_connect ("test", "test", "gqiannew2:1521/pdborcl");

if ($connection == false){

//Foroci_connecterrors, nohandle needed

$e=oci_error();
die($e['message']);
}

// define an SQL command and store it in a variable $query

$query="insert into faculty(facname, facssno, officeaddress, worksfor) ".

"values ('Mike', '000000010', 'Engineering Building', 'CS')";

// define a cursor for the SQL command stored in the variable $query.

$cursor = oci_parse ($connection, $query);

if ($cursor == false){

// For oci_parse errors, pass the connection handle

$e = oci_error($connection);

die($e['message']);

}

// run the cursor

$result = oci_execute ($cursor, OCI_NO_AUTO_COMMIT);

if ($result == false){

// For oci_execute errors pass the cursor handle

$e = oci_error($cursor);

die($e['message']);

}

// commit the result

oci_commit ($connection);

// close the connection with oracle

oci_close ($connection);

echo ("Record inserted.");

(d) Notice how the code connects to Oracle (oci_connect). After login, a connection handle ($connection) is obtainedand used to parse SQL statements(oci_parse). The connection handle is also used for commit (oci_commit) and logout (oci_close).

The SQL statement does not need to end with a semicolon (;).

Through oci_parse, a cursor variable ($cursor) is obtained. The cursor variable is used to execute the SQL command that it represents (oci_execute).

The PHP built-in function putenv() is used to set the environment variables needed for Oracle. It is not necessary in our since the variables are already set. (Use Linux command “set” to check)

Note the PHP function die()/exit(). It is used to quit the execution of the current PHP code.

The period (.) is the string concatenation operator in PHP. By the way, the string concatenation operator in Oracle SQL is ||, e.g., 'aa' ||'bb' ='aabb'.

Note how nested strings are represented in PHP. You are allowed to have a nested string that uses quotation marks different from the outer quotation mark, that is, if the outer quotation mark is double, then you can directly use apair of nested single quotation markand vice versa. See the string of $query as an example: since SQL use single quote marks, we need the nested string withsingle quote marks here.

Note the PHP Boolean constant true and false.

In function oci_execute(), the option OCI_NO_AUTO_COMMIT indicates the execution of the SQL statement without commit. By default, the SQL statement will be committed automatically after the successful execution of oci_execute(). If the option OCI_NO_AUTO_COMMIT is used, function oci_commit() is needed to commit the transaction in the database.

Oracle Update in PHP

Write a PHP script, called oraupdate.php, which updates the worksfor attribute of the faculty member Mike to EE. You only need to change a small part of the above PHP code to achieve this.

Submit the scriptoraupdate.phpalong with this lab assignmentthrough D2L email.

Display a Table

(a) Create a file called displayfaculty.php using the following content. Note that you need to replace the account test/test with your own Oracle username and password

// setup connection with Oracle

$connection = oci_connect ("test", "test", "gqiannew2:1521/pdborcl");

if ($connection == false){

//Foroci_connecterrors, nohandle needed

$e=oci_error();
die($e['message']);
}

// this is the SQL command to be executed

$query = "select * from Faculty";

// parse the SQL command

$cursor = oci_parse ($connection, $query);

if ($cursor == false){

// For oci_parse errors, pass the connection handle

$e = oci_error($connection);

die($e['message']);

}

// execute the command

$result = oci_execute ($cursor);

if ($result == false){

// For oci_execute errors pass the cursor handle

$e = oci_error($cursor);

die($e['message']);

}

// display the results

echo "<table border=1>";

echo"<tr<th>Name</th<th>SSN</th<thAddress</th" .

"<th>Department</th</tr>";

// fetch the result from the cursor one by one

while ($values = oci_fetch_array ($cursor)){

$name = $values[0];

$ssn = $values[1];

$address = $values[2];

$dept = $values[3];

echo "<tr<td>$name</td<td>$ssn</td<td>$address</td>" .

"<td>$dept</td</tr>";

}

echo "</table>";

// free up resources used by the cursor

oci_free_statement($cursor);

// close the connection with oracle

oci_close ($connection);

(b) Test the file in your Web browser.

(c) Note how the cursor variable ($cursor) is used. After oci_execute(), the cursor variable is populated with the query result. oci_fetch_array() is used to fetch tuples from the cursor one at a time. The fetched tuple is returned as an array. The elements in the array correspond to the attributes in a query result tuple in the same order. The size of the array equals the number of the attributes of the result tuple. The returned value of oci_fetch_array()is alsoused to check the end of the cursor. It returns FALSE when the end of the cursor is reached.

When a cursor has been used, its recourse needs to be released by using the oci_free_statement() function.

(d) Write a PHP script, called displayokcstudent.php, which displays the name, SSN and level of those students who are from OKC.

Submit the scriptdisplayokcstudent.php along with this lab assignmentthrough D2L email.

PHP for Oracle References



Session Management

Session control is an important part of Web applications. Since HTML is not designed for interactions with users, session control is needed to remember user information between different Web/PHP pages.

Note that there are different ways for session control in PHP. If you are more familiar with a method other than the one discussed in this lab assignment, you may use the other method in the course project.

(a) Create tables client and clientsession by execute login_schema.sql in SQL*Plus.

The client table is used to store user information for login purpose. The clientsession table stores the sessionid for each user who has logged in the system. One session id is assigned to a user after the user login. It is used to identify and verify the user between different Web pages in the application. You may check the definition of both tables in login_schema.sql. Note that, since USER is a reserved word in Oracle SQL, we cannot use it as the table name for users.

(b) In SQL*Plus, insert the following two tuples into the table client for testing purpose.

insert into client values ('smith', 'David Smith', 'happy', NULL);

insert into client values ('susan', 'Susan Gonzales', 'lucky', NULL);

commit;

We have added two users, whose usernames are smith and susan, respectively.

<HTML>

<HEAD>

<TITLE>Simple Login Form</TITLE>

</HEAD>

<BODY>

User ID: <INPUT type="text" name="clientid">

</FORM>

</BODY>

</HTML>

login.htmlis the Web page to accept user login information. It uses the FORM feature of HTML to accept user inputs. A textbox and a button are displayed in the Web page.

Within the HTML tag FORM, the attribute action indicates that,when the button is clicked, PHP script loginresponse.php will be invoked. The attribute method is used to indicate the method used to pass user inputs to the PHP scriptloginresponse.php. In HTML, there are two basic methods for a FORM to pass user inputs, namely, POST and GET. Please refer to an HTML manual for further explanations. In this FORM, we choose to use method POST.

Basically, when the button is clicked, loginresponse.php is invoked and the user input in the textbox is passed to loginresponse.php using the identifier clientid.

(d) In directory public_html, create a PHP file called loginresponse.phpusing the following content. Note that you need to replace the account test/test with your own Oracle username and password.

$clientid = $_POST["clientid"];

$connection = oci_connect ("test", "test", "gqiannew2:1521/pdborcl");

if($connection == false){

$e=oci_error();
die($e['message']);
}

// connection OK - lookup the client

$sql = "select clientid " .

"from client " .

"where clientid='$clientid'";

$cursor = oci_parse($connection, $sql);

if ($cursor == false) {

$e = oci_error($connection);

echo$e['message']."<BR>";

oci_close ($connection);

// query failed - login impossible

die("Client Query Failed");

}

// query is OK - If we have any rows in the result set, we have

// found the client

$result = oci_execute($cursor);

if ($result == false){

$e = oci_error($cursor);

echo$e['message']."<BR>";

oci_close($connection);

die("Client Query Failed");

}

if(!$values = oci_fetch_array ($cursor)){

oci_close ($connection);

// client username not found

die ("Client not found.");

}

oci_free_statement($cursor);

// found the client

$clientid = $values[0];

// create a new session for this visitor

$sessionid = md5(uniqid(rand()));

// store the link between the sessionid and the clientid

// and when the session started in the session table

$sql = "insert into clientsession " .

"(sessionid, clientid, sessiondate) " .

"values ('$sessionid', '$clientid', sysdate)";

$cursor = oci_parse($connection, $sql);

if($cursor == false){

$e = oci_error($connection);

echo $e['message']."<BR>";

oci_close ($connection);

// insert Failed

die ("Failed to create a new session");

}

$result = oci_execute($cursor);

if ($result == false){

$e =oci_error($cursor);

echo $e['message']."<BR>";

oci_close($connection);

die("Failed to create a new session");

}

// insert OK - we have created a new session

//oci_commit ($connection);

oci_close ($connection);

// jump to your welcome page

Header("Location:welcomepage.php?sessionid=$sessionid");

The first thing we do in loginresponse.php is to fetch the user input clientid from system variable $_POST. $_POST is an array populated by the POST method in login.html. Instead of using an index number, we use string "clientid" as an index to fetch the user input clientid from array $_POST["clientid"] and assign it to variable $clientid.

The code then verifies the existence of the client id using table client. If the user exists, a new session id is randomly generated. The session id is inserted into table clientsession along with clientid. The session id is used to verify user privilege to access a Web/PHP page.

Note the PHP function Header() near the end of the code. After the successful verification of user login information, Header() is used here to redirect the Web browser to: "welcomepage.php?sessionid=$sessionid"

Note the part of the URL after the question mark. Here the GET (as opposed to POST) method is used to pass the session id to PHP script welcomepage.php for verification purpose.

IMPOTANT: In such a Web application, whenever you go to a Web/PHP page, you almost always need to pass the session id for verification purpose.

(e) In directory public_html, create a PHP file called welcomepage.php using the following content.

// include the verification PHP script

include "verifysession.php";

if ($sessionid == "") {

// no active session - clientid is unknown

echo("Invalid user!");

}

else {

// here we can generate the content of the welcome page

echo("Hello, welcome to my Website.");

}

The PHP script welcomepage.php is very straightforward. It first embeds/runs PHP script verifysession.php using PHP function include(). Here, you can consider PHP file verifysession.php as a procedure, it is embedded into welcomepage.php by function include(). It takes the session id passed towelcomepage.php and verifies it. If the execution of verifysession.php fails to verify the session id, the variable $sessionid is set to "" in verifysession.php. Using the content of variable $sessionid, welcomepage.php can decide the validity of the session id and what to do.

IMPORTANT: In such a Web application, you need to include verifysession.php at the beginning of almost every Web/PHP page except login.html. It guarantees that each page is accessed by legal users. If someone wants to access a Web/PHP page directly without a valid sessionid, verifysession.php will notice it (set $sessionid to "") and your code can decide what to do.

(f) In directory public_html, create a PHP file called verifysession.php using the following content. Note that you need to replace the account test/test with your own Oracle username and password.

$sessionid =$_GET["sessionid"];

$clientid = "";

$connection = oci_connect ("test", "test", "gqiannew2:1521/pdborcl");

if($connection == false){

$e = oci_error();

echo $e['message']."<BR>";

$sessionid="";

}

else {

// connection OK - validate current sessionid

if (!isset($sessionid) or ($sessionid=="")) {

// no session to maintain

$sessionid="";

}

else{

// lookup the sessionid in the session table to get the clientid

$sql = "select clientid " .

"from clientsession " .

"where sessionid='$sessionid'";

$cursor = oci_parse($connection, $sql);

if($cursor == false){

$e = oci_error($connection);

echo $e['message']."<BR>";

// query failed - login impossible

$sessionid="";

}

else{

$result = oci_execute($cursor);

if ($result == false){

$e = oci_error($cursor);

echo $e['message']."<BR>";

$sessionid="";

}

else{

if($values = oci_fetch_array ($cursor)){

// found the sessionid

$clientid = $values[0];

}

else {

// invalid sessionid

$sessionid = "";

}

oci_free_statement($cursor);

}

oci_close($connection);

}

The first thing we do inverifysession.php is to fetch the argument from system variable $_GET. $_GET is an array populated by the GET method(Remember the URL: " Instead of using an index number, we use string "sessionid" as an index to fetch the argument sessionidfrom array $_GET["clientid"] and assign it to variable $sessionid.

The code then verifies the existence of the session id using table clientsession. If the session id exists, $sessionid and $clientid will be populated. Otherwise, $sessionid is set to "".