"System Administration: It's a dirty job, but someone said I had to do it"

Seen on a T-shirt
One of the most commonly faced dilemmas of system administrators is making dissimilar operating systems and tools work with each other. It is my hope that this article will make those system adminstrators' jobs a little easier when it comes to making Access and PHP work together.
If you want to use your Microsoft Access database with PHP you have a limited number of methods you can try. The most obvious one is to run PHP on Windows and use the ODBC functions. This is not an acceptable solution to many people because it forces you to use IIS and NT as your web server environment. For those preferring other operating systems, the most popular choice is an ODBC to ODBC bridge as sold by Openlink (http://www.openlinksw.com/) and easysoft (http://www.easysoft.com/). Both of these products come with a limited version that you can download and use for free. I wanted a open source alternative to these products and finally found what I was looking for in ODBCSocketServer (http://odbc.linuxave.net/). This tutorial will take you through configuring your machines and using the socket server.
In order to accomplish this task, you are going to need two machines. One machine will have the operating system of your choice, the web server of your choice and PHP on it. The other machine will have Windows, MS Access, and ODBC on it. The socketserver on the Windows machine will look for connections on a TCP/IP port, PHP will generate XML commands and send them to the socket server. The socket server will then execute the SQL statements in the commands and pass another XML document back to PHP. Finally, PHP will parse the XML document and manipulate the resulting recordset. Here is a simple diagram.
   MS-Access                                 Browser
      |                                         |
   ODBC/ADO                                 www server
      |                                         |
 socket server<------------------------------->PHP
                      XML Data
 Windows Machine                          Linux Machine
What is really interesting is that PHP does not need any database services compiled in only XML and the built in socket services are needed.
Ok, lets get started. I will be referring to the Linux machine as the client and the windows machine as the server for the rest of this document.
Step 1: Gathering up the materials.
You will need the following items.
Step 2: Setting up the server:
Step 3: Setting up the client:
Using phpinfo() make sure that XML support is compiled. If it's not compiled, recompile PHP using the --with-xml configuration options. Although not required for this application, you might also want to enable DOM and WDDX functionality. Here is an example.
./configure \
        --with-apxs=/usr/sbin/apxs \
        --enable-track-vars \
        --with-xml \
        --with-dom \
        --with-wddx
make
make install
If you are using an earlier version you will need to make a change to the php.ini file. Either set the allow_call_time_pass_reference = On or add ~E_COMPILE_WARNING to the error_reporting setting If you don't do this php will complain bitterly about a call by reference which we have to make.
Next, drop the included odbcsocketserverobj.php file into a directory in your include path.
That's it!. We are now ready to run some tests.
Your communication with the socket server will be handled by the odbcsocketserverobj.php. This object has the following "public" interface.

<?php

$HostName;              // Name of the host to connect to
$Port=9628;             // Port to connect to (set to the default)
$ConnectionString;      // Connection string to use I will show an example later.
$CaseFolding=true;      // Determines weather or not all XML tags are mapped to uppercase pretty useful sometimes
$XML="";                // the raw XML as returned from the socket server
$Error="";              // Please don't use use the function instead
$Recordset=null;        // An array containing the recordset just in case you want to deal with it directly
$BOF = true;            // Indicates that the current record position is before the first record in a Recordset object.
$EOF = true;            // Indicates that the current record position is after the last record in a Recordset object.
$RecordCount=0;         // Please don't use use the function instead
$FieldNames;            // Please don't use use the function instead
$FieldCount=0;          // Please don't use use the function instead

execute($SQL)
errorMsg()
getField($field) 

moveNext()
nextRow()           

movePrev()
prevRow()            

moveFirst()
firstRow()               

moveLast()
lastRow()            

moveRow($rowNumber = 0)
getNumOfRows()
getNumOfFields() 
getFieldNames() 

?>
Even though PHP does not have any concepts of private and public variables, I have made an effort to hide the usage of variables in the object. Wrapper functions are provided to access variables. For example, it is better oop practise to use getNumOfField() method rather then the $RecordCount variable. Also, note that the movement records are aliased to each other. This was done to establish compatibility with phpDB recordset objects.
Now that we know what the object does, let's put it to the test.
Here is how you would use this object:

<?php

// Of course we have to include the file.
include ("socketserverobj.php");
    
    
// First we create the class
    
$db = new ODBCSocketServer;
    
// Set the Hostname, port, and connection string
   
$db->HostName = "192.168.0.4";
$db->Port = 9628;
   
// any valid ADO connection string should be fine. I find it easier just to set up the DSN
// and pass in the user info
   
$db->ConnectionString = "DSN=Some_ODBC_DSN;UID=Some_User;PWD=Some_Password;";
   

//now execute the SQL
    
    
$result = $db->execute("Select Field1, field2 from SomeTable");
        
if (!$result){
    print "<p>There was an error : " . $db->errorMsg() . "<p>";
} else {
                
    // Simple iteration when you already know the field names
                
    print "<table>";
    while (!$db->EOF){


    Print "<tr>";
            
    // the  case folding is true by default
    // this means we have to use upper case when accessing field names.
    // If case folding was false then we would have to use  exact case like this
    // $db->Field1 ,  $db->field2
    // or use getField method (see next example)

    print "<td>". $db->FIELD1. "</td>";
    print "<td>". $db->FIELD2 . "</td>";
    Print "</tr>";
    $db->moveNext();
            
}
        
print "</table>";
        
// What if you don't know the field names?
        
        
// go back to the beginning we could have moved backwards too!

$db->moveFirst();

print "<table>";

while (!$db->EOF){
    Print "<tr>";
    foreach ($db->getFieldNames() as $fieldname) {
        Print "<td>" . $db->getField($fieldname) . "</td>";
    } // foreach
    Print "</tr>";

    $db->moveNext();
} // while
            
print "</table>";
        
?>
Of course you are not limited to SELECT queries. Action queries (INSERT and UPDATE) obviously don't return a recordset, but they do return a true or false depending on how the engine handled them. Once again, if there are any errors present you can check the Error variable. You may also want to do some things with the XML variable. For example, you may want to send the raw XML to a client for B2B syndication.
Although this object was written to parse the MS ADO XML format, it could easily be rewritten to accommodate the native XML format of the socket server. In a future version, the socket server will be able to hand out either XML type on demand.
I hope this tutorial was useful to you. I want to thank the fine people at Team FXML for the ODBC Socket server and of course the fine people at PHP for providing such a useful development environment.
The socketserverobj include file is available below.
-- Tim