csr2html - csr2html function

Home
E-mail Us
Oracle Articles
New Oracle Articles

Oracle Training
Oracle Tips
Oracle Forum
Class Catalog

Remote DBA
Oracle Tuning
Emergency 911
RAC Support
Apps Support
Analysis
Design
Implementation
Oracle Support

SQL Tuning
Security
Oracle UNIX
Oracle Linux
Monitoring
Remote support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

Oracle Database Tips by Donald Burleson

csr2html

The next script, abundantly used throughout the tool, defines the function "csr2html", used to present a relational cursor as an HTML table. Here is the content of the csr2html.php script:

<?php
require_once('helper.inc.php');
require_once "HTML/Table.php";
function csr2html(&$sth,$fill="n/a") {
   GLOBAL $rattrib;
   $ncols=$sth->FieldCount();
   for($i=0; $i<= $ncols;$i++) {
      $cols[$i]=$sth->FetchField($i);
   }
   $tableAttrs = array("rules" => "rows,cols",
                       "border" => "3",
                       "align" => "center" );
   $hattr=array("style" => "background-color: #ADD8E6");
   $table = new HTML_Table($tableAttrs);
   $table -> setAutoGrow(true);
   $table -> setAutoFill($fill);
   for($i=0;$i<$ncols;$i++) {
      $table->setHeaderContents (0,$i,$cols[$i]->name);
   }
   $table->setRowAttributes(0,$hattr);
   while ($row=$sth->fetchRow()) {
      $table->addRow($row,$rattrib);
   }
   ?>
   <center>
   <?=$table->toHTML()?>
   </center>
<?php
}
?>

This is the same example shown in Chapter 4 with very few changes. The only change is the global array $rattrib, added later in the coding phase to allow me to align table values differently in different scripts. It was not created as an argument to the function because of simplicity. This was the simplest solution. This function is used in every menu item throughout the tool. The login form is the same as in Chapters 3 and 4 with a bit more elaborate look:

Login_form.php

<?php function login_form($init_usr) { ?>
<p>
<center>
<h2>DBA Helper</h2>
<h5>
You are running for the shelter of DBA's little helper
<br>
It will help you on your way, get you through your busy day
</h5>
<hr>
<br>
</center>
</p>
<form action=<?=$_SERVER['PHP_SELF']?> method="post">
<table
   cellpadding="0"
   cellspacing="0" border="2"
   align="center"
   bgcolor="#10ADF4">
<th align="center">
<td colspan="2" align="center">Login:</td>
</th>
<tr>
<td>Username:</td>
<td><input type="text"
      name="user"
      value= <?php
          if (!empty($_POST['user']))
               echo $_POST['user'];
               else echo "$init_usr";
           ?>
      size="20"
     maxlength="32">
</td>
</tr>
<tr>
<td>Password:</td>
<td><input type="password" name="passwd" size="20"></td>
</tr>
<tr>
<td>Database:</td>
<td><input type="text" name="database" size="20"
maxsize=32></td>
</tr>
<tr>
<td><input type="submit" name="login" value="login"></td>
</tr>
</table>
</form>
<br>
<hr>
<?php } ?>

The only difference from the login form used throughout this book is the fact that this form is placed within a table, giving it a distinctive look. The same applies to the main menu. The procedure dba_helper.php does the same thing as Example 13 and its modifications; it checks the database login, starts the session and stores the connection parameters as session variables. When everything is checked, it uses the "header" function to display the following HTML file, appropriately named "frames.html":

frames.html

<html>
<head>
<title>DBA Helper Main Menu</title>
</head>
<frameset cols="20%,80%">
<frame name="menu" src="main_menu.html">
<frame name="output" src="init.php">
<noframes>
<center>
<h2>
Sorry, this tool can not be used if your browser<br>
does not support frames
</h2>
</center>
</noframes>
</frameset>
</html>

This creates the frames and invokes the initial screen, showing the main menu and the output of the script "init.php". The next interesting script is "init.php". It is a very small script, but with some interesting elements:

<html>
<body bgcolor="#EFECC7">
<?php
require_once('helper.inc.php');
$pat='/^CORE\s+([0-9]+)/';
session_start();
$DSN=$_SESSION['DSN'];
$ADODB_FETCH_MODE=ADODB_FETCH_NUM;
$db = NewADOConnection("oci8");
$VER='select * from v$version';
$TIM="select to_char(startup_time,'MM/DD/YYYY HH24:MI:SS')
      from v\$instance";
try {
    $db->Connect($DSN['database'],
                 $DSN['username'],
                 $DSN['password']);
    echo "<h4>Connected to
database:".strtoupper($DSN['database'])."</h4>"."\n";
    echo "<pre>\n";
    $rs1=$db->Execute($VER);
    while($row=$rs1->FetchRow()) {
        echo "\t".$row[0]."\n";
        if (preg_match($pat,$row[0],$match)) {
           $_SESSION['version']=$match[1];
        }
    }
    $rs2=$db->Execute($TIM);
    $row=$rs2->FetchRow();
    $_SESSION['startup']=$row[0];
    $db->close();
}
catch (Exception $e) {
    die($e->getTraceAsString());
}
?>
</pre>
</body>
</html>

This script displays the database name and version, and stores the major version of the database and the date that the instance was started as session variables. The interesting part is the use of the preg_match PHP function which performs a Perl regular expression match. The syntax of the function is shown as follows:

preg_match($pattern,$string,$matches)

This searches the string, $string for a pattern, $pattern and puts the resulting matches in the array, $matches. The preg_match function stops searching the array as soon as the first match is encountered. If we need to search the entire string $string, the preg_match_all function is used instead of preg_match. Our pattern was defined by the expression $pat='/^CORE\s+([0-9]+)/'; which means this:

"The string that begins with CORE followed by one or more space characters which are, in turn, followed by one or more digits". Digits are remembered as $match[1] because of the parenthesis around the expression for "one or more digits", "[0-9]+". Regular expressions are quite complicated topic in itself and is even a subject of a separate book. The classic reference for everything about the regular expressions is found in the book by Jeffrey Friedl, "Mastering Regular Expression", published by O'Reilly Publishers.

The short introduction to the Perl variety of regular expressions, also used by PHP, is the llama book, "Learning Perl" by Randal Schwartz and Tom Christiansen. It was also published by O'Reilly Publishers and has a llama on the cover page. Perl regular expressions are implemented in PHP by using the PCRE library (Perl Compatible Regular Expressions) which is used by many software products, so learning how to use Perl regular expressions makes a lot of sense and is not a wasted effort.

On-line information is also available at well known web sites, such as http://www.pcre.org and http://www.regular-expressions.info. The init.php file outputs the initial page seen earlier in this chapter.

Also worth noticing is the structure of the file. The file begins with <html> and <body> tags, and the background color is set in the <body> tag. This is the simplest way to set the background color and all scripts comprising DBA_Helper use this method for setting the background colors.

See code depot for complete scripts

This is an excerpt from the book Easy Oracle PHP. You can get it for more than 30% by buying it directly from the publisher and get instant HTML-DB scripts from the code depot: