Have you ever wanted to create applications that support more than one database? With the DBX functions found in PHP, creating support for multiple databases is just a matter of abstraction. Read on to learn more about the powerful features that DBX has to offer developers

.

At least once during the week I tend to review the PHP website, at least the manual section (http://www.php.net/manual/en/) to check out some of the new modules that are available to the PHP user. I then tend to rebuild PHP with all the new added features just to try them out.

What is DBX anyway?
That brings me to today's topic: the DBX abstraction layer. And yes, this is another database abstraction layer similar to PHPLib and PEAR. Not that we don't have enough abstraction as it is, yet I want to show the power of DBX. Then you can chalk up one more module in your PHP arsenal.

In the PHP manual I found what DBX does: "The dbx functions allow you to access all supported databases using a single calling convention. The dbx-functions themselves do not interface directly to the databases, but interface to the modules that are used to support these databases."

For now DBX only supports about six databases:

1. FrontBase (available from PHP 4.1.0).
2. Microsoft SQL Server
3. MySQL
4. ODBC
5. PostgreSQL
6. Sybase-CT (available from PHP 4.2.0).

You can check out the DBX module functions out at http://www.php.net/manual/en/ref.dbx.php or at http://www.guidance.nl/php/dbx/doc/ .



DBX by example
One of the drawbacks with using DBX is that PHP needs to be compiled with DBX support, and, since it relies on the underlying database functions, PHP needs to be compiled with them as well. For example, suppose I use MySQL and PostgreSQL (and I do), I then need to compile them into PHP along with DBX in order for DBX to work properly. If I do not, then I get an error like this: "Unable to load DBX_PGSQL module".

Here is an example of how to configure PHP to have DBX support on Linux/Unix:

    $ ./configure
        --with-mysql=/usr/local/mysql
        --with-pgsql=/usr/local/pgsql
        --enable-dbx
    $ make
    $ make install

Here is an example of how to configure PHP to have DBX support on Windows:

Download the php-version.zip file for PHP.net.
Extract the file to your current PHP directory or to something like c:php.
Move the DLLs in the extension directory to your system32 directory.

    c:windowssystem32
    or 
    c:winntsystem32

Edit your php.ini file and uncomment the following line: extension=php_dbx.dll
Uncomment the line that specifies the database you are going to use. For example, I want to use MySQL and PostgreSQL, so I would uncomment this line: extension=php_pgsql.dll only because MySQL support is already in PHP.

So, now that I have DBX configured into PHP we're ready for some killer examples.

For connecting to the database I will use the following:

<?php


    $username     = "rhunter";
    $password     = "bigdog";
    $host         = "blackcat";
    $dbname    = "projects";
    $dbtype    = "pgsql";    //This can be anyone of the 6 supported dbs 


?>

One thing that I want to point out here, is that I can use PHP defined constants for the database types. Here are the constants:

1. DBX_MYSQL or "mysql"
2. DBX_ODBC or "odbc"
3. DBX_PGSQL or "pgsql"
4. DBX_MSSQL or "mssql"
5. DBX_FBSQL or "fbsql" (available from PHP 4.1.0)
6. DBX_SYBASECT or "sybase_ct" (available from PHP 4.2.0)

So, instead of using "pgsql", I can use DBX_PGSQL.



Connecting
Now connecting to the database is simple:

<?php
    $conn = dbx_connect($dbtype,
                        $host,
                        $dbname,
                        $username,
                        $password );

?>

In the connecting example the function dbx_connect() returns a result. The result can either be an object handler if successful or FALSE if unsuccessful. Notice the true power of DBX is in the ability to change database types. In this example, I could have set the database type ($dbtype) to MySQL or ODBC if I wanted. This allows me to port the application to other database systems without modifying the database connection code.

If we want to use a persistent connection, we can add that parameter to the connection function like so:

<?php
    $conn = dbx_connect($dbtype,
                        $host,
                        $dbname,
                        $username,
                        $password,
                        DBX_PERSISTENT );

?>

Now I have created a persistent connection to the database.


Querying
Connecting was simple enough, let me show you how to do a query and get a result.

<?php
    $sql = "select id, description, user from current_project;";
    $result = dbx_query( $conn, $sql );

?>

If successful, then I get an object back or else I get "0" if it fails.

So now, I have a result and I would like to see what is in the result. I can access the data contained in $result as a multidimensional array.

Here is a simple example:

<?php
    echo $result->data[0][0];  

?>

This will print the first element in the first row.

Let me explain how this multidimensional array works:

The first subscript of the array specifies the row or record, the second subscript of the array specifies the column or field. This allows me to have direct access to all rows and columns of the array. And this is the cool part, I do not have to use any "fetch" function to get data out the result (i.e. mysql_fetch_row() or pg_fetch_row()). This is one of the reasons I use DBX.



Now, suppose that the result had 3 rows and 4 columns in each row. How do you suppose I can loop through it?

Let see:

<?php


    // Go through the rows
    for( $i = 0, $i < $result->rows; $i++ ) {
        // Go through the columns
        for( $j = 0; $j <  $result->cols; $j ) {
                echo "$result->data[$i][$j]n";
        }
    } 

    // or another way using foreach

    foreach( $result->data as $row ) {
        foreach( $row as $column) {
            echo "$columnn";
        }
    }


?>

That was easy. This is why I love working with DBX.

After I get a result, I close the connection to the database using the dbx_close() function. Let's see how that works:

<?php
    $close = dbx_close( $conn );

?>

Remember that the dbx_close() function returns a Boolean value: True on success and False on failure.

I like to make sure that the connection closes properly so I check it based on the $close variable.

<?php


    if( $close ) {
        echo "Connection closedn";
    }
    else {
        echo "Error: connection not closedn";
    }


?>

So let me show you the whole script so that you can see how it all flows:

<?php


    $username    = "rhunter";
    $password    = "bigdog";
    $host        = "blackcat";
    $dbname    = "projects";
    $dbtype    = "pgsql";

    $conn = dbx_connect( $dbtype, $host, $dbname, $username, $password );

    $sql = "select id, description, user from current_project;";

    // Query database and get a result
    $result = dbx_query( $conn, $sql );

    if( is_object( $result ) ) {
        // Go through the rows
        foreach( $result->data as $row ) {
                // Go through the columns
            foreach( $row as $column ) {
                echo "$columnn";
            }
        }
    }
    else  {
        echo "No result returned.n";
    }

    // Close connection to db...
    $close = dbx_close( $conn );

    // Make sure connection closes...
    if( $close ) {
        echo "Connection closedn";    
    }
    else {
        echo "Error: connection not closedn";
    }


?>

Summary
My main goal here is to open the door to the DBX functions by showing some examples that are simple and easy to use. After you have started playing with DBX I hope that you will use the other functions that are in the module that I have not covered in these examples. DBX can provide some great abstraction to your database connecting applications. Try out DBX and see what you think. Until next time...


Ray "BigDog" Hunter