Sr. Web Developer
mediabistro.com
US-NY-New York

Justtechjobs.com Post A Job | Post A Resume

Cross-platform and Portable Development With PHP
Building an E-Commerce Website On Windows and Unix/Linux: Cross-platform and Portable Development With PHP
Mike Banahan
Synopsis
We have recently built and delivered an e-commerce project which runs both on Linux and Windows, talking optionally to either Microsoft SQL Server or MySQL. The software was built with PHP4 and is now deployed on a Windows NT server under IIS and SQL server. It runs equally happily (and somewhat faster) on Linux and MySQL.
We thought there might be some people who would like to know a little about how it was done.
Background
The project objectives themselves were not out of the ordinary. We had to build a site for industrial use which would allow a database of products, prices and discount matrices to be accessed by registered users. The users should be able to place orders, review order status and carry out typical shopping-basket operations of the kind you would see on a many on-line ordering sites. Although the site wouldn't be allowed to connect to the live enterprise resource planning system (security concerns were amongst some of the reasons) it would receive regular stock updates and should try to provide a good indication of current stock levels.
The particular project objectives aren't all that important. The technically interesting aspects are the cross-platform solution that evolved.
Our customer was keen that any solution should be able to run equally well on Linux or Windows NT and that it should be independent of the database product as far as possible. Various commercial constraints meant that it would inevitably be deployed on NT/IIS with the data stored in a SQL Server database. A particular concern of the implementation was that the database would NOT be local to the web server, but instead accessed physically remotely via the corporate internal WAN. The WAN is known to have limited bandwidth and to get busy from time to time.
Flexibility and portability being important matters, we concluded that a solution based on either Perl or PHP would be the best bet and elected to use PHP since it was easier to convince some sceptics that PHP was a mature product in the NT environment. The situation was politically sensitive (spot the understatement) and whilst we would have been happy with either choice, the art of the possible pointed towards PHP. (Our developers still prefer Perl by the way).
php/NT/Linux

Cross-Platform Issues
We already had PHP and MySQL happily running on Linux. The PHP download for NT was no problem and after telling the SQL Server installation that it had to be prepared to speak TCP/IP to clients (an option buried amongst the numerous dialog boxes), it was responding to local requests from PHP too. We persuaded the PHP code to talk across our network to MySQL without the slightest difficulty. The reverse was another matter.
Building a version of PHP that would run on Linux and talk to SQL Server was not proving to be easy. After quite a lot of digging around the PHP website and reading what various people had to say in mailing lists, we'd found that a) SQL Server talks the same protocol as Sybase (no surprise, since SQL Server is/was based on a Sybase core) and that b) we would need some special libraries to make it work. Those libraries are not part of standard PHP distributions for Linux/Unix. We chose to use the freetds library.
It took a lot of cursing and sweating to get PHP working with freetds - we'd have been well advised to have spent our time looking around for the guidance that is actually out there, but sadly hadn't found it first time around. At least one useful starting point is: Michael Peceny's site and of course, after all the struggling, others came to light as well. You do need to be happy compiling applications from source to make it work. If there are any packaged installations of PHP and freetds, then we didn't find them.
Eventually PHP4 running on Linux was talking to SQL Server running on NT.
Being used to the mature and powerful Perl DBI:: interface for accessing databases, PHP's insistence on using a different set of function calls per database seemed terribly retrograde. Whilst there are probably thousands of similar solutions to what we did (and most of them probably better), we built our own extremely simple interface so that the bulk of the software wouldn't have to call functions conditionally depending on the target database.
On each target we include a tiny file which specifies the database in use, the hostname, username and password. Here is an edited version:

<?
// config.php
//


// database management system ('mssql' or 'mysql'):
$DBms = 'mysql';

// database server:
$DBhost = 'landlord.gbdirect.co.uk';

$DBuser = 'xxxx';
$DBlpass = 'yyyy';

// database:
$DBname = 'zzz';
?>
A common set of interface functions is then included, as shown below:

<?

// Lots of stuff to give a database interface that is relatively
// independent of the underlying functions. No doubt this has
// been done elsewhere, but at present I don't have access to it.
// These globals need wrapping up as an object asap
//
//

include 'config.php';

$DBdebug = 0;

if($DBms == "mysql"){
        $DBconnect = "mysql_connect";
        $DBselect_db = "mysql_select_db";
        $DBquery = "mysql_query";
        $DBfetch_array = "mysql_fetch_array";
        $DBfetch_object = "mysql_fetch_object";
        $DBfetch_row = "mysql_fetch_row";
        $DBerror = "mysql_error";
        $DBnum_fields = "mysql_num_fields";
        $DBlist_fields = "mysql_list_fields";
        $DBsql_char_length = "Char_Length";
}elseif($DBms == "mssql"){
        $DBconnect = "mssql_connect";
        $DBselect_db = "mssql_select_db";
        $DBquery = "mssql_query";
        $DBfetch_array = "mssql_fetch_array";
        $DBfetch_object = "mssql_fetch_object";
        $DBfetch_row = "mssql_fetch_row";
        $DBerror = "mssql_error";
        $DBnum_fields = "mssql_num_fields";
        $DBlist_fields = "mssql_list_fields";
        $DBsql_char_length = "DataLength";
}

function DBconnect($host, $username, $password){
        global $DBconnect, $DBdebug;
        if($DBdebug)echo "DBconnect is $DBconnect\n";;
        $rval = $DBconnect($host, $username, $password);
        if($DBdebug)echo "Result of DBconnect is $rval\n";
        return $rval;
}

function DBuse($dbname, $connection){
        global $DBselect_db, $DBdebug;
        $rval = $DBselect_db($dbname, $connection);
        if($DBdebug)echo "Result of DBuse is $rval\n";
        return $rval;
}
function DBquery($query, $connection){
        global $DBquery, $DBdebug;
        if($DBdebug)echo "<br>\nQuery: $query";
        $rval = $DBquery($query, $connection);
        if($DBdebug)echo "<br>\nResult: $rval.";
        return $rval;
}
function DBerror($connection){
        global $DBerror, $DBdebug;
        $rval = $DBerror($connection);
        if($DBdebug)echo "Result of error() is $rval\n";
        return $rval;
}
function DBfetch_array($resultid){
        global $DBfetch_array, $DBdebug;
        $rval = $DBfetch_array($resultid);
        if($DBdebug)echo "Result of fetch_array is $rval\n";
        return $rval;
}
function DBfetch_object($resultid){
        global $DBfetch_object, $DBdebug;
        $rval = $DBfetch_object($resultid);
        if($DBdebug)echo "Result of fetch_object is $rval\n";
        return $rval;
}
function DBfetch_row($resultid){
        global $DBfetch_row, $DBdebug;
        $rval = $DBfetch_row($resultid);
        if($DBdebug)echo "Result of fetch_row is $rval\n";
        return $rval;
}
function DBnum_fields($resultid){
        global $DBnum_fields, $DBdebug;
        $rval = $DBnum_fields($resultid);
        if($DBdebug)echo "Result of num_fields is $rval\n";
        return $rval;
}
function DBlist_fields($resultid){
        global $DBlist_fields, $DBdebug;
        $rval = $DBlist_fields($resultid);
        if($DBdebug)echo "Result of list_fields is $rval\n";
        return $rval;
}
function DBaux_show_array($array, $sep = "\n"){
        while(list($k,$v) = each($array)){
                echo "Key $k has value $v $sep";
        }
}

// Concatenating strings is different for MySQL and SQL Server
function DBconcat($words)
{
        global $DBms;

        if ($DBms == 'mssql')
                return preg_replace('/ /', ' + ', $words);
        else
                return 'Concat(' . preg_replace('/ /', ', ', $words) . ')';
}

?>
Having built our interface we could get on with bulding the application. A couple of auxiliary PHP programs were writte so that we could always drop all the tables in the database and re-create them empty - these are run using PHP stand-alone instead of as a CGI script - and we found no serious problems other than normal application development concerns.
Most of the development was done using Linux tools with the NT virtual server directory mounted onto the Linux system (using smbmount) so that the one set of files, located on the NT system, were simultaneously being used to run the Linux and NT versions of the site. Switching our configuration files to tell each server which database to use allowed us to ensure that the software operated properly with both SQL Server and MySQL.
Of course we found incompatibilities between the dialects of SQL that the two databases use. MySQL considers every data type to be a string (or at least works as if it does) so that something like:
insert into blah values("abc", "123")
works even if the second column was declared to be a number. SQL Server won't stand for that and you need to be type-conscious.
A special function had to be written to deal with string concatenation in a consistent manner (see DBconcat above) because we needed it often enough. A small number of places in the software discovered irritating inconsistencies which had be dealt with.
Here is a very frustrating example of serious database-dependency:

<?
// Annoying database incompatibility!  MS Sql server can't cope with the ON
// clause of a LEFT JOIN involving more than two tables, so it has to have a
// special version of this query written using sub-selects

if ($DBms == 'mssql')
        list($found) = DBfetch_row(DBquery("
                        SELECT COUNT(part.id)
                        FROM part, price_factor, customer
                        WHERE part.id = '$SearchPartNum'
                                AND (allow_usa = 1 OR allow_uk = 1)
                                AND part.price_group = price_factor.part_group
                                AND price_factor.customer_group = customer.price_group
                                AND factor > 0
                                AND customer.id = '$Customer'
                                AND part.price_group NOT IN
                                (
                                  SELECT part_group
                                        FROM vending_prohibition, customer
                                        WHERE customer_type_id = customer_type_id
                                          AND customer.id = '$Customer'
                                )
                        ", $Con));
else
        list($found) = DBfetch_row(DBquery("
                        SELECT COUNT(part.id)
                        FROM part, price_factor, customer
                        LEFT JOIN vending_prohibition
                                ON part.price_group = vending_prohibition.part_group
                                        AND vending_prohibition.customer_type_id = customer.type_id
                        WHERE part.id = '$SearchPartNum'
                                AND allow_usa = 1
                                AND part.price_group = price_factor.part_group
                                AND price_factor.customer_group = customer.price_group
                                AND factor > 0
                                AND customer.id = '$Customer'
                                AND vending_prohibition.customer_type_id IS NULL
                        ", $Con));

?>
Other niggles we encountered were:
  • Needing to select only a part of the resultset from a query (see `Performance' below) requires different SQL clauses to be used for each database.
  • A day was wasted tracking down a difference between the databases where SQL Server stores DECIMAL types with a set of trailing NUL characters tacked on the end. When retrieved from the database by the version of PHP we were using, it would ignore the fractional part. A price like $1.23 was being retrieved as $1 from SQL Server; using MySQL we got the right value back.
  • A small difference between PHP's handling of strftime resulted in the %e format being expanded as a blank on the NT implementation. We had to use %d instead and adjust the result.
The goal of building code which would run without caring which database product it was accessing simply couldn't be done cleanly enough for our taste. Extending it to even more databases would not be a pleasant prospect.
Performance
We'd love to say that we have done a lot of performance testing, but we haven't. Apart from noticing that MySQL is a good deal nippier than SQL Server at most of the tasks, database performance hasn't been a big concern. Our biggest performance problem was having to to deal (on the deployed system) with a WAN separating the web front-end from the database. Customers searching for particular types of component could easily generate queries that returned record sets with thousands of entries and the first version of the software was appallingly slow when that happened. By paging through record sets using MySQL's limit and SQL Server's equivalent of it we got up to an acceptable level of performance and that was enough.
Conclusions
Cross-platform development with PHP proved to be entirely possible. Cross-database development (as well) is trickier but by no means impossible. Our customer is now planning to deploy the same code on Linux where it is organisationally acceptable and not constrained by the Microsoft-only corporate `strategy'. PHP has caused us very little heartache. Our concerns about the insistence of running the database via a low bandwidth connection from the web server proved to be valid, but a workaround has been found. Our client has taken significant order volumes using this system and has now commissioned several extensions.
Overall, a success.
This article was republished with permission. The original is at http://development.gbdirect.co.uk/phpwin.html.
Mike Banahan has been involved in the Unix and Open Standards area since 1977. He has been an active member of the C and C++ standards committees and currently works on consulting and training projects through his company GBdirect. The company switched to using exclusively Open Source products in 1978, except where demanded by clients.