Connect Oracle Database with PHP

With PHP you can easily connect to a database, request data and display it in your Web site, or even modify the data in your database. MySQL is a very popular database, and there are many tutorials on the internet for PHP and MySQL. MySQL is free, which may attract a lot of people. Because of its wide application, I do not want to repeat the use of MySQL here. Oracle is used extensively in enterprise applications, so we use Oracle to describe the connection between PHP and the database. We're certainly not talking about the design of Oracle databases because this is beyond our scope of discussion.

PHP provides two sets of functions to connect with Oracle, namely the Ora_ and OCI functions. Where the Ora_ function is slightly stale. The OCI function update is said to be better. The use of the two grammars is almost the same. Your PHP installation options should be able to support the use of both.

Connection

<? if ($conn =ora_logon ("User@tnsname", "PassWord"))
{echo "SUCCESS! Connected to database\n ";
}else
{echo "Failed:-( Could not connect to database\n ";}
Ora_logoff ($conn);
Phpinfo ();
?>

The above code connects to the database using the Oracle database name, user name, and password defined by Tnsname (indicated in your Tnsnames.ora file). On the basis of a successful connection, the Ora_logon function returns a nonzero connection ID and stores it in the variable $conn.

Inquire

Assuming the connection with the database is ready, let's take a look at the actual application of the database query. The following code shows a typical example of a connection and query:

function Printoraerr ($in _cur)
{
Check if Oracle is faulted
Display if there is an error
This function is called every time Oracle is requested when the pointer is activated
if (Ora_errorcode ($in _cur))
echo "Oracle code-". Ora_error ($in _cur). " \ n ";
Return
}
if (! ( $conn =ora_logon ("User@tnsname", "password")))
{echo "Connection to Database failed\n";
Exit
}
echo "Connected as connection-$conn \ n";
echo "Opening cursor ... \ n";
$cursor =ora_open ($conn); Printoraerr ($cursor);
echo "opened cursor-$cursor \ n";
$qry = "Select user,sysdate from Dual";
echo "parsing the query $qry ... \ n";
Ora_parse ($cursor, $qry, 0); Printoraerr ($cursor);
echo "Query parsed \ n";
echo "Executing cursor ... \ n";
Ora_exec ($cursor); Printoraerr ($cursor);
echo "Executed cursor \ n";
echo "fetching cursor ... \ n";
while (Ora_fetch ($cursor))
{
$user =ora_getcolumn ($cursor, 0); Printoraerr ($cursor);
$sysdate =ora_getcolumn ($cursor, 1); Printoraerr ($cursor);
echo "row = $user, $sysdate \ n";
}
echo "fetched all records \ n";
echo "Closing cursor ... \ n";
Ora_close ($cursor);
echo "Closed cursor \ n";
echo "Logging off from Oracle ... \ n";
Ora_logoff ($conn);
echo "logged off from Oracle \ n";
?>

Show results

The following code shows how to query the database and output the result:

function Printoraerr ($in _cur, $conn)
{
Check if Oracle is faulted
Display if there is an error
This function is called every time Oracle is requested when the pointer is activated
If it encountered an error, we exit immediately
if (Ora_errorcode ($in _cur))
{echo "Oracle code-". Ora_error ($in _cur). "N";
Ora_logoff ($conn);
Exit
}
Return
}

function Exequery ($w _qry, $conn)
{
$cursor =ora_open ($conn); Printoraerr ($cursor, $conn);
Ora_parse ($cursor, $w _qry,0); Printoraerr ($cursor, $conn);
Ora_exec ($cursor); Printoraerr ($cursor, $conn);
$numrows = 0;
$w _numcols=ora_numcols ($cursor);
Show Head
echo "\ n";
for ($i =0; $i < $w _numcols; $i + +)
{
$align = (Ora_columntype ($cursor, $i) = = "Number")? " Right ":" Left ";
echo "\ T". Ora_columnname ($cursor, $i). "\ n";
}
echo "\ n";
while (Ora_fetch ($cursor))
{
echo "\ n";
for ($i =0; $i < $w _numcols; $i + +)
{
$align = (Ora_columntype ($cursor, $i) = = "Number")? " Right ":" Left ";
if (Ora_columntype ($cursor, $i) = = "LONG")
echo "".
Ora_getcolumn ($cursor, $i). "\ n";
Else
echo "". Ora_getcolumn ($cursor, $i). "\ n";
Printoraerr ($cursor, $conn);
}
$numrows + +;
echo "\ n";
}
if ($numrows ==0)
echo "Query returned no records \ n";
Else
{
echo "\ n";
echo "Count \ n";
echo "$numrows \ n";
echo "\ n";
}
echo "\ n";
Ora_close ($cursor);
Return
}
Main program
if (! ( $conn =ora_logon ("User@sid", "password")))
{
echo "Error:cannot connect to database\n";
Exit
}
$qry = "Select
DEPTNO \ "Dept\"
, empno \ "Emp\"
, EMPNM \ "Name\"
, Salary \ "Salary\"
From
Employee
ORDER by ";
Exequery ($qry);
Ora_logoff ($conn);
?>


HTTP-based Oracle Login

Confirm the Oracle login by adding the following code to the PHP page code. Note You must set the $ SID correctly.

if (!isset ($PHP _auth_user))
{
Header ("Www-authenticate:basic realm=\" $SID \ "");
Header ("http/1.0 401 Unauthorized");
$title = "Login instructions";
echo "
Authorized to enter the site
\ n ";
Exit
}
Else
{
if (! ( $conn =ora_logon ("$PHP _auth_user@ $SID", $PHP _AUTH_PW)))
{Header ("www-authenticate:basic realm=\" $SID \ "");
Header ("http/1.0 401 Unauthorized");
$title = "Login instructions";
echo "
Authorised to enter the site
\ n ";
Exit
}}
?>

