Technical Blogging of the Fuzzy IT Kind

Zend Framework offers a very robust solution for almost any kind of web site; however, there are times when all you want to do is connect to a database without the need for the rest of the framework.  Bellow I will show a quick example of how to connect to two different databases using the Zend_Db, Zend_Config_Ini, and Zend_Loader  classes.

First you need to define your ini file (make sure to store this file out side of the web root, mine is stored at /var/)

[production]
; PostgreSQL database connection info
db.pgsqlServer.adapter = PDO_PGSQL
db.pgsqlServer.config.host = server_ip_or_url
db.pgsqlServer.config.dbname = database_name
db.pgsqlServer.config.username = "dbuser"
db.pgsqlServer.config.password = "dbpassword"
db.pgsqlServer.config.driver_options.PDO::ATTR_ERRMODE=PDO::ERRMODE_EXCEPTION

; Microsoft SQL Server database connection info
db.mssqlServer.adapter = PDO_MSSQL
db.mssqlServer.config.pdoType = dblib
db.mssqlServer.config.host = server_ip_or_url
db.mssqlServer.config.dbname = database_name
db.mssqlServer.config.username = "mssql_user"
db.mssqlServer.config.password = "mssql_password"
db.mssqlServer.config.driver_options.PDO::ATTR_ERRMODE=PDO::ERRMODE_EXCEPTION

From the above ini file contents, there are a few things to take note of:

1. You need to define the pdoType as dblib when connecting to MSSQL with the  PDO Adapter (the default type mssql is not available in the php5_sybase package)
2. You can set driver_options (attributes) for the PDO connection, some of the attributes you can set are
   1. PDO::ATTR_ERRMODE = PDO::ERRMODE_EXCEPTION             (throw exceptions)
   2. PDO::ATTR_PERSISTANT = true                                                  (make persistent connections)
   3. PDO::ATTR_DEFAULT_FETCH_MODE=PDO::FETCH_ASSOC   (return an associative array when fetching query result by default)

Next, create your php file that will be using the database connections. For this example, my php file is located at /var/www/example_project and the Zend libraries are located at include/libs/Zend

<?php
//set the include path for Zend Framework (the include path is not defined in php.ini)
set_include_path('include/libs' . PATH_SEPARATOR . get_include_path());

//Include the Zend_Loader class
require_once 'Zend/Loader.php';

//load the Zend classes for use
Zend_Loader::loadClass('Zend_Config_Ini');
Zend_Loader::loadClass('Zend_Db');

//load the configuration file
$config = new Zend_Config_Ini('/var/my_example.ini', 'production');

//try to connect to and work with the postgres database
try
{
     //connect to postgresql
     $dbh = Zend_Db::factory(
         $config->db->pgsqlServer->adapter,
         $config->db->pgsqlServer->config->toArray()
     );

     //set the instructor id number to use for the query
     $instructorID = 1234;

     //define the query
     $sql = "SELECT Full_Name From Trained WHERE train_id = :idNumber";

     //prepare the query
     $stmt = $dbh->prepare($sql);

     //bind the instructor id as an integer data type to the query
     $stmt->bindParam(':idNumber', $instructorID, PDO::PARAM_INT);

     //execute the query
     $stmt->execute();

     //if an instructor with the given id exits
     if ($stmt->rowCount() > 0) {

         $row = $stmt->fetch(PDO::FETCH_ASSOC);
         echo "The instructors full name is: ",$row['full_name'];
     }

     //clear the statement
     $stmt = null;

     //close the database connection
     $dbh->closeConnection();

}
catch (PDOException $e)
{
     //echo $e->getMessage();
     echo "There was an error querying the database";
}
catch (Zend_Db_Adapter_Exception $e)
{
     //echo $e->getMessage();
     echo "Unable to connect to the database";
}
catch (Exception $e)
{
     echo $e->getMessage();
}
?>

In the above example, I only connected to and used the Postgres database since the code for using either database is almost exactly the same.  The only thing you would need to change in the above code to use the MSSQL database would be to change the pgsqlServer part in the Zend_Db::factory() parameters to mssqlServer.

In closeing the catch blocks at the bottom the php file do the following

• PDOException – Catches any exception thrown by PDO while working with the database (in this example, this pertains to querying the database)
• Zend_Db_Adapter_Exception – Catches any exception thrown while trying to connect to the database (or while using Zend_Db_Adapter, the $dbh variable)
• Exception – Catches any other exceptions I missed