PHP modules for standardizing DB access
Por: Nediam <javier@nediam.com.mx>
Publication date: 2005-05-25
Last updated: 2006-03-26
Sometimes we require that some of our applications be able to run using different database management systems. This situation may arise either because the
company where our application is running is in the process of migrating from its RDBMS and our programs still need to keep running, or because our
different clients work with different DBMSs (one client may use MySQL for his company while another could only use Informix), which is highly likely to
occur in real life. This is why there is the need for a mechanism that enables our applications to work in a 'transparent' way with relation to the
database management system, regardless of which it is.
In order to achieve this, one posible solution could be to manually modify each of the scripts that make up our application with the appropriate functions
to interact with the new DBMS. For example, if our application consists of 10 scripts that use access to DBs, we would have to modify each of the 10 files,
one by one. Maybe it wouldn't be so difficult and it would not take too long to make these changes, but what happens if our application has 200 or 500
scripts that use access to DBs?
For medium or large applications many developers use a module for interaction with DBs. This is very useful because in this module the connection
parameters are specified as well as other necessary configurations for establishing the connection with the DBMS, and in case some parameter related to
the DB server had to be changed later (for example the IP address of the server or the DB user password), only this file would have to be changed and not
all scripts.
Then what we would have to do is standardize the functions that conform the DB interaction modules so our application can work in a similar way
no matter which DBMS is interacting with. In other words, if our application currently interacts with PostgreSQL and in our module we named the function to
execute some SQL sentence execute_query, then, if we need that our application now use Informix, in the module we create to interact with Informix
the function for executing SQL sentences must have the same exact name: execute_query. The same applies for the names of all functions in our
module for DB interaction.
Let us suppose, for example, that our application currently uses Informix, and that we have a file named modules_lst.inc containing all the
includes we need. It would look something like this:
<?php
function add_requires($path='./')
{
require($path . 'functions/informix_fns.php');
require($path . 'functions/output_fns.php');
require($path . 'functions/misc_fns.php');
require($path . 'functions/math_fns.php');
}
?>
Continuing with the example, the scripts that make up the application would begin something similar to this:
<?php
require('./modules_lst.inc');
add_requires();
....
....
?>
So to in order for our application be able to work with another DBMS, instead of changing 200 or 500 scripts we simply have to change one line in one
script! (supposing that there is already a module for interaction with the new DBMS. At the end of this article there are some modules for interaction with
distinct DBMS available for downloading. Continuing with the example, to make our application now work with PostgreSQL instead of Informix, first we have
to design or obtain a module for DB access in PostgreSQL. We need to verify that the new module meets the standardization of function names in our current
module in Informix. We must also make sure both managers have the functionality required for the application. Then we need to do the corresponding tests
to verify that our module is working correctly. Finally, we need to change the line of the require in the file modules_lst.inc to make our
application now use PostgreSQL DB access module:
<?php
function add_requires($path='./')
{
require($path . 'functions/postgresql_fns.php');
require($path . 'functions/output_fns.php');
require($path . 'functions/misc_fns.php');
require($path . 'functions/math_fns.php');
}
?>
That would be what it takes to get our application to start using PostgreSQL instead of Informix, without modifying a single line of the 200 or 500
scripts that make up our application :).
The following table shows the standard of names proposed for interacting with databases and the functions that would have to be used to be implemented in
some DBMS:
| Standard function | Description | PostgreSQL function | MySQL function | Informix function | Oracle function |
| fco_db_connect($db) | Opens a DB connection | pg_connect | mysql_connect | ifx_connect | oci_connect |
| fco_db_pconnect($db) | Opens a persistent DB connection | pg_pconnect | mysql_pconnect | ifx_pconnect | oci_pconnect |
| fco_db_close($db) | Closes a DB connection | pg_close | mysql_close | ifx_close | oci_close |
| fco_db_query($db, $qry) | Executes a SQL sentence | pg_query | mysql_query | ifx_query | oci_parse oci_execute |
| fco_db_fetch_row($res) | Returns an array containing the values of the next row of the result resource | pg_fetch_row | mysql_fetch_row | N/A | oci_fetch_row |
| fco_db_fetch_array($res) | Returns an associative array with the values of the next row of the result resource | pg_fetch_array | mysql_fetch_assoc mysql_fetch_array |
ifx_fetch_row | oci_fetch_array |
| fco_db_fetch_all($res) | Returns and associative array with all the rows of the result resource | pg_fetch_all | N/A | N/A | oci_fetch_all |
| fco_db_num_rows($res) | Returns the number of rows that contains a result resource | pg_num_rows | mysql_num_rows | ifx_num_rows | oci_num_rows |
| fco_db_num_fields($res) | Returns the number of fields that contains a result resource | pg_num_fields | mysql_num_fields | ifx_num_fields | oci_num_fields |
| fco_db_table_types($db, $table) | Returns an associative array with the names of the fields and data types of a table | N/A | N/A | ifx_fieldtypes | N/A |
| fco_db_last_error( ) | Saves in a global variable the description of the last db error occurred | pg_last_error | mysql_error | ifx_errormsg | oci_error |
| fco_db_display_last_error( ) | Displays the last db error ocurred and exits the script | N/A | N/A | N/A | N/A |
Note: The prefix 'fco' in the names of the standard functions was used to avoid conflicts with the names of native PHP functions
Following this standard of names, the modules for interact with some DBMS would look like this:
| DMBS | Visualize | Download |
| - PostgreSQL | postgresql_fns | postgresql_fns |
| - MySQL | mysql_fns | mysql_fns |
| - Informix | informix_fns | informix_fns |
| - Oracle | oracle_fns | oracle_fns |
In order for these modules to work, the clients of the DBMS in question should have been installed already and properly configured. Also, PHP should have
been compiled with support for these. If you have any question regarding clients' configuration (particularly Informix and Oracle, which are the most
tedious) feel free to email me and we can sort it out.
The functions presented in this article are the basic ones required for interaction with databases. The important thing is to adapt the modules for each
particular application. For example, a simple application could be well enough with the functions herein presented so it would be able to work with
different DMBS. Yet, other applications may require more standardized functions. The particularities of each manager we are working with should also be considered (such as the fact that the array that returns the function ifx_fetch_row in Informix has no numerical indexes, or that in Oracle the names of the keys in associative arrays are in capitals). This is to avoid affecting the functionality of our application when we migrate to another DBMS.
I hope this article could be helpful for you. If you have any questions or comments, please send them to <javier@nediam.com.mx>
References:
- PHP Manual - http://www.php.net/manual/en/index.php
- Welling, L., Thompson, L. PHP and MySQL Web Development Ed. SAMS
The latest version of this document is available at:
http://nediam.com.mx/en/source/db_modules/index.php