Class Variables

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

Class Constructor

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

Using this Class

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();

Standard functions

These are some standard functions which I use throughout my software and which can be tailored for use in any application.

db_connect

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

 


Go to Buy @ My Store http://www.oocities.org/hsx_go2buy/