Use PHP on System i, Part 1: Introduction and examples

An overview, sample applications, and one group's experiences

Learn how to install PHP and DB2® on System i™, and understand how to port a MySQL application to DB2 on i5/OS®. Understand the history and architecture types of PHP, including the new architecture that uses i5/OS with an Apache HTTP server and the IBM DB2 database to bring PHP applications to System i. Read examples of PHP Web applications. Learn how PHP applications are especially beneficial to small and medium businesses, because open source PHP applications are often inexpensive and easily accessible. [The product option for the CCA Cryptographic Service Provider has been changed from option 13 to option 35. The title has been changed to indicate the first article in a series. -Ed.]

Share:

Margaret Myslinska, Software Engineer Intern, IBM

Photo of Margaret MyslinskaMargaret Myslinska was an IBM Software Engineer Intern during the summer of 2006. She is currently finishing her degree at University of Chicago, Urbana Champaign, Illinois.



Andrew Nere, Software Engineer Intern, IBM

Photo of Andrew NereAndrew Nere was an IBM Software Engineer Intern during the summer of 2006. He is currently finishing his degree at St. Cloud State University, Minnesota.



David Swenson, Software Engineer Intern, IBM

Photo of David SwensonDavid Swenson was an IBM Software Engineer Intern during the summer of 2006. He is currently finishing his degree at Minnesota State University, Mankato, Minnesota.



Gloria Velazquez, Software Engineer Intern, IBM

Photo of Gloria VelazquezGloria Velazquez was an IBM Software Engineer Intern during the summer of 2006. She is currently finishing her degree at University of Texas at El Paso, Texas.



Kathryn Lamont (kathrynl@us.ibm.com), Software Engineer, IBM

Photo of Kathryn LamontKathryn Lamont is a software engineer with IBM Software Group. She works on the installation team for the middleware product IBM WebSphere Process Server for Multiplatforms. Previously, Kathryn worked on IBM WebSphere Business Integration Server Express for iSeries. She was a technical mentor for the PHP on System i 2006 summer intern project. She joined the IBM Rochester team in 2004. Kathryn graduated from Iowa State University with a computer engineering degree. You can reach Kathryn at kathrynl@us.ibm.com.



David Peraza (dperaza@us.ibm.com), Software Engineer, IBM

Photo of David PerazaDavid Peraza is a software engineer with IBM Systems and Technology Group. He works with WebSphere MQ integration with the System i platform. He was a technical mentor for the PHP on System i 2006 summer intern project. You can reach David at dperaza@us.ibm.com.



08 February 2007 (First published 14 November 2006)

Also available in Russian

Introduction: Sharing experiences

On April 3, 2006, IBM® and Zend Technologies Ltd.™ began to bring the hypertext preprocessor (PHP) engine and PHP development tools to the System i platform. PHP is popular in the open source community because of its capability to create dynamic Web applications. The PHP open source community has developed literally thousands of PHP applications for many different uses. System i customers can be excited about the number of applications and capabilities that this emerging technology brings to the business world.

Three sections address three main themes:

  • Overview -- An overview of PHP on System i, including the history of PHP, why it is important to System i, and what types of architectures exist for PHP.
  • PHP Web applications -- PHP applications, including examples of three specific applications.
  • PHP on System i installation and database considerations -- Our overall experiences with PHP on System i. This section includes information about the installation of PHP, how to use the DB2 database with PHP, and how to port existing PHP applications to the DB2 database, including many common differences you might encounter in porting a MySQL® database application to DB2 on i5/OS.

Overview of PHP on System i

PHP began as Personal Home Page: a simple Perl hack Rasmus Lerdorf wrote in 1994. Today, PHP means hypertext preprocessor. In 1997, Zeeve Suraski and Andi Gutmans produced PHP 3. Shortly afterwards, they created Zend Technologies Ltd., the company that produces the Zend Engine for PHP.

One of the key features of PHP is that the majority of applications written in PHP are open source. Open source means that everyone has free access to the code and can modify and redistribute it, either for free or for profit. For this reason, many PHP applications are available, and many are free. Some examples of such applications include wikis, bulletin boards, and shopping carts, which you can use to help manage and run small and medium businesses.

Popularity of PHP

PHP is one of the most widely used Web programming languages today with over 4.5 million PHP developers and over 22 million Internet domains using PHP. PHP is a powerful server-side scripting language you can use to create dynamic Web sites and applications.

Architectural patterns of PHP

For implementing PHP on a server, there are two popular architectural patterns: LAMP and WIMP. LAMP (Linux®-Apache-MySQL-PHP) uses a Linux-based operating system. LAMP uses an Apache HTTP server in conjunction with the Zend Core PHP engine, and connects to PHP applications using the MySQL. WIMP (Windows®-IIS-MySQL-PHP) uses a Windows-based operating system with Microsoft Internet Information Services (IIS) instead of Apache.

IBM and Zend software engineers recently developed a third architectural pattern to bring PHP to the IBM System i platform. This architecture uses i5/OS with an Apache HTTP server and the IBM DB2 database to connect to PHP applications.

Benefits of using PHP on System i

The many benefits of using PHP on the System i platform include:

  • The System i platform comes integrated with the components needed to support robust business computing, such as the operating system, database, security, and networking. These components, combined with the Zend PHP framework, provide the needed resources needed to develop Web-based applications able to support a large user base.
  • PHP is a robust, scalable, stable, secure, performance-driven language for developing business-critical Web applications. Small-to-medium-sized businesses can benefit from this adaptive language running on System i machines.
  • The language supports Service-Oriented Architecture (SOA) by providing support for Web services.
  • PHP integrates well with traditional systems, databases, and other applications.
  • PHP has thousands of customizable open source applications you can use to quickly get business applications up and running.
  • Even though database connectivity might be its most popular use, you can also configure PHP to run as a standalone Common Gateway Interface (CGI) script. The CGI version runs with all servers and on most operating systems. The only disadvantage to using CGIs is a reduction in performance.
  • You can integrate PHP scripts with various external libraries to allow a large variety of operations, thereby taking advantage of the System i platform.

Compatibility with traditional languages

For many years, the System i platform has been running RPG, COBOL, and other traditional languages. Because PHP is compatible with these traditional languages, System i can directly call highly tuned RPG and COBOL routines. Using an API that directly interfaces with these languages instead of using alternatives, such as SOA, allows for easier development of Web applications. However, the performance of the application depends on its intended use. Small- and medium-sized business applications might be better suited for PHP implementation, whereas larger, more-scalable applications might be better suited for an SOA Web services implementation. PHP compatibility with programming languages IBM uses can save customers redevelopment costs and time needed to replace existing software.


PHP Web applications

With PHP on the System i platform, customers are able to take advantage of the thousands of open source Web applications available. PHP applications have become popular among both home and business customers. Many of these applications enable nontechnical people to quickly develop and customize applications without the need for particular programming language skills. Many of these applications are known as content management system (CMS) applications that allow users to easily create and manage Web sites without knowing of HTML. See Resources for where to download some PHP applications.

PHP applications can be divided into two categories: flat-file and database types. You can install and configure both types easily on System i. You can install flat-file PHP applications on a System i platform with the PHP engine. Database-driven applications usually work with the open source MySQL database. You can download and install MySQL on System i. See Resources to link to a posting and example on the Zend Core Web site discussion forum. After you install the MySQL database on the System i platform, you can install and run database-driven applications.

The most popular open source applications available include wikis, bulletin boards, and shopping carts. These are not the only open source applications available, but they show examples of the wide variety of what PHP can accomplish.

The wiki

A wiki is a type of Web site that allows users to add, remove, or edit its content quickly and easily. Often users edit the content without registration or access to the files located on the hosting Web server. The PmWiki Web site (see Resources) is an open source wiki developed using PHP. PmWiki uses a flat-file system instead of a database to maintain the contents of the site. After you install the Zend Core framework on System i, put the PmWiki files into Apache HTTP Web server's public Web folder. From there, use the PmWiki interface to customize the wiki. The syntax to edit pages is simply combinations of punctuation marks to create the desired text effect. In addition to basic editing, you can include libraries to handle advanced features, such as message blogs, security, and multimedia.

The bulletin board

Forums and bulletin boards allow people's questions to be shared with the world, and they provide the possibility of their getting an answer. The phpBB (PHP Bulletin Board) Web site (see Resources) helps you easily develop a bulletin board application. phpBB enables you to create forums, organize forums into categories, submit topics, submit responses, and send group and personal emails. phpBB implements a database to contain the content of the site and to keep track of users.

The shopping cart

Open source shopping cart applications enable businesses to create and customize their own e-commerce shopping sites. Buyers can create accounts and purchase items easily online. The Zen Cart Web site (see Resources) is an example of where to find such an application. Zen Cart features a number of customizable templates for you to develop a business application. Other modifications and administration features are easily accessible using a Web-based GUI. Because a shopping site requires security for its customers, Zen Cart integrates the use of SSL and digital certificates. A local certificate is created specifically for the Zen Cart store on the server machine. Customers of the Zen Cart store receive and save the certificate when they log into the store. Both the browser and the server use the certificate to encrypt and decrypt the transmitted text, allowing users to use Zen Cart securely. Zen Cart interfaces directly with a database to store its data.


PHP on System i: Installation and database considerations

System i users have several issues to consider when choosing an open source application. The first and probably most direct issue is selecting from the diversity of existing applications. Zend Core is the only PHP engine currently available for i5/OS on the System i platform. Before installing Zend Core, you need to install 13 licensed programs included with the System i software package (see Table 1). The introduction of the Zend Core framework for i5/O has greatly simplified installation of PHP on System i computers. Zend's setup tool provides the System i platform with the PHP engine, Apache HTTP Web server, DB2 native support, and Web GUI administration.

The Zend Core product is a free, licensed product. The installation involves creating a SAVF file on the System i and copying the Zend Core to the file in binary. The Zend Core includes detailed instructions with its download from the company Web site (see Resources).

By installing Zend Core, the associated PHP Apache Web server is configured. You can easily perform additional customization of the framework using the GUI administration page through a Web browser. The Zend PHP engine is currently supported only on i5/OS Version 5 Release 4.

The i5/OS prerequisites for installing the Zend PHP engine are:

  • i5/OS Version 5 Release 4 Modification 0 or higher
  • The latest version of PTFs
  • The /usr/local directory, because the product will be installed under this directory by adding a folder
  • The following Zend licensed programs (check by using the command: go licpgm, option 10):
Table 1. Zend Core prerequisites on i5/OS
DescriptionProduct optionLicensed program
IBM Portable Utilities for i5/OS*base5733sc1
OpenSSH, OpenSST, Zlib15733sc1
System Openness Includes135722ss1
Qshell305722ss1
Portable App Solutions Environment335722ss1
Digital Certificate Manager345722ss1
CCA Cryptographic Service Provider355722ss1

Syntax of PHP

The syntax of PHP is easy to use and very similar to that of C and Perl. Math and logic operators are handled in PHP the same way they are in the C language. You can write new functions in PHP just as you would write them in the Java™ language. The latest versions of PHP are object-oriented, similar to the Java language. Some of the differences include:

  • All PHP code begins and ends with a tag, like Javascript. This enables PHP to be embedded directly into HTML code in Web sites and applications.
  • Variables in PHP begin with a dollar sign ($).
  • Variables are loosely typed, and the same variable that once was used for integers can later by used as a character. Depending on your perspective, this can either expand the versatility of a variable or make following a single variable more difficult.
Listing 1. Example of using the same variable for different types in PHP
<?php

$var = 5;

echo '$var';
//$var will print as '5'

$var = 'string';

echo '$var'
//Now, $var will print as 'string' !

?>

The best way to get started with PHP is to visit the PHP resource site (see Resources). This site provides all the syntax and functions of PHP, as well as detailed examples. The open source community updates it continuously, providing new examples and instructions.

Connection to the DB2 database

With PHP, when connecting to the DB2 database, you can use one of two types of drivers to create a connection. The first and older of the two is the ODBC connection driver. Use the following code sample to connect to the DB2 database through ODBC:

Listing 2. ODBC connection
<?php
	
$db_host	= "server.mynetwork";
$db_user	= "dbuser";
$db_pass	= "dbpass";
	
odbc_connect($db_host, $db_user, $db_pass, "SQL_CUR_USE_ODBC");
	
@odbc_setoption($this->db_connectid, 1, SQL_ATTR_COMMIT, SQL_TXN_NO_COMMIT)
  or die('Failed setoption: ' . odbc_error() . ":" . odbc_errormsg());
			
@odbc_setoption($this->db_connectid, 1, SQL_ATTR_DBC_DEFAULT_LIB, $this->dbname)
  or die('Failed select: ' . odbc_error() . ":" . odbc_errormsg());

?>

The second method of connecting to the DB2 database is using the IBM_DB2 connection, shown in the code sample below. This newer method can help solve some of the connectivity issues encountered when using ODBC, but either method works to connect to DB2 in PHP.

Listing 3. IBM_DB2 connection
<?php

$database	= 'SAMPLE';
$user		= 'db2inst1';
$password	= 'ibmdb2';

db2_connect($database, $user, $password);

?>

DB2 use of schemas

The default configuration for DB2 for i5/OS is as a single systemwide database. Schemas are the logical containers for related database objects on the System i platform. A schema created in DB2 is analogous to a database in MySQL. In other words, a schema is like a database in MySQL, where tables can be created and populated. You can create DB2 schemas with the CREATE SCHEMA statement.

Referencing data from DB2 database

When referencing data from the DB2 database, some things to specifically consider include referencing table names, column names, and table data.

Referencing table names in DB2

DB2 table references within an SQL statement can be either unqualified or explicitly qualified with a schema name.

Listing 4. Example of referencing table names
INSERT INTO table VALUES(val1, val2, … valn);
//MySQL Insert Statement

INSERT INTO table VALUES(val1, val2, … valn);
INSERT INTO schema.table VALUES(val1, val2, … valn);
//DB2 Insert Statements

A PHP connection to DB2 for i5/OS defaults to using the SQL naming convention. With the SQL naming convention, the only schema that is searched for unqualified objects is the current (or default) schema. The current schema value usually defaults to the schema that matches the name of the current SQL authorization name (or user profile name). If the authorization name is JOHNDOE and an unqualified object is referenced, DB2 for i5/OS expects to find that object in a schema named JOHNDOE. You can use the i5_lib connection option to specify the default schema to use for unqualified references on a connection.

Referencing column names in DB2

The field names of a table in DB2 are capitalized by default, even if they were lowercased when defined. Therefore, when referring to them within a PHP script, capitalize them.

Referencing and accessing data from a table in DB2

When retrieving data from a DB2 table, consider whether the data is from the fields (column headers) or the actual values (all data in the columns and rows). The field names can be retrieved normally as strings. However, the values of the columns are returned with a non-visible extra character, such as the following:

Table 2. Non-visible extra characters
Character nameCharacterASCII code
Ordinary space" "ASCII 32 (0x20)
Tab"\t"ASCII 9 (0x09)
New line (line feed)"\n"ASCII 10 (0x0A)
Carriage return"\r"ASCII 13 (0x0D)
Null-byte"\0"ASCII 0 (0x00)
Vertical tab"\x0B"ASCII 11 (0x0B)

Therefore, if you need to reference this data by its value in the code, it is advisable to use a function, such as trim(), that removes extra characters.

Migrating PHP applications from MySQL to DB2

Even though SQL is a universal language, use of the language differs slightly based on the database type. The majority of PHP applications connect to the MySQL database. MySQL is an open source database that you can download for free and use with many database-driven PHP applications. You can download and install MySQL on System i.

Some System i customers might want to use the DB2 database instead of the open source MySQL. This section describes considerations in moving a PHP application from MySQL to DB2. When you migrate SQL statements from MySQL to DB2, you find differences in syntax and function. Table 3 lists some of the differences; explanations of the differences follow Table 3.

Table 3. Differences between MySQL and DB2 statements
Desired actionMySQL statementDB2 statement
To return a selected number of rows from a tableSQL -> LIMITSQL -> FETCH FIRST n ROWS
To determine last generated ID of a table after an insert statementPHP-> mysql_insert_idSQL -> SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1;
To show tablesSQL -> SHOW TABLES FROM table_nameSQL -> SELECT TABLE_NAME FROM schema_name.SYSTABLES WHERE TABLE_NAME=table_name
To show indexesSQL -> SHOW INDEX FROM table_nameSQL -> SELECT INDEX_NAME FROM schema_name.SYSINDEXES WHERE TABLE_NAME=table_name
To determine number of rows affected by an SQL statementPHP -> mysql_num_rows() for SELECT statements
, mysql_affected_rows() for INSERT, UPDATE, DELETE
PHP -> db2_num_rows() (does not work for SELECT statements when using non-scrollable cursor)
To insert a key into a table where a primary key already existsSQL-> IGNORE drops the insert statement
To set a column to automatically generate and increment its valuesauto_incrementGENERATED BY DEFAULT AS IDENTITY
To escape a characterAdd a slash ('\') in front of the character, such as "Smith\'s"Add an apostrophe ( ' ) in front of the character, such as "Smith''s"
To return an array, indexed by both column name and position, representing a row in a result setmysql_fetch_array()db2_fetch_both()

Connection to the database

When Web applications connect to a MySQL database, they often use a mysql_connect function in PHP. To connect to DB2, there are two different options: the odbc_connect and the db2_connect (see Connection to the DB2 database). The ODBC driver is older than the IBM_DB2 driver, but because ODBC is not necessarily used specifically for the DB2 database, open source applications more often use OBDC.

SQL differences between DB2 and MySQL

There are differences between the SQL of DB2 and MySQL. Some of these differences include data types, data definition languages, and showing tables.

Data type differences between DB2 and MySQL

Database-driven Web applications often automatically generate scripts and tables for initial use. These scripts, although all written in SQL, have certain syntax differences, depending on the database. For example, in porting an application from MySQL to DB2, be sure to change some of the MySQL data type names to equivalent DB2 names. Table 4 shows a few examples of such translations.

Table 4. Differences in data types between MySQL and DB2
MySQLDB2
TINYINTSMALLINT
MEDIUMINTINTEGER
INTINTEGER
TINYTEXTCLOB(255)

Data definition language differences

After changing all data type names into those using DB2 syntax, the next step of the application porting process is to change the data definition language (DDL) syntax into DB2 syntax. The syntax that changes includes:

  • Granting and revoking user privileges
  • Creating and deleting tables, views, and indexes
  • Defining referential integrity rules

The following code samples show the differences.

Listing 5. MySQL example for table creation
create table myTable (
    age int(11) unsigned NOT NULL out_increment,
    first_name varchar(10) unsigned default NULL,
    last_name varchar(20) unsigned default NULL,
    PRIMARY KEY (age)) type-MyIASM;
Listing 6. DB2 example for table creation
Create table myTable (
    age INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR(10) default NULL
    last_name VARCHAR(20) default NULL
    PRIMARY KEY (age));

Showing tables

Another difference between DB2 and MySQL is the use of the SHOW TABLES and SHOW INDEX FROM table_name, which is supported in MySQL, but not in DB2. In DB2, replace these statements with SELECT statements, as shown in Table 5.

Table 5. Showing databases, tables, and indexes using MySQL and DB2
MySQLDB2
Show databasesSELECT TABLE_SCHEM FROM SYSIBM.SQLSCHEMAS
Show tablesSELECT TABLE_NAME FROM SYSTABLES WHERE TABLE_SCHEMA = schema_name
Show index from table_nameSELECT INDEX_NAME FROM SYSINDEXES WHERE TABLE_NAME = table_name AND INDEX_SCHEMA = schema_name

Escape character

Another difference between DB2 and MySQL is how to escape a character. In MySQL, you add a slash ( '\' ) before the character to escape. In DB2, escape a character by placing an apostrophe in front of it. For example, if you want to place the word " Smith's " in an SQL statement, type " Smith\'s ". If you want the same results in DB2, type " Smith''s ".

A helpful tool

Converting these scripts by hand can be a lengthy process. However, there are conversion tools to help convert the SQL syntax and database porting. The IBM DB2 Migration Toolkit (MTK), for example, is free software (see Resources). The DB2 MTK currently supports conversions of MySQL databases to DB2 SQL syntax, but it does not support a DB2 for i5/OS target. An upcoming version of the DB2 MTK supports converting MySQL to DB2 for i5/OS SQL syntax. See Resources for a link to the IBM DB2 Migration Toolkit Web page for the latest version information. The DB2 Migration Toolkit is best suited to convert long SQL scripts. This tool is not designed for directly converting the SQL statements embedded in PHP code. However, you can copy individual SQL statements into the DB2 MTK Translator utility to ease conversion of complex MySQL SQL statements.

PHP differences between using DB2 and MySQL

In converting from MySQL database to DB2 database, differences in APIs can arise. The syntax for connecting to MySQL does not directly translate to that of DB2. Functions that exist for a MySQL connection might not exist for a DB2 connection, and functions that exist for a DB2 connection might not exist for a MySQL connection. Often, functions that have similar names might return entirely unrelated values. Even functions that might have the same name and return the same values can have a very different parameter order. Some examples of functions that perform differently in DB2 and MySQL include the following:

  • The mysql_index_id() function returns the ID generated by the last INSERT statement. In DB2, you use an auto-incrementing identity column and the IDENTITY_VAL_LOCAL function to get the same result.
  • The db2_num_rows() function returns the number of rows affected by a SQL statement. In DB2, this function works for INSERT, DELETE, and UPDATE statements. The MySQL version of this function, mysql_num_rows(), works only for SELECT statements.
  • The db2_fetch_array() function returns an array, indexed by column position, representing a row in a result set. The MySQL equivalent of this function, mysql_fetch_array(), returns an array that is numerically indexed by the column position as well as by column name.

Conclusion: Benefiting small and medium businesses

PHP on System i is a great benefit to small and medium businesses. After understanding the basic background and architecture types of PHP, you can download and install thousands of available applications on System i, either by themselves or with a supported database. There are considerations when porting an application from the MySQL database to DB2, such as database connectivity. However, the cutting edge, low-cost applications that PHP offers combined with the solid System i platform and DB2 database make a strategic choice for any growing business.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Open source
ArticleID=174350
ArticleTitle=Use PHP on System i, Part 1: Introduction and examples
publish-date=02082007