The first task is the
define the class and its variables, like so:
class Default_Table
{
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 ()
{
$this->tablename = 'default';
$this->dbname
= 'default';
$this->rows_per_page = 10;
$this->fieldlist
= array('column1', 'column2', 'column3');
$this->fieldlist['column1'] = array('pkey' => 'y');
} // constructor
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.
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)
{
$this->data_array
= array();
$pageno
= $this->pageno;
$rows_per_page = $this->rows_per_page;
$this->numrows = 0;
$this->lastpage = 0;
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.
global $dbconnect, $query;
$dbconnect =
db_connect($this->dbname) or trigger_error("SQL", E_USER_ERROR);
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.
if (empty($where)) {
$where_str = NULL;
} else {
$where_str = "WHERE
$where";
} // if
Next we want to count the number of
rows which satisfy the current selection criteria:
$query = "SELECT count(*) FROM $this->tablename
$where_str";
$result =
mysql_query($query, $dbconnect) or trigger_error("SQL", E_USER_ERROR);
$query_data =
mysql_fetch_row($result);
$this->numrows
= $query_data[0];
If there is no data we can exit at
this point.
if ($this->numrows <= 0) {
$this->pageno = 0;
return;
} // if
If 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;
} // if
Next 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;
} // if
Now we can build the query string and
run it.
$query = "SELECT * FROM $this->tablename $where_str $limit_str";
$result =
mysql_query($query, $dbconnect) or trigger_error("SQL", E_USER_ERROR);
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.
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);
return $this->data_array;
} // getData
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:
$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)
{
$this->errors
= array();
We then connect to the database using
the code described previously:
global $dbconnect, $query;
$dbconnect =
db_connect($this->dbname) or trigger_error("SQL", E_USER_ERROR);
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.
$fieldlist = $this->fieldlist;
foreach ($fieldarray as $field => $fieldvalue) {
if (!array_key_exists($field, $fieldlist)) {
unset ($fieldarray[$field]);
} // if
} // foreach
We can now construct the query string
to insert a new record into the database:
$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;
} // insertRecord
'updateRecord' Method
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.
function
updateRecord ($fieldarray)
{
$this->errors
= array();
We then connect to the database using
the code described previously:
global $dbconnect, $query;
$dbconnect =
db_connect($this->dbname) or trigger_error("SQL", E_USER_ERROR);
We then edit the input array to remove
any item which does not belong in this database table:
$fieldlist = $this->fieldlist;
foreach ($fieldarray as $field => $fieldvalue) {
if (!array_key_exists($field, $fieldlist)) {
unset ($fieldarray[$field]);
} // if
} // foreach
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:
$where = NULL;
$update = NULL;
foreach ($fieldarray as $item => $value) {
if (isset($fieldlist[$item]['pkey'])) {
$where .= "$item='$value' AND ";
} else {
$update .= "$item='$value', ";
} // if
} // foreach
Each 'name=value' pair was inserted
with a trailing separator which must be removed from the last entry:
$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)
{
$this->errors
= array();
We then connect to the database using
the code described previously:
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;
$where = NULL;
foreach ($fieldarray as $item => $value) {
if (isset($fieldlist[$item]['pkey'])) {
$where .= "$item='$value' AND
";
} // if
} // foreach
Each 'name=value' pair was inserted
with a trailing separator which must be removed from the last entry:
$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
So much for defining the class with
its properties and methods, but how do you go about using it in your PHP
scripts?
The first step is to create a subclass
for each physical database table which extends
this base class. This must contain its own class
constructor specifically tailored to reflect the details of the database
table in question. This is done using code similar to the following:
require_once 'default_table.class.inc';
class Sample extends Default_Table
{
// additional class variables go here
function
Sample ()
{
$this->tablename = 'sample';
$this->dbname
= 'foobar';
$this->rows_per_page = 15;
et cetera ...
} // end class constructor
} // end class
Having created a subclass you are then
able to include the class definition in any script and create
one or more objects from this class. You are then able to start using the
class to communicate with your database, as shown in the following code
snippets:
include 'sample.class.inc';
$dbobject = new Sample;
// if $where is null then all rows
will be retrieved
$where = "column='value'";
// user may specify a particular
page to be displayed
if (isset($_GET['pageno']) {
$dbobject->setPageno($_GET['pageno']);
} // if
$data = $dbobject->getData($where);
$errors = $dbobject->getErrors();
if (!empty($errors)) {
// deal with error message(s)
} // if
All data retrieved will now be
available as a standard associative array in $data. The following
values may also be retrieved if required:
·
$dbobject->numrows will return the total number of rows which satisfied the
selection criteria.
·
$dbobject->pageno will return the current page number based on $rows_per_page.
·
$dbobject->lastpage will return the last page number based on $rows_per_page.
In the following code snippets $fieldarray may be the $_POST array, or it may be constructed within your PHP script.
$fieldarray = $dbobject->insertRecord($fieldarray);
$errors = $dbobject->getErrors();
$fieldarray = $dbobject->updateRecord($fieldarray);
$errors = $dbobject->getErrors();
$fieldarray = $dbobject->deleteRecord($fieldarray);
$errors = $dbobject->getErrors();
These are some standard functions
which I use throughout my software and which can be tailored for use in any
application.
This is the contents of my 'db.inc'
file which I include in every script. As well as opening a connection to your
MySQL server it will select the desired database.
$dbconnect = NULL;
$dbhost
= "localhost";
$dbusername = "****";
$dbuserpass = "****";
$query = NULL;
function db_connect($dbname)
{
global $dbconnect, $dbhost, $dbusername, $dbuserpass;
if (!$dbconnect) $dbconnect =
mysql_connect($dbhost, $dbusername, $dbuserpass);
if (!$dbconnect) {
return 0;
} elseif (!mysql_select_db($dbname)) {
return 0;
} else {
return $dbconnect;
} // if
} // db_connect