jump to content

Next Previous Contents

4. Database Connectivity

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.

4.1 Connecting

<? 
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();
?>
The code above just connects to an Oracle database defined by TNSNAME (this entry is there in your tnsnames.ora file), using the userid user and password password. Upon successful connection, the function Ora_Logon returns a non-zero connection identifier, which we store in the variable $conn.

4.2 Querying

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"; ?>

4.3 Display Results

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);
?>

4.4 Authentication against Oracle

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;
        }
  }
?>


Next Previous Contents