Combining an Oracle Database with PHP to Manage Data

PHP is the one of the most commonly used scripting languages. The Oracle database is the most commonly used commercial database (according to Gartner, Inc.). PHP and the Oracle database make a great combination suitable for developing web applications. The PHP Oracle extension may be used to connect to Oracle database 10g, 9i, and 8i by using the Oracle Call Interface (OCI). The PHP extension for the Oracle database is included in the PHP 5 installation. The Oracle PHP extension provides features such as connection caching (pooling), privileged connections using external credentials, statement caching, and row pre-fetching. In this article, you will install PHP’s OCI extension for Oracle database, decide which connection method to use, create a database, and retrieve data from that database and display it in a table.

Installing the Oracle Extension

First, install PHP 5 and Apache HTTP Server 2. Add C:/PHP, the directory in which PHP 5 is installed, to the PATH environment variable. Rename the php.ini-recommended.configuration file in the C:/PHP directory to php.ini. The example application in this article will store and fetch data from Oracle database 10g. Therefore, enable the Oracle database extension in php.ini configuration file by removing the ‘;’ from the following line.

extension=php_oci8.dll

Set the extension directory directive in php.ini to the directory containing the PHP extensions.

extension_dir = "./ext"

Next, configure Apache HTTP Server 2 with PHP 5 by adding the following configuration directives to <Apache2>/conf/httpd.conf file; <Apache2> being the directory in which Apache 2 web server is installed.

# For PHP 5
LoadModule php5_module "C:/PHP/php5apache2.dll"
AddType application/x-httpd-php .php

# configure the path to php.ini
PHPIniDir "C:/PHP/"

You need to restart the Apache 2 server. Install the Oracle database 10g, including the sample schemas. The OCI8 extension provides various configuration directives for the php.ini file. Some of the commonly used configuration directives are discussed in Table 1.

Table 1: OCI8 Configuration Directives

If any of the configuration directives is modified, restart the Apache2 server. The Oracle OCI8 PHP class library provides various functions to connect to the Oracle database, run SQL statements, and retrieve result sets. Some of the commonly used OCI8 PHP 5 functions are discussed in Table 2. All the OCI8 PHP functions return FALSE on error.

Table 2: OCI8 Extension Functions

Which Connection Method to Use

The OCI8 PHP class library provides the oci_connect(), oci_pconnect(), and oci_new_connection() methods to connect to the Oracle database. If a non-persistent connection is required that is cached, use the oci_connect() method. When the oci_connect() method is invoked with the same parameters more than once in the same script, the same connection resource is returned. Caching of connections improves connection efficiency. The cache used by oci_connect() is deallocated when a script has run or when the connection resource is closed. If transactional isolation between two sets of queries is required, use the oci_new_connection() method; this returns a new connection. A connection returned by oci_new_connection() is not cached. If a persistent connection that is cached across requests is required, use the oci_pconnect() method. A persistent connection does not get closed when the script has run or with the oci_close(resource connection) method. A persistent connection reduces the overhead of opening and closing connections, thus improving performance. However, if an application connects to the Oracle database using a different set of credentials for different web users, the number of connections in the persistent cache increases to the point that the performance of the Oracle server decreases due to the many idle connections. Tune the persistent connections by setting a limit on the maximum number of persistent connections in the cache with the oci8.max_persistent configuration directive, and reducing the number of idle persistent connections with the oci8.persistent_timeout configuration directive.

Creating a Database Table

In this section, you will create an Oracle database table. Create a PHP script, createOracleTable.php, in the C:/Program Files/Apache Group/Apache2/htdocs directory, the document root directory of Apache web server. Define variables $username, $password, and $db for the Oracle database username, password, and database. Specify the $db variable value as the database SID value in the <Oracle10g>/NETWORK/ADMIN/tnsnames.ora file.

$username='OE';
$password='pw';

$db='(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
   )
)';

If the HOST, PORT, or SERVICE_NAME are different than those specified in the PHP script, modify the settings in the script. Use the oci_connect() method to connect with the database.

$connection = oci_connect($username, $password, $db);

If an error occurs in obtaining a connection with the Oracle database, retrieve the error using the oci_error() function and output an error message.

if (!$connection) {
   $e = oci_error();
   echo htmlentities($e['message']);
}

Prepare an Oracle statement to create a database table using the oci_prepare() function.

$stmt = oci_parse($connection,
   "CREATE TABLE OE.Catalog(CatalogId VARCHAR(25) PRIMARY KEY,
   Journal VARCHAR(25), Publisher Varchar(25), Edition VARCHAR(25),
   Title Varchar(45), Author Varchar(25))");

Execute the SQL statement using the oci_execute() method.

$r = oci_execute($stmt);

If an error occurs in generating the table, retrieve the error using the oci_error() function and output an error message.

if (!$r) {
   $e = oci_error($stmt);
   echo htmlentities($e['message']);
}else{

   echo  " Created tablenn";
}

Prepare an SQL INSERT statement to add a row of data to the Catalog table using the oci_prepare() statement.

$sql = "INSERT INTO OE.Catalog VALUES('catalog1', 'Oracle Magazine',
    'Oracle Publishing', 'Nov-Dec 2004', 'Database Resource Manager',
    'Kimberly Floss')";
$stmt = oci_parse($connection, $sql);

Run the SQL statement using the oci_execute() function.

$r = oci_execute($stmt);

Similarly, add another row of data. The PHP script, createOracleTable.php, that creates an Oracle database table is presented in Listing 1.

Listing 1: createOracleTable.php

<?php
$username='OE';
$password='pw';

$db='(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
   )
)';

$connection = oci_connect($username, $password, $db);

if (!$connection) {
   $e = oci_error();
   echo htmlentities($e['message']);
}

$stmt = oci_parse($connection,
   "CREATE TABLE OE.Catalog(CatalogId VARCHAR(25) PRIMARY KEY,
   Journal VARCHAR(25), Publisher Varchar(25), Edition VARCHAR(25),
   Title Varchar(45), Author Varchar(25))");
if (!$stmt) {
   $e = oci_error($connection);
   echo htmlentities($e['message']);
}
$r = oci_execute($stmt);
if (!$r) {
   $e = oci_error($stmt);
   echo htmlentities($e['message']);
}else{

   echo  " Created tablenn";
}

$sql = "INSERT INTO OE.Catalog VALUES('catalog1', 'Oracle Magazine',
   'Oracle Publishing', 'Nov-Dec 2004', 'Database Resource Manager',
   'Kimberly Floss')";
   $stmt = oci_parse($connection, $sql);
if (!$stmt) {
   $e = oci_error($connection);
   echo htmlentities($e['message']);
}

$r = oci_execute($stmt);
if (!$r) {
   $e = oci_error($stmt);
   echo htmlentities($e['message']);
}else{

   echo " Added a rownn";
}
$sql = "INSERT INTO OE.Catalog VALUES('catalog2', 'Oracle Magazine',
   'Oracle Publishing', 'March-April 2005',
   'Starting with Oracle ADF ', 'Steve Muench')";

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

if (!$stmt) {
   $e = oci_error($connection);
   echo htmlentities($e['message']);
}
$r = oci_execute($stmt);
if (!$r) {
   $e = oci_error($stmt);
   echo htmlentities($e['message']);
}else{

   echo " Added a rownn";
}

?>

Start Apache web server, if not already started. Run the PHP script in a browser with URL http://localhost/createOracleTable.php.A database table, Catalog, gets generated and data gets added to the table, as shown in Figure 1.

Figure 1: A database table gets generated and data gets added.

Retrieving Data from the Database

Next, you will use the PHP Oracle extension to connect with the Oracle database and retrieve data from the database table Catalog that you created in the previous section. Create a PHP script, retrieveOracleData.php, in the Apache2 server’s htdocs directory. Define variables $username, $password, and $db for Oracle database username, password, and database. Specify the $db variable value as the database SID value in the <Oracle10g>/NETWORK/ADMIN/tnsnames.ora file.

$username='OE';
$password='pw';

$db='(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
   )
)';

Obtain a connection with the database using the oci_connect function.

$connection = oci_connect($username, $password, $db);

Prepare an Oracle statement to select rows of data. The oci_parse(connection, query) function is used to compile a statement. The prepared SQL statement uses a bind variable, :journal, for the JOURNAL column.

$stmt = oci_parse($connection, "SELECT * from OE.CATALOG
   WHERE JOURNAL=:journal");

Bind a value to the :journal bind variable using the oci_bind_by_name() function.

$journal='Oracle Magazine';
oci_bind_by_name($stmt, ":journal", $journal);

Run the SQL query with the oci_execute(statement) function.

$r = oci_execute($stmt);

Fetch all the rows in the result set using the oci_fetch_all(statement, result) function.

$nrows = oci_fetch_all($stmt, $results);

The oci_fetch_all function returns the number of rows in the result set. If the number of rows is more than 0, create an HTML table to output the rows in the result set. By default, the oci_fetch_all() function fetches data by column. Iterate over the rows, and add the column data to the HTML table.

for ($i = 0; $i < $nrows; $i++) {
   echo "<tr>n";
   foreach ($results as $data) {
      echo "<td>$data[$i]</td>n";
   }
   echo "</tr>n";
}

You used the oci_fetch_all() method to retrieve data from the result set. But, one of the other fetch methods; oci_fetch(), oci_fetch_row(), oci_fetch_array(), or oci_fetch_object(), or oci_fetch_assoc() also may be used. The PHP script retrieveOracleData.php is shown in Listing 2.

Listing 2: retrieveOracleData.php

<?php

$username='OE';
$password='pw';

$db='(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
   )
)';

$connection = oci_connect($username, $password, $db);
$stmt = oci_parse($connection, "SELECT * from OE.CATALOG
   WHERE JOURNAL=:journal");

$journal='Oracle Magazine';
oci_bind_by_name($stmt, ":journal", $journal);

$r = oci_execute($stmt);

$nrows = oci_fetch_all($stmt, $results);

if($nrows>0){
echo "<table border><tr><th>Catalog

Id</th><th>Journal</th><th>Publisher</th><th>Edition</th>
   <th>Title</th><th>Author</th></tr>";

for ($i = 0; $i < $nrows; $i++) {
   echo "<tr>n";
   foreach ($results as $data) {
      echo "<td>$data[$i]</td>n";
   }
   echo "</tr>n";
}

echo "</table>";

}

?>

Next, run the retrieveOracleData.php script in the Apache web server with the URL http://localhost/retrieveOracleData.php. The Oracle database data gets retrieved and displayed in an HTML table, as shown in Figure 2.

Oracle2.jpg Data gets retrieved and displayed in an HTML table.

Conclusion

PHP provides the OCI8 extension to connect to an Oracle database and runs SQL queries in the database. In this article, you installed the OCI8 extension, established a connection to Oracle database, created a database table, and retrieved database table data.

About the Author

Deepak Vohra (dvohra09@yahoo.com) is a Sun Certified Java Programmer and a Sun Certified Web Component Developer, and has published in devx, FTPOnline, JavaBoutique, ONJava, and java.net.