Professional PHP5 (Programmer to Programmer Series)
ISBN: N/A
EAN: N/A
EAN: N/A
Year: 2003
Pages: 182
Pages: 182
The easiest way to implement a basic abstraction layer is to create a class that acts as a wrapper around the native functions of a particular database backend. To reuse this component in a project with a different backend, you would need to rewrite the code in which you use the native functions for that RDBMS. This example happens to use PostgreSQL. Later in this chapter we introduce you to a more sophisticated approach, but this simple example should help illustrate how a database abstraction layer works.
Because most applications don't need to modify the internal structure of the database, we can limit the functionality of this simple abstraction layer to SELECT, INSERT, UPDATE, and DELETE statements. The select()method should return an associative array that represents the entire recordset. The insert(), update(), and delete()functions should return the number of records affected by the underlying SQL statement. To make life easier, these last three functions should handle any string escaping that needs to happen.
You're likely to need only to connect to one database concurrently, so create a central configuration file that stores the information needed to connect. We'll use PHP for this configuration file, but you could just as easily use a number of more human-readable formats, as you'll discover in Chapter 17. Save this file as config.php:
<?php $cfg['db']['host'] = 'localhost'; $cfg['db']['port'] = 5432; //PostgreSQL default port $cfg['db']['user'] = 'postgresql'; $cfg['db']['password'] = 'mypass'; $cfg['db']['name'] = 'mydatabase'; ?>
The class should open a connection to the database in its constructor and close the connection in its destructor. The connection string will come from the config.php configuration file. Create a file called class.Database.php and enter the following code:
<?php require_once('config.php'); class Database { private $hConn; public function __construct() { global $cfg; // allow our method to access the $cfg associative array // by making it global $connString = ' host=' . $cfg['db']['host']; $connString .= ' user=' . $cfg['db']['user']; $connString .= ' password=' . $cfg['db']['password']; $connString .= ' port=' . $cfg['db']['port']; $connString .= ' dbname=' . $cfg['db']['name']; $this->hConn = @pg_connect($connString); if(! is_resource($this->hConn)) { throw new Exception("Unable to connect to the database " . "using \"$connString\"", E_USER_ERROR); } } public function __destruct() { if(is_resource($this->hConn)) { @pg_close($this->hConn); } } }
In the constructor, a connection to the database server is opened and the connection handle is assigned to the private $hConn member. If a connection could not be established using the supplied data, an exception is thrown. Be sure to wrap all calls to new Database() in a try...catch block.
To make this useful, you'll need to create methods that allow SELECT, INSERT, UPDATE, and DELETE statements. The code in the next section shows how these might be implemented.
When selecting information from the database, that information is a lot easier to access if it's stored in an associative array, in which the keys correspond to the field names of the table in question. The select() method shows how this works in PostgreSQL.
public function select($sql) { $hRes = @pg_query($this->hConn, $sql); if(! is_resource($hRes)) { $err = pg_last_error($this->hConn); throw new Exception($err); } $arReturn = array(); while(($row = pg_fetch_assoc($hRes)) ) { $arReturn[] = $row; } return $arReturn; }
For each row of data returned by the query specified in the $sql parameter, a two-dimensional array is built. The first dimension is a numeric index representing the row number (starting with 0). The second dimension is the name of the field.
This isn't a great approach for dealing with very large recordsets because every row (or tuple) is stored in memory. We look at some alternate ways of implementing a select() method later in this chapter that will be far more efficient for dealing with large recordsets.
Now that you can get information out of the database, you'll need to be able to add, modify, and delete records. The following code shows one possible implementation of the insert(), update(), and delete() methods. The first method takes as its parameters the name of a table and an associative array of field names and values. The SQL statement is constructed inside the function, properly escaping string values in the process:
public function insert($table, $arFieldValues) { $fields = array_keys($arFieldValues); $values = array_values($arFieldValues); // Create a useful array of values // that will be imploded to be the // VALUES clause of the insert statement. // Run the pg_escape_string function on those // values that are something other than numeric. $escVals = array(); foreach($values as $val) { if(! is_numeric($val)) { //make sure the values are properly escaped $val = "'" . pg_escape_string($val) . "'"; } $escVals[] = $val; } //generate the SQL statement $sql = " INSERT INTO $table ("; $sql .= join(', ', $fields); $sql .= ') VALUES('; $sql .= join(', ', $escVals); $sql .= ')'; $hRes = pg_query($sql); if(! is_resource($hRes)) { $err = pg_last_error($this->hConn) . "\n" . $sql; throw new Exception($err); } return pg_affected_rows($hRes); }
The update method takes as its parameters the name of a table and an associative array of field names and values, where the keys of the array are the fields to be altered and the values are the new values for those fields. The $arConditions array is a similarly structured array of fields and values to be used in the WHERE clause:
public function update($table, $arFieldValues, $arConditions) { // create a useful array for the SET clause $arUpdates = array(); foreach($arFieldValues as $field => $val) { if(! is_numeric($val)) { //make sure the values are properly escaped $val="'" . pg_escape_string($val) . "'"; } $arUpdates[] = "$field = $val"; } // create a useful array for the WHERE clause $arWhere = array(); foreach($arConditions as $field => $val) { if(! is_numeric($val)) { //make sure the values are properly escaped $val = "'" . pg_escape_string($val) . "'"; } $arWhere[] = "$field = $val"; } $sql = "UPDATE $table SET "; $sql .= join(', ', $arUpdates); $sql .= ' WHERE ' . join(' AND ', $arWhere); $hRes = pg_query($sql); if(! is_resource($hRes)) { $err = pg_last_error($this->hConn) . NL . $sql; throw new Exception($err); } return pg_affected_rows($hRes); }
The delete method takes as its parameters the name of the table from which rows are to be deleted, and an associate array of field => value pairs, which are used to generate the WHERE clause applicable to this deletion:
function delete($table, $arConditions) { //create a useful array for generating the WHERE clause $arWhere = array(); foreach($arConditions as $field => $val) { if(! is_numeric($val)) { //make sure the values are properly escaped $val = "'" . pg_escape_string($val) . "'"; } $arWhere[] = "$field = $val"; } $sql = "DELETE FROM $table WHERE " . join(' AND ', $arWhere); $hRes = pg_query($sql); if(! is_resource($hRes)) { $err = pg_last_error($this->hConn) . NL . $sql; throw new Exception($err); } return pg_affected_rows($hRes); }
In each of these three functions the SQL statement is constructed by joining members from the $arFieldValues and $arCondition arrays. The string values of these arrays have been properly escaped to handle awkward characters in particular, apostrophes.
Having executed the generated SQL statement, each function returns the number of rows affected by the query. Note that a return value of 0 (which will evaluate as false) does not necessarily indicate failure, just that nothing was modified.Especially for UPDATE and DELETE statements, this may well be what is expected, depending on the input to the function and the contents of the table. Generally, INSERT statements should always affect exactly one record, but if the table has triggers that interfere with the INSERT, a valid INSERT statement may affect zero records, or more than one.
Using this class in your applications is very straightforward, and far cleaner than directly interacting with the pg_[x] functions. Create a simple table called mytable in PostgreSQL by using the following SQL statement.
CREATE TABLE "mytable" ( "id" SERIAL PRIMARY KEY NOT NULL, "myval" varchar(255) );
Then create a test file, DB_test.php that looks like this:
<?php require_once('class.Database.php'); try { $objDB = new Database(); } catch (Exception $e) { echo $e->getMessage(); exit(1); } try { $table = "mytable"; $objDB->insert($table, array('myval' => 'foo')); $objDB->insert($table, array('myval' => 'bar')); $objDB->insert($table, array('myval' => 'blah')); $objDB->insert($table, array('myval' => 'mu')); $objDB->update($table, array('myval' => 'baz'), array('myval' => 'blah')); $objDB->delete($table, array('myval' => 'mu')); $data = $objDB->select("SELECT * FROM mytable"); var_dump($data); } catch (Exception $e) { echo "Query failure" . NL; echo $e->getMessage(); } ?>
In this example, four insert statements are issued, creating four rows in mytable where myval is equal to 'foo', 'bar', 'blah', and 'mu' respectively. The update statement sets myval to 'baz' in any rows where myval is currently 'blah' (which will be just one row, that which we have previously inserted). The delete statement removes the record where 'myval' is equal to 'mu'.
The var_dump statement is used to show the current contents of the table after this flurry of activity and should give you output similar to the following:
array(3) { [0]=> array(2) { ["id"]=> string(2) "1" ["myval"]=> string(3) "foo" } [1]=> array(2) { ["id"]=> string(2) "2" ["myval"]=> string(3) "bar" } [2]=> array(2) { ["id"]=> string(2) "3" ["myval"]=> string(3) "baz" } } Note that subsequent runs of this test script will result in further insertions into the database table, as there is no code to empty it before or after use. If you've hit the Refresh button in your browser a few times, your output may well be somewhat longer than the above.
You'll notice from the above output that the select statement has returned the entire recordset as a two-dimensional array. The topmost level of the array is numerically indexed with one member for each row. The second level is an associative array in which the field name is the key.
If you ever wanted to use this component in an application that ran on MySQL instead of PostgreSQL, you would need to change all the pg_[x] functions to their mysql_[x] equivalents. Doing so sometimes requires changing the order of parameters to those functions or changing the way in which the specific parts of the internals of this class work, but the external API can remain the same regardless of the type of database. Other reusable componentry that needs to interact with a SQL backend can either be recycled from application one to application two without modification (if the SQL statements needed by those components can run without modification on the second platform) or will run with minor changes to the SQL statements and no changes to the PHP code.
The example Database class shown previously is, however, too simple for most applications. It doesn't handle large recordsets efficiently, lacks support for transactions, and has very rudimentary error handling. You could spend the rest of the chapter inventing a more robust set of database abstraction functions, but much of this work has already been done for you.