* * * * *

                       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("[email protected]","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
[12] http://www.php.net/manual/en/function.ora-plogon.php
[13] http://www.php.net/manual/en/function.mysql-affected-rows.php
[14] http://www.php.net/manual/en/function.pg-affected-rows.php
[15] http://www.php.net/manual/en/function.ora-numrows.php
[16] http://www.php.net/manual/en/function.mysql-error.php
[17] http://www.php.net/manual/en/function.pg-last-error.php
[18] http://www.php.net/manual/en/function.ora-error.php
[19] http://www.php.net/manual/en/function.mysql-insert-id.php
[20] http://www.php.net/manual/en/function.pg-last-oid.php
[21] http://www.php.net/manual/en/function.mysql-fetch-row.php
[22] http://www.php.net/manual/en/function.pg-fetch-row.php
[23] http://www.php.net/manual/en/function.ora-fetch.php
[24] http://www.php.net/manual/en/function.mysql-num-fields.php
[25] http://www.php.net/manual/en/function.pg-num-fields.php
[26] http://www.php.net/manual/en/function.ora-numcols.php
[27] http://www.php.net/manual/en/function.mysql-num-rows.php
[28] http://www.php.net/manual/en/function.pg-num-rows.php
[29] http://www.php.net/manual/en/function.ora-numrows.php
[30] http://www.php.net/manual/en/function.mysql-query.php
[31] http://www.php.net/manual/en/function.pg-query.php
[32] http://www.php.net/manual/en/function.ora-parse.php
[33] http://www.php.net/manual/en/function.ora-exec.php
[34] http://www.php.net/manual/en/function.mysql-result.php
[35] http://www.php.net/manual/en/function.pg-get-result.php
[36] http://www.php.net/manual/en/function.mysql-select-db.php
[37] http://www.php.net/manual/en/function.mysql-field-name.php
[38] http://www.php.net/manual/en/function.pg-field-name.php

Email author at [email protected]

You are viewing proxied material from gopher.conman.org. The copyright of proxied material belongs to its original authors. Any comments or complaints in relation to proxied material should be directed to the original authors of the content concerned. Please see the disclaimer for more details.