With PHP, you can easily connect to a database, query for data , display results in a web site, modify the data in the database etc. MySQL is a very popular database and there are lot of tutorials available on the web about using PHP with MySQL. MySQL is free, so it might be of interest to many people. Since it is widely covered, we will not illustrate the use of MySQL here. Oracle is widely used in the enterprises and that is the database we'll use to get an idea about database connectivity. We will not cover principles of database design with Oracle, since it is beyond the scope of this talk.
PHP provides for two sets of functions to work with Oracle, viz., ora_ and OCI functions. Functions which start with ora_ are slightly older set. OCI functions are newer and claimed to be better. There is not much difference in the syntax between these two sets. As mentioned before, your PHP installation must've support for these functions to be tried out.
For a paper on obtaining and installing Apache with PHP3 on Microsoft Windows platform, and to get the basics of working with Oracle, you can check my short article on that.
<?
if ($conn=Ora_Logon("user@TNSNAME","password")) {
echo "<B>SUCCESS ! Connected to database<B>\n";
} else {
echo "<B>Failed :-( Could not connect to database<B>\n";
}
Ora_Logoff($conn);
phpinfo();
?>
Let us assume that the connection step went smoothly. Let us fire some queries against the database. The following code illustrates a typical page which connects and queries an Oracle database.
<?
/*
* connect to database and execute a query
*/
function printoraerr($in_cur){
// function to check whether an oracle error occured
// if it did, print the error
// call this after every oracle call when a cursor is active
if(ora_errorcode($in_cur))
echo "Oracle code - ".ora_error($in_cur)."
\n";
return;
}
/** main */
if (!($conn=ora_logon("user@TNSNAME","password"))) {
echo "Connection to database failed\n";
exit;
}
echo "Connected as connection - <b>$conn</b><br>\n";
echo "Opening cursor ...<br>\n";
$cursor=ora_open($conn); printoraerr($cursor);
echo "Opened cursor - <b>$cursor</b><br>\n";
$qry="select user,sysdate from dual";
echo "Parsing the query <b>$qry</b> ...<br>\n";
ora_parse($cursor,$qry,0); printoraerr($cursor);
echo "Query parsed <br>\n";
echo "Executing cursor ...<br>\n";
ora_exec($cursor); printoraerr($cursor);
echo "Executed cursor<br>\n";
echo "Fetching cursor ...<br>\n";
while(ora_fetch($cursor)){
$user=ora_getcolumn($cursor,0); printoraerr($cursor);
$sysdate=ora_getcolumn($cursor,1); printoraerr($cursor);
echo " row = <B>$user, $sysdate </B><br>\n";
}
echo "Fetched all records<br>\n";
echo "Closing cursor ...<br>\n";
ora_close($cursor);
echo "Closed cursor<br>\n";
echo "Logging off from oracle... <br>\n";
ora_logoff($conn);
echo "Logged off from oracle <br>\n";
?>
The next code section shows how to fire generic queries against the database and display the results in a tabular form.
<?
function printoraerr($in_cur, $conn){
// function to check whether an oracle error occured
// if it did, print the error
// call this after every oracle call when a cursor is active
// If it encountered an error, we exit immediately
if(ora_errorcode($in_cur)) {
echo "Oracle code - ".ora_error($in_cur)."<br>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);
// print headers
echo "
<TABLE WIDTH=\"100%\" BORDER=\"0\" CELLSPACING=\"1\" CELLPADDING=\"2\">
<TR>\n";
for ($i=0;$i<$w_numcols;$i++) {
$align=(ora_columntype($cursor,$i)=="NUMBER")?"RIGHT":"LEFT";
echo "\t<TH VALIGN=TOP ALIGN=$align>".ora_columnname($cursor,$i)."</TH>\n";
}
echo "</TR>\n";
while(ora_fetch($cursor)){
echo "<TR>\n";
for ($i=0;$i<$w_numcols;$i++) {
$align=(ora_columntype($cursor,$i)=="NUMBER")?"RIGHT":"LEFT";
if(ora_columntype($cursor,$i)=="LONG")
echo "<TD VALIGN=TOP ALIGN=$align><PRE>".
ora_getcolumn($cursor,$i)."</PRE></TD>\n";
else
echo "<TD VALIGN=TOP ALIGN=$align>".ora_getcolumn($cursor,$i)."</TD>\n";
printoraerr($cursor,$conn);
}
$numrows++;
echo "</TR>\n";
}
if ($numrows==0)
echo "<TR><TD COLSPAN=\"$w_numcols\"><B>Query returned no records
</B></TD></TR>\n";
else {
echo "<TR>\n";
echo "<TH COLSPAN=\"".($w_numcols-1)."\" ALIGN=RIGHT>Count</TH>\n";
echo "<TH ALIGN=RIGHT>$numrows</TH>\n";
echo "</TR>\n";
}
echo "</TABLE>\n";
ora_close($cursor);
return;
}
// main
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 1,2";
exequery($qry);
ora_logoff($conn);
?>
Add this to the beginning of the code to validate oracle login. You must specify $SID properly.
<? if(!isset($PHP_AUTH_USER)) {
Header("WWW-authenticate: basic realm=\"$SID\"");
Header("HTTP/1.0 401 Unauthorized");
$title="Login Instructions";
echo "<blockquote>
You are not authorised to enter the site
</blockquote> \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 "<blockquote>
You are not authorised to enter the site
</blockquote> \n";
exit;
}
}
?>
Since you are seeing this, it means that your browser does not support cascading style sheets. Please download and use one of the many browsers that support web standards.