The Boston Diaries

The ongoing saga of a programmer who doesn't live in Boston, nor does he even like Boston, but yet named his weblog/journal “The Boston Diaries.”

Go figure.

Friday, March 25, 2005

More like a foolish inconsitency

A foolish consistency is the hobgoblin of little minds.

Ralph Waldo Emerson

You want to use a database for a PHP project. Okay, PHP supports several different types of databases, like PostgreSQL and MySQL among others, and being SQL, there shouldn't be much difference bewtween SELECT statements, right? (wrong I know—there can be huge differences between SQL implementations, but like Alan Kay said, the simple stuff should be simple). So, I would expect something like:

$db = db_use('mysql');
$result = db_query($db,"SELECT widgets FROM parts WHERE color = 'red'");

$db = db_use('postgresql');
$result = db_query($db,"SELECT price FROM catalog WHERE parts='widgets'");

You select the type of database you want to use, and PHP automagically handles the details for you. The simple stuff should be simple, right?

But not in PHP. No. In PHP you get a show bunch of functions that work only on MySQL, and another set for PostgreSQL, and yet another set for Oracle. It's not like once you select a database that you'll end up changing it any time soon, right? Who changes databases? So chances are, once you select a database, that's it and who cares if there's a specific set of functions for this database and a different set for another database?

Well, unless you make it available for other people to use (open source or not) and they want to use one of those other databases? Heaven forbid that happening.

But hey, it could happen, right?

Which gets me to some PHP code I had the pleasure of dealing with today (and no, it wasn't osCommerce):

$result = $system_query("SELECT * FROM sometable$name");

Hello! $system_query? That's a variable! Hmmm … that's … interesting. Poke around some more …

$system_connect = $list[0];
$system_affected_rows = $list[1];
$system_error = $list[2];
$system_insert_id = $list[3];
$system_fetch_row = $list[4];
$system_num_fields = $list[5];
$system_num_rows = $list[6];
$system_query = $list[7];
$system_result = $list[8];
$system_select_db = $list[9];
$system_field_name = $list[10];

$system_connect = ereg_replace("\n","","$system_connect");
$system_affected_rows = ereg_replace("\n","","$system_affected_rows");
$system_error = ereg_replace("\n","","$system_error");
$system_insert_id = ereg_replace("\n","","$system_insert_id");
$system_fetch_row = ereg_replace("\n","","$system_fetch_row");
$system_num_fields = ereg_replace("\n","","$system_num_fields");
$system_num_rows = ereg_replace("\n","","$system_num_rows");
$system_query = ereg_replace("\n","","$system_query");
$system_result = ereg_replace("\n","","$system_result");
$system_select_db = ereg_replace("\n","","$system_select_db");
$system_field_name = ereg_replace("\n","","$system_field_name");

$system_connect = ereg_replace(13,"","$system_connect");
$system_affected_rows = ereg_replace(13,"","$system_affected_rows");
$system_error = ereg_replace(13,"","$system_error");
$system_insert_id = ereg_replace(13,"","$system_insert_id");
$system_fetch_row = ereg_replace(13,"","$system_fetch_row");
$system_num_fields = ereg_replace(13,"","$system_num_fields");
$system_num_rows = ereg_replace(13,"","$system_num_rows");
$system_query = ereg_replace(13,"","$system_query");
$system_result = ereg_replace(13,"","$system_result");
$system_select_db = ereg_replace(13,"","$system_select_db");
$system_field_name = ereg_replace(13,"","$system_field_name");

Um … okay … what's in SQLcalls.txt?



A list of the MySQL function calls in PHP are read in, then the trailing line ending characters are stripped. Never mind that could be done as:

$list                 = file("../SQLcalls.txt");
$system_connect       = rtrim($list[ 0]);
$system_affected_rows = rtrim($list[ 1]);
$system_error         = rtrim($list[ 2]);
$system_insert_id     = rtrim($list[ 3]);
$system_fetch_row     = rtrim($list[ 4]);
$system_num_fields    = rtrim($list[ 5]);
$system_num_rows      = rtrim($list[ 6]);
$system_query         = rtrim($list[ 7]);
$system_result        = rtrim($list[ 8]);
$system_select_db     = rtrim($list[ 9]);
$system_field_name    = rtrim($list[10]);

which not only would be slightly faster, but a bit more maintainable and portable (and I don't even know PHP that well and already I'm writing better code in it—sheesh!). But besides, that, the intent of the programmer seems to be a way to isolate the name of the function so that the code can be quickly “ported” to use different databases. Laudable, except for one small little detail—


Let's try using PostgreSQL. Well, let's see if we can map the MySQL functions listed to their PostgreSQL or Oracle counterparts:

PHP function equivalents between MySQL, PostgreSQL and Oracle
MySQL PostgreSQL Oracle
mysql_connect() pg_connect() ora_logon() or ora_plogon()
mysql_affected_rows() pg_affected_rows() ora_numrows()
mysql_error() pg_last_error() ora_error()
mysql_insert_id() pg_last_oid() (guess) ?
mysql_fetch_row() pg_fetch_row() ora_fetch()
mysql_num_fields() pg_num_fields() ora_numcols()
mysql_num_rows() pg_num_rows() ora_numrows()
mysql_query() pg_query() ora_parse() + ora_exec()
mysql_result() pg_get_result() ?
mysql_select_db() ? ?
mysql_field_name() pg_field_name() ?

It's beginning to look pretty bad, but that's not the worse of it. Let's just concentrate on the connect functions. mysql_connect() looks like:

$mysql = mysql_connect("","db_user","soopersekrit");

But that just connects you to the MySQL server. You still have to select which database you want to use. Then you have pg_connect():

$pg = pg_connect(" port=5432 dbmame=warehouse user=db_user password=donttellanyone");

In which you can specify the database. Now there's ora_plogon():

$oracle = ora_plogon("","shhhh");

Which supposedly will hook you up with the database in question. I guess. Because I couldn't locate the PHP Oracle equivalent to mysql_select_db(). But aside from that, notice anything about the three calls? Like how they're all completely different? mysql_connect() takes three parameters (well, there are more, but they're optional), pg_connect() takes just one, but the single string argument contains name/value pairs, some of which are optional! And ora_plogon() takes two.

Nice try, but a wasted effort.

Obligatory Picture

[“I am NOT a number, I am … a Q-CODE!”]

Obligatory Contact Info

Obligatory Feeds

Obligatory Links

Obligatory Miscellaneous

You have my permission to link freely to any entry here. Go ahead, I won't bite. I promise.

The dates are the permanent links to that day's entries (or entry, if there is only one entry). The titles are the permanent links to that entry only. The format for the links are simple: Start with the base link for this site:, then add the date you are interested in, say 2000/08/01, so that would make the final URL:

You can also specify the entire month by leaving off the day portion. You can even select an arbitrary portion of time.

You may also note subtle shading of the links and that's intentional: the “closer” the link is (relative to the page) the “brighter” it appears. It's an experiment in using color shading to denote the distance a link is from here. If you don't notice it, don't worry; it's not all that important.

It is assumed that every brand name, slogan, corporate name, symbol, design element, et cetera mentioned in these pages is a protected and/or trademarked entity, the sole property of its owner(s), and acknowledgement of this status is implied.

Copyright © 1999-2024 by Sean Conner. All Rights Reserved.