Home Web Design Computing Music

DLib Library

The SQL abstraction provided by the library means that the loading, saving and general handling of database records is achieved through the use of simple function calls instead of assembling possibly complex SQL statements. For example, the following code will read a record for someone with a reference of BLOGGSF from whatever SQL table is associated with the Contact type. If a record is successfully found, then it will set the two parts of his name and then save the record, printing an error if the save fails:

$contact = new Contact ("BLOGGSF");

if ($contact->ok)  // Success
{
  $contact->lastname = "Bloggs";
  $contact->firstname = "Fred";

  if (!$contact->save ())
    print "Error saving";
}
else
  print "Could not find record";

As can be seen, no SQL is directly used here and the programmer only needs to know which database field attributes are available for the Contact class. The ok member variable is always available to be checked to see whether or not the previous SQL operation succeeded. Note that the save () function returns this directly so we can use it in an if statement.

Each physical database table needs to be represented by a PHP class that holds information about that table's record layout. The class also needs to provide the basic load/save/delete options. These classes are easily set up as the library provides a base class, DbRecord, that provides most of this required functionality. DbRecord is defined in dbrecord.php.

Let's look at how we set up the Contact class and record layout. First of all we'll assume that the database table contains text fields for the contact's reference id and names. It also has a large text field for some notes, a date-of-birth field and an integer field containing a value which represents the contact's 'type' (assuming we need to make a distinctions in our list of contacts - for example, we might use different values from 0 upwards representing: family, friend, acquaintance and enemy!). Here's how they are set up:

$td = addTable ("contacts", "Contact");

$td->defineFields
(
  "ref",       "VARCHAR", 20, DFA_PRI,
  "firstname", "VARCHAR", 30, DFA_NOTNULL | DFA_LIST,
  "lastname",  "VARCHAR", 30, DFA_INDEX | DFA_LIST,
  "notes",     "LONGTEXT", 0, DFA_NOTNULL,
  "dob",       "VARCHAR",  8, DFA_NOTNULL,
  "ctype",     "INTEGER",  0, DFA_NOTNULL
);

class Contact extends DbRecord {}

Firstly, we tell the system that we are adding a new table. The addTable function connects a database table called contacts (which may or may not exist at this stage) to the record class Contact that we define a few lines further down. addTable also creates a table definition structure (from the TableDef class in dbclasses.php) and returns it to be stored in $td.

$td is then used to tell the system about the fields from which the Contact record is to be made up. We call the class method defineFields with some parameters - 4 for each database field. They are the field name, type, length and attributes. The type is any valid database type and the attribute is any of the DFA_xxxx (DFA = Database Field Attribute) types which are defined in dbclasses.php. Note that if more than one attribute is required then they can be ORed together, e.g. DFA_PRI | DFA_AUTOINC. In the above, we have defined the ref field to be the primary key and put another index onto the lastname field. The DFA_LIST attribute will be explained later.

Finally we extend the DbRecord class with our Contact class.

The above shows the simplest way we can set up a class. Occasionally, however, we may need to expand upon the basic funtionality provided by DbRecord with some specific extras. Here's a slightly modified version of the Contact class:

$td = addTable ("contacts", "Contact");

$td->defineFields
(
  "ref",       "VARCHAR", 20, DFA_PRI,
  "firstname", "VARCHAR", 30, DFA_NOTNULL | DFA_LIST,
  "lastname",  "VARCHAR", 30, DFA_INDEX | DFA_LIST,
  "notes",     "LONGTEXT", 0, DFA_NOTNULL,
  "dob",       "VARCHAR",  8, DFA_NOTNULL,
  "ctype",     "INTEGER",  0, DFA_NOTNULL,
  "created",   "VARCHAR", 14, DFA_NOTNULL
);

class Contact extends DbRecord
{
  public function save () // Override the save method
  {
    global $_dt;

    if ($this->newRec)
      $this->created = $_dt->now;

    $this->setName ();
    return parent::save ();
  }

  public function coreLoad ($row) // Override the coreLoad method
  {
    parent::coreLoad ($row);
    $this->setName ();
  }

  public function setName ()
  {
    $this->name = "$this->firstname $this->lastname";
  }
}

First, we have added a created field to the record layout in order to store the date and time when the record was first created; plus we have another variable called name, which is not part of the record layout but is used to provide a convenient shortcut to the full name of the person who is represented by this record.

To set these variables we override two of the built-in class methods in DbRecord: save and coreLoad. In the save method we set created to the current date and time via $_dt->now, a global variable that the library always sets up (see datetime.php), and then we call the save method in the main DbRecord class, returning its result from our method (don't forget to add the return otherwise directly checking the result of a save will always report a failure even when it worked ok). The save method also calls a new method, setName. This combines the contact's first and last names into the new name variable separating them with a space.

The main coreLoad method in DbRecord fills in the variables representing the database fields from the data it receives in the $row parameter. First of all we call the method in DbRecord to set up the normal variables and then also calls setName.

Technical note: you may have noticed that we override coreLoad and not load. If you take a look at the source of dbrecord.php you will see that coreLoad is called by load in two places and it is also called by a function in dbautofuncs.php for reading multiple records from a table. If we had merely overridden load then the name member variable would not have been set properly when reading multiple records.

The next page gives some more examples of how we can use our new class.