Using PHP Objects to Access Your Database Tables (Part 1)
By Tony Marston2005-04-05
My 'database_table' class
|
|
Background
I have modeled my approach on the design I used in a language prior to switching to PHP. This prior language was based on components rather than objects, but while reading up on the basics of OOP I discovered that there were in fact some similarities:
| Object Oriented | Component Based |
|---|---|
| You can define CLASSES. | You can define COMPONENTS. |
| You can define properties (data) which can be maintained within the class. | You can define variables within the component definition. |
| You can define methods (functions) to manipulate the properties within the class. | You can define operations (functions) within the component definition. |
| You can define public methods which are accessible from outside the class. | You can define operations which are accessible from outside the component. |
| You can define private methods which are only accessible from inside the class. | You can define local procedures which are only accessible from inside the component. |
| You can define a class constructor which is processed when a class instance is created. | You can define an INIT operation which is processed when a component instance is created. |
| You can define a class destructor which is processed when a class instance is terminated. | You can define a QUIT operation which is processed when a component instance is terminated. |
| You can create one or more instances of a class, each with its own object name. | You can create one or more instances of a component, each with its own instance name. |
| A class instance remains in existence until it is terminated. | A component instance remains in existence until it is terminated. |
| You can access object properties either directly or via a method (although it is considered bad practice to access properties directly). | You can only access component variables via an operation. |
| You can define a class which extends an existing class, thus inheriting all the properties and methods of that that class. | You cannot extend a component, but by using component templates and include files it is possible to share quantities of common code. |
While reading what other developers had done with database objects in PHP (and even Java) I noticed several characteristics which I did not have in my previous component-based solution and which I most certainly did not want to have in my new object-based solution. These were:
Having 'setters' and 'getters' for each column within the table. I do not bother with individual column names as the argument for my standard 'getData' and 'putData' functions is an associative array. As this array can contain any number of 'name=value' pairs I can access the data for any individual column by using PHP's standard array functionality. In my component-based solution I used XML streams instead of arrays, but the principle was the same.
Having separate instances for each database row. This is total overkill as far as I am concerned. As arrays in PHP can contain separate entries for each row I can use a single object to handle all the rows I need. In my component-based solution the XML stream could also contain any number of rows, so I did not need multiple instances for multiple rows.
Having method/operation/function names which are specific to the database table being accessed. For example for the CUSTOMER table you would have a 'getCustomer' and 'putCustomer' method while for the PRODUCT table you would have 'getProduct' and 'putProduct'. This immediately means that you cannot use a general-purpose script to communicate with a database object as you would need to know what methods to use instead of the generic 'getData' and 'putData'. Using generic methods for all derived classes actually conforms to the OOP idea of polymorphism, so I do not see why some developers insist on having different methods for each derived class.
The main reason for adopting the OOP approach is to maximise the amount of reusable code, so I set out to create a base class which could contain all the standard code for getting data in and out of any database table. I would then be able to create a separate class for each physical database table which would extend this base class and would therefore only have to contain extra code that is specific to that particular database table.
Class Variables
The first task is the define the class and its variables, like so:
class Default_TableClass Constructor
{
var $tablename; // table name
var $dbname; // database name
var $rows_per_page; // used in pagination
var $pageno; // current page number
var $lastpage; // highest page number
var $fieldlist; // list of fields in this table
var $data_array; // data from the database
var $errors; // array of error messages
This is immediately followed by the constructor method. Note that each derived class should have its own constructor containing proper values:
function Default_Table ()You should notice here that the constructor for each table identifies the name of the database to which that particular table belongs. It is therefore possible to create classes for tables which belong to more than one database, and to access more than one database within the same session.
{
$this->tablename = 'default';
$this->dbname = 'default';
$this->rows_per_page = 10;
$this->fieldlist = array('column1', 'column2', 'column3');
$this->fieldlist['column1'] = array('pkey' => 'y');
} // constructor
The variable $fieldlist is used to list all the columns within that table, and to identify which is the primary key. How this is used will become apparent later on.
'getData' Method
This is my standard method for getting data out of the database through the object. It can be used to retrieve any number of rows. I start by defining the function name with any arguments, then initialise some variables. Note that $this->pageno may have been set previously to request a particular page in a multi-page display. By default this starts at 1, but different values may be requested from the user by using hyperlinks provided on the HTML page.
Someone once suggested that I have a getNextPage() and getPreviousPage() method to provide the navigation mechanism, but this is both unnecessary and restrictive - my single method can jump to any page that is available rather than current+1 or current-1.
function getData ($where)Next I connect to the database using my standard db_connect procedure. Note that the table name is picked up from the variable which was set in the class constructor. In the event on an error this will invoke my standard error handler.
{
$this->data_array = array();
$pageno = $this->pageno;
$rows_per_page = $this->rows_per_page;
$this->numrows = 0;
$this->lastpage = 0;
global $dbconnect, $query;The input argument $where can either be empty or it can contain selection criteria in the format "column1='value', column2='value', ...". If it is not empty I construct a separate string to include in any database query.
$dbconnect = db_connect($this->dbname) or trigger_error("SQL", E_USER_ERROR);
if (empty($where)) {
$where_str = NULL;
} else {
$where_str = "WHERE $where";
} // ifNext we want to count the number of rows which satisfy the current selection criteria:
$query = "SELECT count(*) FROM $this->tablename $where_str";If there is no data we can exit at this point.
$result = mysql_query($query, $dbconnect) or trigger_error("SQL", E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$this->numrows = $query_data[0];
if ($this->numrows <= 0) {
$this->pageno = 0;
return;
} // ifIf there is data then we want to calculate how many pages it will take based on the page size given in $rows_per_page.
if ($rows_per_page > 0) {
$this->lastpage = ceil($this->numrows/$rows_per_page);
} else {
$this->lastpage = 1;
} // ifNext we must ensure that the requested page number is within range. Note that the default is to start at page 1.
if ($pageno == '' OR $pageno <= '1') {
$pageno = 1;
} elseif ($pageno > $this->lastpage) {
$pageno = $this->lastpage;
} // if
$this->pageno = $pageno;Now we can construct the LIMIT clause for the database query in order to retrieve only those rows which fall within the specified page number:
if ($rows_per_page > 0) {
$limit_str = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
} else {
$limit_str = NULL;
} // ifNow we can build the query string and run it.
$query = "SELECT * FROM $this->tablename $where_str $limit_str";At this point $result is simply a resource that points to the data, so we need to extract the data and convert it into an associative array. This will have an entry for each row starting at zero, and for each row it will have a series of 'name=value' pairs, one for each column which was specified in the SELECT statement.
$result = mysql_query($query, $dbconnect) or trigger_error("SQL", E_USER_ERROR);
while ($row = mysql_fetch_assoc($result)) {
$this->data_array[] = $row;
} // while
Finally we release the database resource and return the associative array containing all the data.
mysql_free_result($result);I should point out here that this is a simplified version of the code which I actually use in my application. My query string is constructed from several component parts as shown in the following:
return $this->data_array;
} // getData
$query = "SELECT $select_str FROM $from_str $where_str $group_str $having_str $sort_str $limit_str";Each of these component parts can be tailored by instructions from the calling script in order to provide the maximum amount of flexibility. In this way I think I have succeeded in building a single function that can handle a multitude of possibilities.
'insertRecord' Method
When the details of a new database record are input through the client's browser they are received by your PHP script in the $_POST array. It therefore seems logical to me to use the $_POST array as the input to my next function. As usual we start by defining the function name and its argument(s). We also initialise the array of potential error messages.
function insertRecord ($fieldarray)We then connect to the database using the code described previously:
{
$this->errors = array();
global $dbconnect, $query;Now, using the contents of $fieldlist which was set in the class constructor we can edit the input array to filter out any items which do not belong in this database table. This removes the SUBMIT button, for example.
$dbconnect = db_connect($this->dbname) or trigger_error("SQL", E_USER_ERROR);
$fieldlist = $this->fieldlist;We can now construct the query string to insert a new record into the database:
foreach ($fieldarray as $field => $fieldvalue) {
if (!array_key_exists($field, $fieldlist)) {
unset ($fieldarray[$field]);
} // if
} // foreach
$query = "INSERT INTO $this->tablename SET ";
foreach ($fieldarray as $item => $value) {
$query .= "$item='$value', ";
} // foreach
You may have noticed that each 'name=value' pair was appended to the query string with a trailing comma as a separator, so we must remove the final comma like so:
$query = rtrim($query, ', ');
Now we can execute the query. Notice here that instead of the default error checking I look specifically for a 'duplicate key' error and return a simple error message rather terminating the whole script with a fatal error.
$result = @mysql_query($query, $dbconnect);
if (mysql_errno() <> 0) {
if (mysql_errno() == 1062) {
$this->errors[] = "A record already exists with this ID.";
} else {
trigger_error("SQL", E_USER_ERROR);
} // if
} // if
The last act is to return control to the calling script.
return;This routine will update a single record using data which is passed in as an associative array. As with the insertRecord routine this may come directly from the $_POST array. As usual we start by defining the function name and its argument(s). We also initialise the array of potential error messages.
} // insertRecord
'updateRecord' Method
function updateRecord ($fieldarray)We then connect to the database using the code described previously:
{
$this->errors = array();
global $dbconnect, $query;We then edit the input array to remove any item which does not belong in this database table:
$dbconnect = db_connect($this->dbname) or trigger_error("SQL", E_USER_ERROR);
$fieldlist = $this->fieldlist;In order to update a single record we need to extract the primary key to build a WHERE clause for our database query. At the same time we can also build our UPDATE clause. This can be done within a single loop. Notice that we are using the contents of the class variable $fieldlist to identify the primary key for the current table:
foreach ($fieldarray as $field => $fieldvalue) {
if (!array_key_exists($field, $fieldlist)) {
unset ($fieldarray[$field]);
} // if
} // foreach
$where = NULL;Each 'name=value' pair was inserted with a trailing separator which must be removed from the last entry:
$update = NULL;
foreach ($fieldarray as $item => $value) {
if (isset($fieldlist[$item]['pkey'])) {
$where .= "$item='$value' AND ";
} else {
$update .= "$item='$value', ";
} // if
} // foreach
$where = rtrim($where, ' AND ');
$update = rtrim($update, ', ');
Finally we can execute the query and return to the calling script.
$query = "UPDATE $this->tablename SET $update WHERE $where";
$result = mysql_query($query, $dbconnect) or trigger_error("SQL", E_USER_ERROR);
return;
} // updateRecord
Notice that by default it is not possible to change the primary key. Although some databases do allow it, most do not, and I have always designed my databases and associated applications accordingly.
'deleteRecord' Method
This routine will delete a single record using data which is passed in as an associative array. As a minimum this array must contain details of the record's primary key. As usual we start by defining the function name and its argument(s). We also initialise the array of potential error messages.
function deleteRecord ($fieldarray)We then connect to the database using the code described previously:
{
$this->errors = array();
global $dbconnect, $query;
$dbconnect = db_connect($this->dbname) or trigger_error("SQL", E_USER_ERROR);
We now use the contents of the class variable $fieldlist to identify the primary key for the current table so that we can construct the WHERE clause for our database query:
$fieldlist = $this->fieldlist;Each 'name=value' pair was inserted with a trailing separator which must be removed from the last entry:
$where = NULL;
foreach ($fieldarray as $item => $value) {
if (isset($fieldlist[$item]['pkey'])) {
$where .= "$item='$value' AND ";
} // if
} // foreach
$where = rtrim($where, ' AND ');Finally we can execute the query and return to the calling script.
$query = "DELETE FROM $this->tablename WHERE $where";
$result = mysql_query($query, $dbconnect) or trigger_error("SQL", E_USER_ERROR);
return;
} // deleteRecord
Tutorial Pages:
» Intended Audience
» Prerequisites
» An introduction to OO functionality within PHP
» My 'database_table' class
» Using this Class
» Standard functions
» Summary
|
