3 Getting Connected

In this chapter, you create HR application files that implement PHP functions to connect and disconnect to the Oracle Database. You also develop a PHP function that enables you to execute a query to validate that a database connection has been successfully established.

This chapter also guides you through the creation and modification of PHP files that call a function to produce the header and footer for the Departments page, where the footer section of the page includes a date and time.

This chapter has the following topics:

Note:

For simplicity, the user name and password are written into this sample application code. For applications that will be deployed, coding the user name and password strings directly into your application source code is not recommended. Oracle recommends that you use a more secure technique, such as implementing a dialog that prompts the user for the user name and password.

See Oracle Database Security Guide and the documentation for your development environment for details on security features and practices.

Building the Departments Page

In this section, you will create the functions and styles for the first screen of your application.

Follow these steps to build the Departments page:

  1. To create a directory for your application files, and to change to the newly created directory, enter the following commands in a command window:

    On Windows:

    mkdir c:\program files\Apache Group\Apache2\htdocs\chap3
cd c:\program files\Apache Group\Apache2\htdocs\chap3

    On Linux:

    mkdir $HOME/public_html/chap3
cd $HOME/public_html/chap3

    If you create files in a different location, you must change the steps for file editing and execution to match your working directory name and URL.

  2. To start developing your application user interface, use your preferred text editor to create a file called anyco_ui.inc that contains the two functions ui_print_header() and ui_print_footer() with their parameters to enable your application Web pages to have consistent header and footer sections:

    <?php

function ui_print_header($title)
{
  $title = htmlentities($title);
  echo <<<END
  <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
     "http://www.w3.org/TR/html4/strict.dtd">
  <html>
  <head>
    <meta http-equiv="Content-Type"
          content="text/html; charset=ISO-8859-1">
    <link rel="stylesheet" type="text/css" href="style.css">
    <title>Any Co.: $title</title>
  </head>
  <body>
  <h1>$title</h1>
END;
}

function ui_print_footer($date)
{
  $date = htmlentities($date);
  echo <<<END
  <div class="footer">
    <div class="date">$date</div>
    <div class="company">Any Co.</div>
  </div>
END;
}

?>

    • This application design uses PHP function definitions to enable modular reusable code.

    • The functions in anyco_ui.inc use a PHP language construct called a "here document." This enables you to place any amount of HTML formatted text between the following two lines:

      echo <<<END
END;

    • Do not put leading spaces in the END; line. If you do, the rest of the document will be treated as part of the text to be printed.

    • Any PHP parameters appearing inside the body of a "here document" are replaced with their values, for example, the $title or $date parameters.

    • The PHP function htmlentities() is used to prevent user-supplied text from accidentally containing HTML markup and affecting the output formatting.

  3. The PHP file uses a Cascading Style Sheet (CSS) file called style.css to specify the presentation style in HTML in the browser.

    Create a style.css file in the chap3 directory with the following CSS text:

    body
{ background: #CCCCFF;
  color:      #000000;
  font-family: Arial, sans-serif; }

h1
{ border-bottom: solid #334B66 4px;
  font-size: 160%; }

table
{ padding: 5px; }

td
{ border: solid #000000 1px;
  text-align: left;
  padding: 5px; }

th
{ text-align: left;
  padding: 5px; }

.footer
{ border-top: solid #334B66 4px;
  font-size: 90%; }

.company
{ padding-top: 5px;
  float: right; }

.date
{ padding-top: 5px;
  float: left; }

  4. To call the user interface functions, create the anyco.php file with the following text:

    <?php

require('anyco_ui.inc');

ui_print_header('Departments');
ui_print_footer(date('Y-m-d H:i:s'));

?>

    The require() PHP command is used to include anyco_ui.inc. The new functions can be called to produce HTML output.

  5. To test the anyco.php file, enter the following URL in your browser:

    On Windows:

    http://localhost/chap3/anyco.php

    On Linux:

    http://localhost/~<username>/chap3/anyco.php

    The resulting Web page is similar to the following:

    Description of chap3_test_install_005.gif follows
    Description of the illustration chap3_test_install_005.gif

    The date and time appear in the page footer section.

Connecting to the Database

In this section, you will add a database connection to your Departments screen so that you can display Department data.

Follow these steps to add a database connection to your application.

To form a database connection, you use the oci_connect() function with three string parameters:

$conn = oci_connect($username, $password, $db)

The first and second parameters are the database user name and password, respectively. The third parameter is the database connection identifier. The oci_connect() function returns a connection resource needed for other OCI8 calls; it returns FALSE if an error occurs. The connection identifier returned is stored in a variable called $conn.

  1. Edit the anyco.php file to add a database connection with the following parameter values:

    • Username is hr.

    • Password for this example is hr. Remember to use the actual password of your HR user.

    • Oracle connection identifier is //localhost/orcl.

  2. Edit the anyco.php file to validate that the oci_connect() call returns a usable database connection, write a do_query() function that accepts two parameters: the database connection identifier, obtained from the call to oci_connect(), and a query string to select all the rows from the DEPARTMENTS table.

  3. Edit the anyco.php file to prepare the query for execution, add an oci_parse() call. The oci_parse() function has two parameters, the connection identifier and the query string. It returns a statement identifier needed to execute the query and fetch the resulting data rows. It returns FALSE if an error occurs.

  4. Edit the anyco.php file to execute the query, add a call to the oci_execute() function. The oci_execute() function executes the statement associated with the statement identifier provided in its first parameter. The second parameter specifies the execution mode. OCI_DEFAULT is used to indicate that you do not want statements to be committed automatically. The default execution mode is OCI_COMMIT_ON_SUCCESS. The oci_execute() function returns TRUE on success; otherwise it returns FALSE.

  5. Edit the anyco.php file to fetch all the rows for the query executed, add a while loop and a call to the oci_fetch_array() function. The oci_fetch_array() function returns the next row from the result data; it returns FALSE if there are no more rows. The second parameter of the oci_fetch_array() function, OCI_RETURN_NULLS, indicates that NULL database fields will be returned as PHP NULL values.

    Each row of data is returned as a numeric array of column values. The code uses a PHP foreach construct to loop through the array and print each column value in an HTML table cell, inside a table row element. If the item value is NULL then a nonbreaking space is printed; otherwise the item value is printed.

    After the edits in Steps 1 to 5, the anyco.php file becomes:

    <?php // File: anyco.php

require('anyco_ui.inc');

// Create a database connection
$conn = oci_connect('hr', 'hr', '//localhost/orcl');

ui_print_header('Departments');
do_query($conn, 'SELECT * FROM DEPARTMENTS');
ui_print_footer(date('Y-m-d H:i:s'));

// Execute query and display results 
function do_query($conn, $query)
{
  $stid = oci_parse($conn, $query);
  $r = oci_execute($stid, OCI_DEFAULT);

  print '<table border="1">';
  while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
    print '<tr>';
    foreach ($row as $item) {
      print '<td>'.
            ($item!== null ? htmlentities($item) : '&nbsp;').'</td>';
    }
    print '</tr>';
  }
  print '</table>';
}

?>

  6. To test the changes made to anyco.php, save the modified anyco.php file. In a browser window, enter the following URL:

    On Windows:

    http://localhost/chap3/anyco.php

    On Linux:

    http://localhost/~<username>/chap3/anyco.php

    The page returned in the browser window should resemble the following page:

    Description of chap3_db_connect_001.gif follows
    Description of the illustration chap3_db_connect_001.gif

    If you want to query the EMPLOYEES data, you can optionally change the query in the do_query() function call to:

    do_query($conn, 'SELECT * FROM EMPLOYEES');

If You Have Connection Problems

Check that the username, password and connection string are valid. The connect string '//localhost/orcl' uses the Oracle Easy Connect syntax. If you are using an Oracle Net tnsnames.ora file to specify the database you want to connect to, then use the network alias as the third parameter to the oci_connect() function.

If you are not seeing errors, set the PHP directive display_errors to ON, and the error_reporting directive to E_ALL|E_STRICT.

If you have a PHP code problem and are not using a debugger, you can examine variables using the PHP var_dump() function. For example:

print '<pre>';
var_dump($r);
print '</pre>';

Other Ways to Connect

In some applications, using a persistent connection improves performance by removing the need to reconnect each time the script is called. Depending on your Apache configuration, this may cause a number of database connections to remain open simultaneously. You must balance the connection performance benefits against the overhead on the database server.

Persistent connections are made with the OCI8 oci_pconnect() function. Several settings in the PHP initialization file enable you to control the lifetime of persistent connections. Some settings include:

oci8.max_persistent - This controls the number of persistent connections per process.

oci8.persistent_timeout - This specifies the time (in seconds) that a process maintains an idle persistent connection.

oci8.ping_interval - This specifies the time (in seconds) that must pass before a persistent connection is "pinged" to check its validity.

For more information, see the PHP reference manual at

http://www.php.net/manual/en/ref.oci8.php

For information about connection pooling, see Connection Pooling in OCI in the Oracle Call Interface Programmer's Guide and the Oracle Database Net Services Administrator's Guide.

Disconnecting from the Database

The PHP engine automatically closes the database connection at the end of the script unless a persistent connection was made. If you want to explicitly close a non-persistent database connection, you can call the oci_close() OCI function with the connection identifier returned by the oci_connect() call. For example:

<?php

$conn = oci_connect('hr', '<your_password>', '//localhost/orcl');
...
oci_close($conn);

...

?>

Because PHP uses a reference counting mechanism for tracking variables, the database connection may not actually be closed until all PHP variables referencing the connection are unset or go out of scope.