August 22, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Combining an Oracle Database with PHP to Manage Data

  • October 5, 2007
  • By Deepak Vohra
  • Send Email »
  • More Articles »

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.



Click here for a larger image.

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.





Page 4 of 5



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel