Blogs

Database-Driven Websites: Using PHP with Informix

Why PHP and Informix?

You may or may not be familiar with PHP, but either way, you use websites that run on it every day. Facebook, Wikipedia, Google, and many other major organizations all rely on PHP—but why? Because it’s a very fast, flexible, open source language that is closely tied into the web server. It runs on most operating systems and in almost any kind of environment. And now it plays nicely with Informix.   Editor's note: Tom Beebe did an excellent webcast on February 12, 2013 about using PHP with IBM® Informix® database. Tom wrote this article as an introduction to his webcast that is also available on demand at the Advance DataTools website.
 

 
For those of you who may have toyed with PHP years ago, there used to be a custom Informix driver available. It was written for Informix Dynamic Server (IDS) version 7.x, but it was unstable and unsupported. However, the language and the drivers have come a long way since then. PHP version 5 introduced PDOs (PHP Data Objects). The PDO system abstracts database drivers through a centralized function set, allowing for standardized statements—which gets away from the old model of each database being handled independently, and often inconsistently.

Currently, you can use two drivers to connect to Informix databases, both of which are supported by IBM. The first one is PDO_IBM, which uses the Distributed Relational Database Architecture (DRDA) connection protocol, and so works only with IDS version 11 and above. It also does not support all of the Informix data types, but it will work with most IBM databases. The other type, which is the focus of this article, is PDO_Informix, which supports any version of IDS. Ongoing compatibility is improving as this driver becomes a fundamental part of the operational acceptance testing (OAT) system. (For details on installing the driver on Linux, click the link to the webcast.)

A common question is, “How hard is it to start using PDO_Informix?” The answer: not hard at all. In fact, it’s very easy. Part of the appeal of PHP is that you can rapidly develop stable and functional code. Getting started is a breeze. (All of the code examples in this article are available for download here.)

Your first connection file looks like this:

<?php
$hostname = 'localhost';
$database = 'stores_demo';
$login = 'login_name';
$password = 'password';
$informixserver = 'testsystem_tcp';
#Only needed if INFORMIXDIR is not already set
putenv("INFORMIXDIR=/opt/informix");
$dbh = new PDO("informix:host=$hostname;service=1516;database=$database;server=$informixserver; protocol=onsoctcp;", $login, $password);
#Set the database handle to return all column names as lowercase
$dbh->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
?>

So how does this connection file work?

$dbh is set to a new PDO object, which can be any PDO database driver installed on the system.

You pass three arguments to it. The first one is the long string with all of the connection information. If you are comfortable with Open Database Connectivity (ODBC), these options will look familiar. Each option is separated by a semicolon, and all of them must be on the same line. The basic format is as follows:

informix:service=<service port/name>;database=<databasename>;server=<informix server>;
protocol=<protocol name>.

You can, of course, add other ODBC-based parameters if you want to manually tune the connection.

The last two arguments to pass to the new PDO object are the login and the password. Once these parameters are established, you now have $dbh available as a database handle for the way you will need it in your application.

The next step shows that connection:

<?php
include “db.php”; # Executes db.php so $dbh will now be set.
#Create a string with the statement we want.
$sqlline = “select customer_num, fname, lname, city from customer where customer_num = 103”;
#Prepare the statement, set it to the $sth variable.
$sth = $dbh->prepare($sqlline);
#Execute the statement
$sth->execute();
#Simple fetch of the next row on the cursor as an object.
$row = $sth->fetch(PDO::FETCH_OBJ);
print “Customer: $row->customer_num $row->fname $row->lname in $row->city\n”;
#close statement handle
$sth->closeCursor();
?>

Next, do a data change as follows:

<?php
include “db.php”;
#Create the update string, note the statement parameters '?'
$sqlline = “update customer set city = ? where customer_num = ?”;
#Create a new statement handle
$sth = $dbh->prepare($sqlline);
$customer_num = 103;
$city = “Annandale”;
#The execution is looking for an array of values, passing a single value will return an error.
#Note, these must be in the same order as you had the parameters.
$var_to_pass = array($city, $customer_num);
#Execute the statement
$sth->execute($var_to_pass);
print “Record Updated, return code: “. $sth->errorCode() .”\n”;
?>

Now when you rerun select.php, you will see the updated value.

These simple scripts are the very basic building blocks with which you can write almost anything you need to run against an Informix database. PHP is an extremely flexible language, so you can easily set it up to do whatever you need when handling data structures.

PHP has come a long way in the last few years. If you have never used it or have not tried it in a while, perhaps it’s time to take an initial (or second) look at the language. PHP can become functional within a few hours, with far more complex systems made possible in a fraction of the time other languages require. There is a reason much of the web runs on PHP—and now it can start running on Informix as well.

What do you think? Please share any thoughts or questions in the comments.

[followbutton username='lesterknutsen' count='false' lang='en' theme='light']
 
[followbutton username='IBMdatamag' count='false' lang='en' theme='light']