Using Apache, PHP and InterBase to develop websites

<< Beginners' guide to Firebird and PHP | Database technology articles | InterBase and Java: making the connection >>

Using Apache™, PHP and InterBase® to develop
websites

  1. Introduction
  2. Installing Apache™ with PHP3 support
  3. Introduction to PHP programming
  4. The basics
  5. A word on variable scope
  6. Connecting to InterBase®
  7. InterBase® Transactions in PHP3
  8. Executing queries
  9. Authenticating users
  10. Displaying the data
  11. Displaying text blobs
  12. Displaying binary blobs
  13. Updating tables in the database

Using Apache™, PHP and InterBase® to develop websites

By Vince Duggan, Borland Developers Conference 2000

Introduction

There are three main players in the web server market: Apache™, IIS, and Netscape. The top sites use these three about equally, although Apache™ dominates in the overall web server market. Of the top sites, about 70% use CGI programming. The three main scripting extensions used are ASP, Perl and PHP. The source of these stats is Nathan Wallace at www.e-gineer.com.

PHP3 and PHP4 have InterBase® 5.x and InterBase® 6 support respectively and allow easy development of websites which interact with InterBase® .

Installing Apache™ with PHP3 support

Most installations of Linux have the Apache™ package on the CD, and most default installs will install Apache™. However, I suggest you remove that package, and then download the latest versions of Apache™ and PHP(3 or 4, depending on whether you need InterBase® 5 or InterBase® 6 support). Here is a very short list of the steps required. These steps assume that Apache™ has been downloaded and extracted into /usr/local/etc/httpd, and PHP has been put into /usr/local/etc/php. Furthermore, the Apache™ configuration files, and PHP's inititialization file go into /www/conf.

The latest Apache™ build can be found at Apache™'s ftp site while PHP can be downloaded from PHP's ftp site.

First configure the Apache™ makefiles: 

 cd /usr/local/etc/httpd
 ./configure --prefix=/usr/local/etc/httpd --sysconfdir=/www/conf

Now make the executables: this can take quite a few minutes. 

 make

Now install Apache™: 

 make install

Now configure PHP3 with support for Apache™ and InterBase®, then make and install the package: 

 cd /usr/local/etc/php
 ./configure --with-apache=../httpd --with-interbase=
 /usr/interbase --with-config-file-path=/www/conf --enable-track-vars
 make
 make install

Now we need to rerun the Apache™ configuration program, this time with PHP3 support, run make again, and make install again.

The previous invocation of configure created a script config.status which can be run to re-configure Apache™ with the same options. We need to do that, as well as add a new option for PHP3 support, then make again, and install again: 

 cd /usr/local/etc/httpd
 ./config.status --activate-module=src/modules/php3/libphp3.a

Now redo the make sequence: 

 make
 make install

Now we need to put the default PHP3.INI file in the correct place: 

 cd /usr/local/etc/php
 cp php3.ini-dist /www/conf/php3.ini

We need to configure the Apache™ httpd.conf file in order that Apache™ will pass certain files extensions to PHP3: 

 cd /www/conf

Now edit httpd.conf, and uncomment the lines referring to PHP3: 

 AddType application/x-httpd-php3 .php3
 AddType application/x-httpd-php3-source .phps

You can add other extensions if you like: 

 AddType application/x-httpd-php3 .phtml

Apache™ and PHP are now installed. Apache™ needs a fair amount of configuring before you can build your website.

Go to Synectics Software for instructions on how to do this. There are also links to other sites which have much more detail.

back to top of page

Introduction to PHP programming

PHP3 and InterBase® is remarkably easy to use. However the manner of programming CGI apps is quite a paradigm shift from ordinary programming in the Windows or Unix/Linux world. This little app that I am going to use to demonstrate some techniques is based on the standard EMPLOYEE.GDB database supplied with InterBase®. I have added one field (PIC BLOB) to the EMPLOYEE table, which I populated with some jpeg images. A backup of this database is included in the supplied source code.

I also added a couple of tables to cater for the authentication (login) side of things, but these are explained as we go along.

In a nutshell, the app allows one to login to the database within your favourite browser, and then examine the details of those employees to whom you have been granted access. In this simple application, rights to view employees is granted on the departmental level.

There is also a page where the user is allowed to modify the salary of the employee being viewed. This is then updated in the database.

back to top of page

The basics

When requesting a document from a web server with a file extension recognized by Apache™ as being a PHP3 document (usually .php3 or .phtml), Apache™ runs the document through the PHP3 module, and PHP3 decides what to present to the browser. If some or all of the contents of the documents is within the PHP3 start and end tags (<?php and ?>), then PHP3 will execute that portion as a PHP3 script. The rest of the document, usually ordinary HTML and/or other scripting languages like JavaScript, is passed through to the browser.

So an example would look something like this: 

 <HTML>
 <BODY>
 <P><H2><A NAME="basics">The Basics</A></H2>
 <P>When requesting a document from a web server etc etc etc

 <?php
 $SECTIONNO=6;
 include ("showcomments.php3");
 ?>

 <P><H2><A NAME="scope">A Word about Variable Scope</A></H2>
 <P>Like all programming languages etc etc etc 

 <?php
 $SECTIONNO=7;
 include ("showcomments.php3");
 ?>  

 </BODY>
 </HTML>

Within the PHP3 start and end tags, you have full access to the PHP3 scripting language. Most commonly one writes code here to access data, and then one generates HTML which displays this data. PHP3 has a function called , and any text which is echo'ed is sent to the browser. Typically one echoes HTML or plain text. Here is an example: 

 <?php
 $MyColor="red";
 echo "<FONT color=$MyColor>This is written in RED</FONT>"; 
 ?>

This displays in the browser like this:

This is written in RED

You will see that I had a variable called $MyColor (all variables start with $) which contains the value of the color I want to use. This could of course have been looked up in a database. PHP3 then substitutes the value of the variable whenever required.

If you look at the page source in your browser, you won't find any references to the PHP3 code. The PHP3 module does not pass it on to the end user's browser. Only the output (if there is any) of the script is sent to the browser. So if you embed usernames and passwords in the code, this will not be seen by the end user.

One of the problems with CGI applications is Variable Scope. In other words, if a variable is declared in a particular piece of PHP3 script, where can that variable be accessed? How are variables passed from one page to another? This is answered in the next section.

back to top of page

A word on variable scope

Here are the basic rules for accessing variables:

  • Any variable declared in a particular PHP3 document (eg tutorial.php3) is available throughout that document.
  • If another document is included (using the PHP3 include function), then the variable is available inside the included document as well.
  • If a variable is declared inside an included document, then that variable is available in the outer document, in code that occurs after the include statement.
  • When a script has finished executing, then all variables vanish.
  • In PHP3 there is a way to make variables survive, and therefore be available in the next call to a PHP3 script, but this makes things overly complex in my opinion, and doesn't work well on Unix/Linux where there are typically a number of httpd daemons running, and you may not connect to the same one each time.
  • PHP4 implements sessions, which may make this easier. However, at time of writing, PHP4 had not yet been released.

So, if a script is invoked, and it declares a bunch of variables, and displays a page of HTML, and one of the links in this page invokes another script, then the variables from the first script are not available in the next script. If you need to pass values from one page to another, then pass these values in the URL, or use the standard HTML FORM with a POST button. Both of these methods are used in the sample application. Note that when a page is POSTed, only the normal HTML INPUT boxes etc. are passed to the new page, not any PHP3 variables. However, the receiving page can access the passed values as ordinary PHP3 variables. So if we had a simple form as follows: 

 <FORM METHOD="post" ACTION="page2.php3?SECTIONNO=3">
 <INPUT TYPE="text" name="NAME" size="50" >
 <INPUT TYPE ="submit" Value="Submit" name="submitbtn">

Then the receiving script (page2.php3) would have access to two variables, $SECTIONNO with a value of 3, and $NAME with a value of whatever had been typed in. The former was passed in the URL, while the latter was POSTed.

back to top of page

Connecting to InterBase®

There are two functions that can be used to connect to InterBase® : 

 ibase_connect
 ibase_pconnect

The first version creates a new connection to the database, while the second creates a persistent connection to the database. When ibase_pconnect is executed, PHP3 looks to see if there is already a similar connection available, and connects to that. If no similar connection is found, then a new one is created.

Apache™ on Linux typically has a number of httpd daemons running to service incoming requests. Each process therefore will have its own persistent connections, which will remain open until Apache™ is stopped or re-started. I have encountered problems with the InterBase® connection timing out when connecting via the network. So if PHP3 was to connect to localhost:/somedir/some.gdb I found that the connection had timed out after a few minutes, and either PHP3 would pause for a long while, hang, or an error would be returned.

In the sample application, all connections use ibase_pconnect, and the connections are to a local database, so the timeout is not encountered.

Note: that since connections remain open, extra care must be taken to ensure that transactions are completed, otherwise long running transactions will create performance problems and record locking problems.

Each php3 page must reconnect to the database, either by using ibase_connect or ibase_pconnect. In the sample application, each page includes a file called header.php3, which displays the common header information. Header.php3 in turn includes checklogin.php3, which is there to ensure that each page is being accessed by a logged in browser. The first thing that checklogin.php3 does is to connect (with ibase_pconnect) to the database. This means that header.php3 has a valid connection, and in turn the page that included header.php3 also has a valid connection to the database.

back to top of page

InterBase® Transactions in PHP3

The InterBase® support within PHP3 allows for a default transaction. Each function that works within a transaction, such as ibase_query will work within the default transaction unless told otherwise. If the default transaction has not been started, then these functions will start it. Here is a fragment of code that uses the default transaction: 

 <?php
 ibase_pconnect("/home/wwwroots/synectics/php3/employee.gdb",
 "sysdba", "masterkey" ); 
 $Q = ibase_query("select retval,user_name 
 from checklogin(?,?)",$LOGINID,$IP);
 $R = ibase_fetch_row($Q);
 $USER_NAME = $R[1];
 ibase_free_result($Q);
 ibase_commit();
 ?>

The default transaction will be sufficient for most purposes. If you need to have a number of different transactions open, then look at ibase_trans, ibase_query and Default Transaction.

back to top of page

Executing queries

There are two main methods of executing queries within PHP3. The simplest is to use ibase_query. This allows you to define the query either with or without parameters, and at the same time, supply the values to the parameters. Here is a small sample: 

 <?php
 ibase_pconnect("/home/wwwroots/synectics/php3/employee.gdb",
 "sysdba", "masterkey" ); 
 $LOGINID = "frank";
 $IP = $REMOTE_ADDR;
 $Q = ibase_query("select retval,user_name from 
 checklogin(?,?)",$LOGINID,$IP);
 $R = ibase_fetch_row($Q);
 $USER_NAME = $R[1];
 ibase_free_result($Q);
 ibase_commit();
 ?>

Here the two parameters are supplied with values. This means that it is easy to run the same query with different values each time. For the most part, queries are executed once each time a page is called, so this is the easiest way to execute queries. If, however, a query needs to be run many times each time a page is executed, inside a loop for example, then it would be better to use the second method of executing queries, which uses two functions: ibase_prepare and ibase_execute. Here is a sample of this method: 

 <?php
 ibase_pconnect("/home/wwwroots/synectics/php3/employee.gdb",
 "sysdba", "masterkey" ); 
 $Q = ibase_prepare("select total from salary where unique_id = ?");
 $TOTAL = 0;
 for ($I=1;$I < 10;$I++)
 {
 $R = ibase_execute($Q,$I);
 $ROW = ibase_fetch_row($R);
 $TOTAL += $ROW[0];
 }
 ibase_free_result($R);
 ibase_commit();
 ?>

This is much more efficient than putting an ibase_query within the loop, since the prepare phase is a very expensive operation. In fact, for short efficient queries, the prepare phase may take twice as long as the execute.

back to top of page

Authenticating users

There are a number of ways to authenticate users using HTML, HTTP and so on. My sample does not use any of these methods, but rather implements a simple way of verifying users via passwords and IP addresses.

Simply put, once the user logs in, a loginid is generated. This loginid is passed from page to page, and the first thing that each page does, is check in the database whether this loginid is still current, and whether the page has been requested by the same IP address as originally logged in. If no activity has been noted for a particular loginid for 10 minutes, then it expires, and the user is requested to log in again.

If the loginid is noted to come from a different IP address, i.e. someone borrowed the URL from someone else, then access is denied for all pages, and the IP address can be logged, and action can be taken.

Like most authentication systems, this one is secure as long as the passwords are kept secret, and as long as the web server and database servers are secure in themselves.

I created a stored procedure which does most of the work: 

 create procedure checklogin (loginid integer,
 ipaddress varchar(15))
 returns (retval integer,user_name varchar(60)) 
 as
 declare variable tmpipaddress varchar(15);
 begin
 /*retval = 0 = OK
 retval = 1 = expired or invalid id
 retval = 2 = wrong ipaddress
 */
 begin
 delete from logins
 where lastaccess < (cast('now'? as date) - 0.007);
 when any do
 tmpipaddress = tmpipaddress;
 end
 retval = 1;
 tmpipaddress = "";
 select ipaddress,user_name
 from logins
 where loginid = :loginid
 into :tmpipaddress,:user_name;
 if (tmpipaddress = "") then
 retval = 1; /*expired or invalid id */
 else if (tmpipaddress = ipaddress) then
 begin
 retval = 0; /*O.K. valid login from correct ipaddress*/
 update logins set lastaccess = 'now' where loginid = :loginid;
 when any do
 tmpipaddress = tmpipaddress;
 end
 else
 retval = 2; /*someone is trying with a valid loginid, 
               but with an invalid ipaddress*/
 suspend;
 end^

The first thing this stored procedure does, is to delete any logins that are older than 10 minutes (0.007 of a day). Since this is the first thing that happens when retrieving any page, this effectively means that once a loginid is older than 10 minutes, the user is forced to log in again. This also means that attempted hacks into expired logns will not be seen as a hack attempt, but will merely be treated as an expired login. The principle could be modified to mark the login as expired, rather than deleting it. This will also allow better access analysis to be done, but the table could grow very large on a busy site.

Each page has an include(header.php3) at the top. This calls include(checklogin.php3), which returns whether the login is valid or not. Depending on this, an appropriate message is displayed, or the page is displayed. Here is the source to header.php3: 

 header.php3

 <?php

 /*This header file is included at the top of all pages except 
 the first, since the first does not do any validation. Each page is 
 passed the LOGINID, and each page  must first check whether this is 
 a valid login id from the same IP Address, and it has not timed out. */ 

 echo "<HTML>
        <TITLE>
        Employee System
        </TITLE>
        <BODY BGCOLOR= "white">"; 

 if (!isset($LOGINID))
    $LOGINID = -1;

 /*CHECKLOGIN check that the LOGINID is valid, and it also makes 
 sure that each page gets connected to the database */ 

 include ("checklogin.php3");

 if ($VALIDLOGIN  == 2)
 {
 echo "<BR><BR><BR><BR><BR><BR>
 <CENTER><H1><FONT color="red">
 An illegal intrusion has been noted from IP Address <BR> 
 $REMOTE_ADDR <BR> $REMOTE_HOST <BR>
 This has been logged in the Intrusion Database! <BR> 
 Please go <A HREF="page1.php3">here</A> to login.</FONT></H1></CENTER>"; 

 }
 if ($VALIDLOGIN  == 0)
 {
 echo "<BR><BR><BR><BR><BR><BR><CENTER><H1><FONT color="blue">
 Your Session has expired. Please go the <A HREF="page1.php3">
 Log On</A> page to log on again.</FONT></H1></CENTER>";
 echo "<CENTER><H3>This happens when your session has been inactive 
 for more than 10 minutes, and is a security measure to prevent 
 unauthorised viewing of your data.</H3></CENTER>"; 

 }
 ?>

You will see that it first calls checklogin.php3 and then depending on the value returned, displays an appropriate error, or falls out, and the relevent page displays. Here is the code to checklogin.php3: 

 checklogin.php3

 <?php
    $VALIDLOGIN = 0;
    ibase_pconnect("/home/wwwroots/synectics/php3/employee.gdb",
    "sysdba", "masterkey" ); 

    if (ibase_errmsg())
    {
       echo "error: " . ibase_errmsg();
       exit;

    }
    $IP = $REMOTE_ADDR;


    $Q = ibase_query("select retval,user_name from 
    checklogin(?,?)",$LOGINID,$IP);
    $R = ibase_fetch_row($Q);
    $RETVAL = $R[0];
    $USER_NAME = $R[1];
    ibase_free_result($Q);
    ibase_commit();

    /*Please excuse the clumsiness here: left hand not 
      knowing what the right is doing :-) */

    if ($RETVAL ==  0)
    {
      $VALIDLOGIN = 1;
    }
    else if ($RETVAL == 2)
    {
        $VALIDLOGIN = 2;
    }
    else if ($RETVAL==1)
    {
       $VALIDLOGIN = 0;
    }
 ?>

back to top of page

Displaying the data

Displaying data is pretty straight-forward. If the data contains HTML text, or if it is text that needs no formatting, then just use the echo function to pump it through to the browser. Another common way of displaying data retrieved from a table is inside an HTML table. This is quite easy: simply create the table with the correct HTML tags, and in a loop print the data inside the row (<TR>) and data (<TD>) tags.

Here is a snippet of code that does this: 

 echo "
 <TABLE >
 <TR>
 <TH>Emp No</TH>
 <TH>Name </TH>
 <TH>Dept No </TH>
 ";


    $Q = ibase_query("select empno,surname,department 
    from employee");
    while ($R = ibase_fetch_row($Q)) 
    {
       echo "
       <TR>
           <TD> $R[0]</TD>
           <TD> $R[1]</TD>
           <TD> $R[2]</TD>
       </TR>

      ";

    }
    ibase_free_result($Q);
    ibase_commit(); 

 echo "
 </TABLE>";

back to top of page

Displaying text blobs

For the most part, text blobs need to be displayed as is, i.e. no further formatting is required. If the text has already been formatted with HTML, then it's straight-forward. If not, then it's probably best to display the text within simple HTML formatting tags, such as a TABLE or inside a TEXTAREA box.

There are two ways to retrieve blob data, for both text or binary data. Both require the blob to be retrieved from the database with a query. The first uses the ibase_blob_echo function, which will be demonstrated in this section, while the second fetches the blob data into a local variable, and then displayed from the local variable. This is demonstrated in the next section Displaying binary blobs. The second method is very useful for text blobs as well.

Here is a snippet of code, which retrieves a text blob, and echos it to the browser: 

 echo "<TABLE>"; 

      $S =  "select proj_name,proj_desc from 
      EMPLOYEE_PROJECT pe, project p ";
      $S .= "where pe.emp_no = ? and p.proj_id = pe.proj_id";
      $Q1 = ibase_query($S,$EMPNO);
      while ($R1 = ibase_fetch_row($Q1))
      {
         echo "
         <TR><TD>Project Name</TD>
             <TD>$R1[0]</TD></TR>
         <TR><TD>Project Desc</TD>
             <TD>";

             ibase_blob_echo($R1[1]);

          echo "</TD></TR>";
      }

 echo "</TABLE>

Here, the blob is echoed out between the table data tags, and appears inside a cell in the table.

back to top of page

Displaying binary blobs

There are two ways of 'displaying' binary data. 'Displaying' may be the wrong term to use, since often the data is not visual, but could be audio, which you want to play in the client browser.

The first way is to use the PHP function header which is an HTTP function. This tells the browser what the format is of the streamed data. Headers need to be sent before any other output, and so it is not clear how to embed a streamed image into a formatted page.

The second way is a bit more clumsy, and requires temporary files, but gives more control. This method is demonstrated here, by showing how to retrieve an image and display it.

We use a standard query to select the data, but when fetching the data, we use the ibase_fetch_object rather than the ibase_fetch_row function. This allows us to retrieve the blob with the ibase_blob_open and ibase_blob_get functions.

The retrieved blob is then written to a file, and then displayed in the browser with a standard <IMG> tag.

The ibase_blob_get function fetches the blob in chunks, so must be used in a loop to ensure that the whole blob is retrieved.

The PHP function tempnam is used to create a temporary file. This file name needs to be modified slightly since the tempnam function can create a file anywhere on any file system, while the <IMG> tag will only serve images below the document root of the web server. In other words, absolute path names do not work the same way for tempnam and <IMG>.

Here is some code to do this. 

    $S =  "select emp_no,hire_date,salary,pic,full_name";
    $S .= " from employee m join user_permissions p ";
    $S .= "  on p.dept_no = m.dept_no ";
    $S .= "  where p.user_name = ? ";
    $S .= "  and m.emp_no = ?"; 

    $Q = ibase_query($S,$USER_NAME,$EMPNO);
    while ($R = ibase_fetch_object($Q))
    {
      $TEMPFILE = tempnam("temp","TMP");
      $TEMPFILE .= ".jpg";

      $BLOBID = ibase_blob_open($R->PIC);
      $FILEID = fopen($TEMPFILE,"w");

 //Loop thru the blob, fetching 10K chunks, and write it to 
   the temp file

      while ($PIC = ibase_blob_get($BLOBID,10240))
      {
         fputs($FILEID,$PIC);    
      }
      fclose($FILEID);
      ibase_blob_close($BLOBID);
      $TEMPFILE = "/php3/" . $TEMPFILE;
      echo "<BR><BR><CENTER><IMG SRC=$TEMPFILE ></CENTER>";

    }

    ibase_free_result($Q);
    ibase_commit();

back to top of page

Updating tables in the database

Updating a table in a database, is as simple as executing an INSERT or DML-DataManipulationLanguageUPDATE statement.

In general when writing a web page which requires user input then a FORM is used with TEXT and TEXTAREA input areas. JavaScript can also be used to provide simple validation on the client side, so that invalid data is not sent to the database. However, all data must be re-validated on the server side, either inside the PHP script, or within database triggers, preferably both.

The sample application allows the user to edit an employee's salary. So, all data is displayed in an ordinary table format, and the salary is displayed inside a TEXT input box. When the user hits the Change Salary button, the data is posted to a PHP3 script which updates the database.

Here is the script which updates the data. All variables such as $EBSALARY were posted to this page. The only slightly tricky thing is to trap any errors and display them, otherwise it couldn't be simpler: 

 <?php
 include("header.php3");

 if ($VALIDLOGIN == 1)
 {

 $Q = ibase_query("update employee set salary = ? where 
 emp_no = ?",$EBSALARY, $EBEMPNO);
 if (ibase_errmsg())
 {
    echo "<FONT color ="red"><h2>Employee $EBEMPNO not 
    updated due to the following error:</h2></font><br>";
    echo "Error: " . ibase_errmsg();
 }
 else
 {
    echo "<CENTER><H2>Employee $EBEMPNO salary changed
    to $EBSALARY</H2></CENTER>";
 }

 ibase_commit();

 include ("trailer.php3");
 }
 ?>

This paper was written by Vince Duggan and is copyright Vince Duggan and IBPhoenix Inc.

See also:
Setting up PHP and Firebird on Linux
Beginners' guide to Firebird and PHP
IBExpert Benchmark

back to top of page
<< Beginners' guide to Firebird and PHP | Database technology articles | InterBase and Java: making the connection >>