|
|
|
|
| |
PHP has numerous functions for connecting to Oracle databases, which is a plus because if you've spent the money to purchase an Oracle database, you'll want to be able to connect to it using this new-fangled programming language that's all the rage. The PHP Manual has the definitive list of PHP -> Oracle connectivity functions, however, you only need a few in order to make a simple connection and select some data:
|
|
OCILogon - opens a connection to Oracle; requires that the environment variable ORACLE_SID has been set and that you have a valid username and password.
|
|
OCIParse - parses a SQL statement.
|
|
OCIExecute - executes the SQL statement. |
|
|
OCINumCols - gets the number of columns used in the SQL statement. |
|
OCIFetch - gets the next row in the result of a SQL statement and places it in a results buffer.
|
|
OCIResult - gets the value of the named column, in the current result row. |
|
OCIFreeStatement - frees the resources in use by the current statement.
|
|
OCILogoff - closes the connection to Oracle.
|
|
Just for argument's sake, let's pretend that an Oracle database is already installed on your system, for which you have a valid username and password. Let's also assume that you've created a table on that database, called COFFEE_INVENTORY. The COFFEE_INVENTORY table has three columns: COFFEE_NAME, ROAST_TYPE and QUANTITY.
|
|
The rows in the COFFEE_INVENTORY table could be populated with data such as:
|
|
French Roast,dark,18 |
|
Kenya,medium,6 |
|
Ethiopian Harrar,medium,35 |
|
Sumatra,dark,8 |
|
Columbian,light,12 |
|
Now, let's do some PHP. Before you begin, you must have a valid username and password for the server named in ORACLE_SID (the server ID environment variable). Then, start your PHP code by creating a connection variable:
|
|
<?
$connection = OCILogon("username","password");
|
|
|
Test that a connection was established, and if it wasn't, print an error message and exit the program:
|
|
|
if (!$connection) {
echo "Couldn't make a connection!";
exit;
}
|
|
|
If you have made it through the connection test, the next step is to create the SQL statement. Using the COFFEE_INVENTORY table, suppose you want to view your inventory, including the name of the coffee and the roast type, with the highest number of bags listed first. Create a variable that holds your SQL statement: |
|
$sql = "SELECT COFFEE_NAME, ROAST_TYPE, QUANTITY
FROM COFFEE_INVENTORY
ORDER BY QUANTITY DESC";
|
|
|
Next, use the OCIParse function to parse the statement in the context of your database connection:
|
|
$sql_statement = OCIParse($connection,$sql);
|
|
|
The next step is to execute the statement: |
|
OCIExecute($sql_statement);
|
|
|
Now it's time to get the results; use the OCIFetch function within a while statement, to get each row in your results:
|
|
while (OCIFetch($sql_statement);
// more code here...
}
|
|
|
The code within the while loop will continue to execute as long as there are rows in the result set. So, let's put some code inside the loop!
|
|
For each row in your result set, you will have a number of columns. The number of columns is important to know, as you'll use this number to step incrementally through the data. Before your while loop, add this variable to get the number of columns used in your SQL statement: |
|
$num_columns = OCINumCols($sql_statement);
|
|
|
Return to your while loop, and place the following for expression within it, to step through your result row:
|
|
for ($i = 0; $i < $num_columns; $i++) {
$column_value = OCIResult($sql_statement,$i);
}
|
|
|
For as long as $i is less than the number of columns in the result row, the loop will execute. This loop will be repeated for every row in the result set. |
|
You'll probably want to print the results in a simple HTML table. Step back and place this statement before the while loop begins, to open the table tag and create the row headings:
|
|
echo "<TABLE BORDER=1>";
echo "<TR><TH>Coffee Name</TH><TH>Roast Type</TH><TH>Quantity</TH>";
|
|
|
Now, add some code within the while loop, to start the table row that will hold the data from the result row:
|
| |
|
Next, add a line within your for statement, to enclose each individual piece of data within inside a table cell:
|
|
echo "<TD>$column_value</TD>";
|
|
|
Before closing the while loop, close the table row: |
| |
|
The new while loop should look something like this: |
|
while (OCIFetch($sql_statement);
echo "<TR>";
for ($i = 0; $i < $num_columns; $i++) {
$column_value = OCIResult($sql_statement,$i);
echo "<TD>$column_value</TD>";
}
echo "</TR>";
}
|
|
|
After the while loop, close the HTML table:
|
| |
|
Finally, you'll want to free up the resources used to perform the query, and close the database connection. Failing to do so could cause memory leaks and other nasty resource-hogging things to occur.
|
|
OCIFreeStatement($sql_result);
OCILogoff($connection);
?>
|
|
|
The full script to perform a simple connection and data selection from an Oracle database could look something like this:
|
|
<?php
// create connection
$connection = OCILogon("username","password");
// test connection
if (!$connection) {
echo "Couldn't make a connection!";
exit;
}
// create SQL statement
$sql = "SELECT COFFEE_NAME, ROAST_TYPE, QUANTITY
FROM COFFEE_INVENTORY
ORDER BY QUANTITY DESC";
// parse SQL statement
$sql_statement = OCIParse($connection,$sql);
// execute SQL query
OCIExecute($sql_statement);
// get number of columns for use later
$num_columns = OCINumCols($sql_statement);
// start results formatting
echo "<TABLE BORDER=1>";
echo "<TR><TH>Coffee Name</TH><TH>Roast Type</TH><TH>Quantity</TH>";
// format results by row
while (OCIFetch($sql_statement);
echo "<TR>";
for ($i = 0; $i < $num_columns; $i++) {
$column_value = OCIResult($sql_statement,$i);
echo "<TD>$column_value</TD>";
}
echo "</TR>";
}
echo "</TABLE>";
// free resources and close connection
OCIFreeStatement($sql_result);
OCILogoff($connection);
?>
|
|
|
Please see the PHP Manual for additional Oracle functions (for which there are many, and they can get very complex!), and try using your own tables and SQL statements instead of the examples above. |
|
|
|
| Abstracting Oracle Connectivity with PHP and OCI8
Categories : PHP, Databases, OCI8, Oracle | | | Creating Auto-incrementing ID Fields with PHP and Oracle
Categories : PHP, PHP options/info, Databases, Oracle | | | Simple Connection to Microsoft SQL Server with PHP
Categories : PHP, MSSql, Databases | | | Custom MySQL-functions
Categories : Databases, MySQL, PHP, PHP Functions | | | Database Abstraction with PEAR
Categories : PHP, Pear, Databases | | | How To add paging (Pagination) with PHP and MySQL
Categories : PHP, Beginner Guides, Databases, MySQL, HTML and PHP | | | Simple Connection to mSQL with PHP
Categories : PHP, mSQL, Databases | | | Watching The Web
Categories : PHP, Databases, MySQL, HTTP, MD5 | | | Saving Images in MySQL
Categories : MySQL, PHP, Graphics, Databases | | | PHP 101 Part 8 of 15 : Databases and Other Animals
Categories : PHP, Beginner Guides, Databases | | | Simple Connection to PostgreSQL with PHP
Categories : PHP, PostgreSQL, Databases | | | Introduction to using PHP 5.0 and SQLite
Categories : PHP, SQLite, Databases | | | Referer Statistics
Categories : PHP, MySQL, HTTP, Databases | | | PHP 101 Part 9 of 15 : SQLite My Fire!
Categories : PHP, Beginner Guides, Databases, SQLite | | | Beginners guide to PHP and MySQL
Categories : PHP, Beginner Guides, Databases, MySQL, Installation | |
| | | | Pascal FORET wrote : 94
Bonjour Julie,
I don`t succeed to connect to an oracle database.
I ve got a web windows/apache/php3 with sqlnet.
When I use sqlnet I can connect myself to the oracle database which
is under an other PC.
When I use php I ve got the message
Fatal error: Call to unsupported or undefined function ora_logon()
I have modify my php3.ini and I have put php3_oci73.dll without ;
I don`t know how to continue `cause the manual seems to be to small !
I`d like to have some help.
Pascal | | | Julie Meloni wrote : 95
You are trying to use the ora_* family of functions while
installing the oci DLL. IOW, you`re mixed up. The manual
can help you with this. | | | Jorge Santos wrote : 151
Hi all,
I`m very frustrated now. For two days I`ve tried to
connect to oracle running on an NT server from php4.
My php script is simply to connect. I tried multiple ways:
using ora_logon, using OCILogon, with the oracle_sid
variable set to the database alias name, with the
oracle_sid not set, from php4 under IIS 4, from php4 on
my Win95 laptop under Apache, you name it, I`ve done
it. BUT I can`t get it to work.
If I make a call to phpinfo(), the display page states
that Oracle is enabled and OCI is also enabled.
The database is on a remote server. I can connect to it
using SQL Plus from either the web server (NT and IIS)
and from my Win95 laptop.
Any help would be appreciated.
Sincerely and many thanks,
Jorge | | | Anonymous wrote : 159
Go through this check list.
1. Apache or IIS running and working with php.
2. Uncomment extensions for php_oci8.dll and
php_oracle.dll on php.ini
3. Look on php.ini the extensions dir and set to . Ej.
c:\php\extensions
3. Create a environment variable on My Computer
properties/Environment tab. ORACLE_HOME=PATH; Ej.
C:\orant
on WIN9X add to Autoexec.bat this line:
set ORACLE_HOME=Path
4. The connection code start like this:
<?php
$DB="PRD0";
$cx=ocilogon("username","password",$DB) or die
("Couln`t connect to database.");
...
?>
Good Luck
| | | Vladas Drobitko wrote : 168
Error in the script:
1.The row "while (OCIFetch($sql_statement);" must be
change to "while (OCIFetch($sql_statement)){"
2. The row "OCIFreeStatement($sql_result);" must be
change to "OCIFreeStatement($sql_statement);"
Good luck
| | | Anonymous wrote : 175
please can someone tell me how to set Oracle_SID
and what is meant by PutEnv("ORACLE_SID=ORASID")
and how to set Oracle_home.
thank you | | | Anonymous wrote : 195
I installed Apache/PHP4 with Oracle support on HP Unix. I tried to connect to a database on another box
with ora_logon() and I got the error message "Oracle: Connection Failed: ORA-12154: TNS:could not resolve
service name ". I can connect to that database with sqlplus. Anybody has experience on HP UNIX please
help. Thanks. | | | Anonymous wrote : 196
I am trying to connect from a NT machine to a oracle database on a HP unix machine.
the error i am getting is:
Warning: _oci_open_server: Error while trying to retrieve text for error ORA-12222 in c:\program files\apache
group\apache\htdocs\ora.php on line 11
i am able to connect to a ldap server on another machine. so it should not be a protocol error.
can some body pl help?
| | | chadi kari wrote : 264
i am trying to establish a connection between php and
an oracle database.
In the php.ini file when i am removing the comment from
extensions php_oci8.dll and php_oracle.dll The apache
server gives me the following error:
unable to load dynamic library oci8.dll and oracle.dll
although i put the 2 dll files in the folder
c:\winnt\system32 .
thank you
| | | satit prapin wrote : 319
I would like to connect database and lock row for
update by send sql command `select * from table
where ... for update nowait` and set mode
OCI_DEFAULT but it can`t work. In case I lock row pass
sql*plus program PHP can check status of row for
readonly until I commit or rollback pass sql*plus . How
can I lock row by PHP?
Thank you | |
|
|
|
