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 [1] project. Okay, PHP supports several different types of databases, like PostgreSQL [2] and MySQL [3] among others, and being SQL (Standard Query Language), 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 [4] said, the simple stuff should be simple). So, I would expect something like:

>

```

$db = db_use('mysql');

db_connect($db,"mysql.example.net","db_user","soopersecretpassword");

db_table($db,"warehouse");

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

db_close($db);

$db = db_use('postgresql');

db_connect($db,"pgsql.example.net","db_user","donttellanyone");

db_table($db,"shops");

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

db_close($db);

```

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 [5]. 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 [6]):

>

```

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

```

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

>

```

$list=file("../SQLcalls.txt");

$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?

>

```

mysql_connect

mysql_affected_rows

mysql_error

mysql_insert_id

mysql_fetch_row

mysql_num_fields

mysql_num_rows

mysql_query

mysql_result

mysql_select_db

mysql_field_name

```

Okay.

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—

IT WON'T WORK!

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

Table: PHP function equivalents between MySQL, PostgreSQL and Oracle
MySQL [7]	PostgreSQL	Oracle [8]
------------------------------
mysql_connect() [9]	pg_connect() [10]	ora_logon() [11] or ora_plogon() [12]
mysql_affected_rows() [13]	pg_affected_rows() [14]	ora_numrows() [15]
mysql_error() [16]	pg_last_error() [17]	ora_error() [18]
mysql_insert_id() [19]	pg_last_oid() [20] (guess)	?
mysql_fetch_row() [21]	pg_fetch_row() [22]	ora_fetch() [23]
mysql_num_fields() [24]	pg_num_fields() [25]	ora_numcols() [26]
mysql_num_rows() [27]	pg_num_rows() [28]	ora_numrows() [29]
mysql_query() [30]	pg_query() [31]	ora_parse() [32] + ora_exec() [33]
mysql_result() [34]	pg_get_result() [35]	?
mysql_select_db() [36]	?	?
mysql_field_name() [37]	pg_field_name() [38]	?

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("mysql.example.net","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("host=pgsql.example.net port=5432 dbmame=warehouse user=db_user password=donttellanyone");

```

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

>

```

$oracle = ora_plogon("db_user@oracle.example.net","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.

[1] http://www.php.net/

[2] http://www.postgresql.org/

[3] http://dev.mysql.com/

[4] http://minnow.cc.gatech.edu/squeak/378

[5] http://www.oracle.com/

[6] http://www.oscommerce.org/

[7] http://www.php.net/manual/en/ref.mysql.php

[8] http://www.php.net/manual/en/ref.oracle.php

[9] http://www.php.net/manual/en/function.mysql-connect.php

[10] http://www.php.net/manual/en/function.pg-connect.php

[11] http://www.php.net/manual/en/function.ora-logon.php