Tutorial: Database - PHP - ODBC Connections
 

Dr. Thomas E. Hicks
Computer Science Department
Trinity University
 

ODBC is an acronym for Open Database Connectivity.

Once you have a working web server, one may connect to the server

• using ODBC
• using relative RealPath connections

There are times one must move a database to another folder or another drive. With an ODBC connection, one simply changes the interface connection and all is done. The problem is that the ODBC connection requires administrative access. Since normal users may not create or change such connections, this is often not an available option to those maintaining web sites.

When a database is moved with a RealPath connection, each and every page interfacing with the database must be individually changed/altered to reflect the new location. This is much more of a hassle than ODBC connections, but it makes database interface possible to those leasing web site space on a commercial Internet Service Provider (ISP) This often makes it possible
for students to do database interface on a university web server; the administration is not going to provide or manage ODBC for the students. This often makes it possible for small companies to do database interface on an ISP web server; the ISP is not going to provide or manage ODBC for the customers. This often makes it possible for teachers to do database interface on a school web server; the administration is not going to provide or manage ODBC for the teachers.

Both connection types have advantages and disadvantages. It is the purpose of this paper to describe the ODBC option. You may find out information about RealPath connections with the following tutorial:

Database - PHP - RealPath Connections

 IIS 5  Install on Windows XP Pro

IIS 5 Installation on Windows 2000 Pro/Server

Installing PWS on Windows 98

Install Apache With IIS

It might even help to read a tutorial that describes the need for Database Web Applications.

Database Need For Web Applications

The Security Database is opened and the Tables tab is selected; in order to keep our example simplistic, the Security Database has only a single table, called Users. The contents of our very simple Users table can be seen below.

Each record in the Users table contains fields Name, No, Password, and IDNo; these will be needed later.

The datatypes of each field may be seen below.

The Security Database above was is ultra trivial application which had only one database table. Relational databases often have many tables; this is true of Access databases as well. This database may be downloaded.

Security.zip

The customers table stores the video store customer entity. The Movie table stores the video store movie entity. The Video Database is a relational database;  the Transact table stores the relationship of movies checked out by the customers.

The Customer.dbf table contains 24 records. Each record of the customer table contains fields Name, No, and Phone. The table designer view shows the datatypes associated with the customer fields.

The Movie.dbf table contains 38 records. Each record of the customer table contains fields Name, No, and Category. The table designer view shows the datatypes associated with the movie fields.

The Transact.dbf table contains 101 records. Each record of the transact table contains fields Name, No, and Phone. The table designer view shows the datatypes associated with the customer fields.

This database may be downloaded.

Video.zip

In order to configure ODBC, one must have administrative privileges. All users using Windows 98 have administrative privileges. Windows NT, Windows 2000, and Windows XP can be configured for individual log in accounts; in order to set up ODBC on these configurations, one must have administrative privileges.

File adodb.inc.php includes many of the database PHP4 extensions to php. File adodb-time.inc.php is used by adodb.inc.php. These should be placed in the web site. I have chosen  to place them in directory ADODB for purposes of this tutorial..

There is PHP support for many databases; among them are Oracle, informix, mysql, ibase, cvc, postgres, sybase, foxpro, etc. Each of these shall require its own driver. Although you shall not neeed all of the drivers, I have placed a collection that I downloaded from the Internet in directory Drivers. See Below!  These should be placed in the web site. I have chosen  to place them in directory ADODB for purposes of this tutorial..

You might want to download your own files from the Internet, but for sake of simplicity, I have included those used in the tutorial in the zipped file below; place them in a folder, called ADODB, at the root of your IIS directory.  (See Below!)

ADODB.zip

The ODBC applet can be found in the control panel. Using the mouse, double-double click on ODBC. (See Below!)

Somewhere in the Start Menu will be some Administrative Tools; you may have to configure your Start Menu to make these visible. Select Data Sources (ODBC) to launch the ODBC Applet. (See Below)

All connections must be placed in the System DNS tab in order to be accessible by the Internet. Using the mouse, select the System DSN tab. (See Below!)

Using the mouse, select the Add button. (See Below!)

Using the mouse, select the MS Access Database Driver. Using the mouse, push/select the Finish button to configure the new ODBC client. (See Below!) 

Enter Security for the Data Source Name. Enter Access Database Users Security for the Description. Using the mouse, select the Select button. (See Below!) The Data Source Name may not match any of the other Data Source Names on the web server; it must be unique. Make note of the Data Source Name [Security]; it too will be needed later. The Description may be anything you choose.

Select the database to be associated with this Data Source Name. Using the mouse, select Drive C. Using the mouse select Security.mdb as the Database Name. Using the mouse, select/push the OK button. (See Below!)

 It is now time to save the new User Data Source. Using the mouse, select/push the OK button. (See Below!)

Security should now be included as a User Data Source in the ODBC Data Source Administrator. The configuration is complete. Using the mouse, select/push the OK button. (See Below!)

All connections must be placed in the System DNS tab in order to be accessible by the Internet. Using the mouse, select the System DSN tab. (See Below!)

Using the mouse, select the Add button. (See Below!)

Using the mouse, select the MS Access Database Driver. Using the mouse, push/select the Finish button to configure the new ODBC client. (See Below!) 

Enter Security for the Data Source Name. Enter Access Database Users Security for the Description. Using the mouse, select the Select button. (See Below!) The Data Source Name may not match any of the other Data Source Names on the web server; it must be unique. Make note of the Data Source Name [Security]; it too will be needed later. The Description may be anything you choose.

Select the database to be associated with this Data Source Name. Using the mouse, select Drive C. Using the mouse select Security.mdb as the Database Name. Using the mouse, select/push the OK button. (See Below!)

 It is now time to save the new User Data Source. Using the mouse, select/push the OK button. (See Below!)

Security should now be included as a User Data Source in the ODBC Data Source Administrator. The configuration is complete. Using the mouse, select/push the OK button. (See Below!)

There  are at least 140 database with ODBC connectivity. Just as there were small differences in the FoxPro and Access configurations, there will be slightly different interfaces for many of the other database configuration applets.

#--------------------------------------------------------------------------
#                              Include The Database Utilities
#--------------------------------------------------------------------------
   Include('../adodb/adodb.inc.php'); 

#--------------------------------------------------------------------------
#                   Access Database ODBC Connect To The Server
#--------------------------------------------------------------------------

	$Conn = &ADONewConnection('vfp');
 	$Conn->PConnect('Video');

The PHP code to connect to the Access Security database is as follows:

#--------------------------------------------------------------------------
#                              Include The Database Utilities
#--------------------------------------------------------------------------
   Include('../adodb/adodb.inc.php'); 

#--------------------------------------------------------------------------
#                        Access Database ODBC Connect To The Server
#--------------------------------------------------------------------------

	$Conn = &ADONewConnection('access');
 	$Conn->PConnect('Security');

Note the simplicity.

Although it is not the purpose of this tutorial to teach PHP or HTML, a example is necessary to verify that the connection works.

<?PHP 
#=========================================================================
#=========================================================================
#====                       ConnectODBCSecurity.php                   ====
#=========================================================================
#====                                                                 ====
#==== Purpose    : Demonstrate ODBC Connection To The Security        ====
#====              Database Using An ODBC Connection.                 ====
#====                                                                 ====
#==== Written By : Dr. Thomas E. Hicks              Date: 08/1/2003   ====
#=========================================================================
#=========================================================================

#--------------------------------------------------------------------------
#                              Include The Database Utilities
#--------------------------------------------------------------------------
   Include('../adodb/adodb.inc.php'); 

#--------------------------------------------------------------------------
#                       Access Database ODBC Connect To The Server
#--------------------------------------------------------------------------

	$Conn = &ADONewConnection('access');
 	$Conn->PConnect('Security');

#--------------------------------------------------------------------------
#                     Read All Of The User Names Into The RecordSet
#--------------------------------------------------------------------------

	$UserSQL = ' SELECT Name ' .
		   ' FROM Users';

	$RecordSet = &$Conn->Execute($UserSQL);

#--------------------------------------------------------------------------
#                              Loop Through The Records
#--------------------------------------------------------------------------

	While (!$RecordSet->EOF) 
	{
?>

<HTML><HEAD>
<TITLE>Dr. Thomas E. Hicks - ConnectODBCSecurity.php </TITLE>
</HEAD>

<BODY>

<?PHP

		Print $RecordSet->fields[0] . '<BR>';
		$RecordSet->MoveNext();
	}

#--------------------------------------------------------------------------
#                        Close & Terminate The Connections
#--------------------------------------------------------------------------

	$RecordSet->Close(); 
	$Conn->Close(); 
	$RecordSet = null; 
	$Conn = null; 
?>

</BODY></HTML>

You can see the results below:

	Dr. Thomas E. Hicks Computer Science Department     Trinity University "Dr. Web"