DLib Library

Tables with multi-field primary keys

Let's create another table. Maybe our Contacts have address records and, for some reason, they are allowed to have more than one address each. We will therefore define a new class called Address:

$td = addTable ("addresses", "Address");

$td->defineFields
(
  "ref",       "VARCHAR", 20, DFA_PRI,
  "addrnum",   "INTEGER",  0, DFA_PRI,
  "addr1",     "VARCHAR", 40, DFA_NOTNULL,
  "addr2",     "VARCHAR", 40, DFA_NOTNULL,
  "addr3",     "VARCHAR", 40, DFA_NOTNULL,
  "town",      "VARCHAR", 40, DFA_NOTNULL,
  "county",    "VARCHAR", 40, DFA_NOTNULL,
  "postcode",  "VARCHAR", 12, DFA_NOTNULL,
  "country",   "VARCHAR", 40, DFA_NOTNULL
);

class Address extends DbRecord {}

Here you can see that we've defined both ref and addrnum (the address number) as primary key fields.

This piece of code will return the record (if it exists) for the third address of BLOGGSF:

$addr = new Address ("BLOGGSF", 3);

if ($addr->ok)
  print "Found his address!";

The code following shows how we can retrieve all of the addresses for a single contact (assumed to have already been retrieved into the $contact variable) and display them. Note the use of the formatAddress function (in functions.php) which takes the record, extracts the address information and formats it appropriately (note: this will only work if the field names used are identical to those shown above):

list ($ok, $adList) = Address::getRecords ("ref = '$contact->ref'", "addrnum");

if ($ok)
  foreach ($adList as $addr)
	print "$addr->addrnum: " . formatAddress ($addr);

Static Methods

There are several static methods available which provide some useful functionality. The format of these is always ClassName::method.

For our Contact class we gain:

• Contact::getRecords ($where, $order, $limit) - this returns an array of all records that match the supplied WHERE statement (the function also takes optional ORDER and LIMIT parameters).
• Contact::getList ($where, $order, $limit, $flds) - this is similar to the getRecords method. By default this will return primary key fields plus any other field that has had the DFA_LIST attribute added - this can be overridden by specifying the fields directly as a CSV in the $flds parameter. This method should be used where a large number of records are to be retrieved but only a limited number of the record's available fields are required (e.g. for our contacts we might want to produce just a listing of the contacts names). Using this method instead of getRecords can also reduce the possibility of an out of memory error when very large numbers of records are retrieved and PHP's memory limit has been set quite small.
• Contact::count ($where) - returns the number of records that match the supplied WHERE statement.
• Contact::distinct ($field, $where) - returns an array of the distinct values from the specified field (the function also takes an optional WHERE statement).
• Contact::deleteRecords ($where) - deletes the records that match the specified WHERE statement.
• Contact::updateField ($field, $from, $to, $where) - updates the specified field from the FROM value to the TO value (optionally limited by a WHERE statement).

The Address record also has the same methods. Following is an example of using the listContacts function. It also uses the br () function call in html.php to separate each line with a couple of BR tags:

list ($ok, $contactList) = Contact::getList ("lastname LIKE 'Smi%'", "lastname");

if ($ok)
  foreach ($contactList as $contact)
    print br (2) . $contact->name;

The $where parameter is sent a piece of SQL code (and is one of the few places anything like SQL will need to be used in your code). The function returns an array of two items:

1. a Boolean result which, if true, indicates that something was found (if false, then either an error occurred or nothing matched the search criteria);
2. an array containing a list of all records that matched the WHERE statement (if $ok is false then this array will be empty).

The list command is used to split up the initial array into its two components. In the above code we then look for a valid return by checking the state of $ok and, by using foreach, iterate over the returned results records printing out the name value for each record found.

Note that if your WHERE statement returns a large number of records and causes an out of memory error (even if you are using DFA_LIST and the associated list autofunction then you will also need to use the $limit parameter to restrict the number of records returned (this is the same LIMIT or START,LIMIT combination that is used in a MySQL SELECT statement or the more restricted TOP statement used by Microsoft database engines).

Table Creation

If the software is installed on a new system then there is no need to manually create the individual database tables (if you are using MySQL and you know the MySQL root user name and password then the system can create the database for you as well). When the library starts up it does a few checks to see if it can read certain records from the always present systemval table (see system.php) - if this check fails then it assumes it is a new installation and attempts to create all of the database tables.

Once a day or whenever the doSQL function (the main library function that sends SQL to the database engine - see database.php) detects a problem with a table, the system checks for updated fields. If it detects that a field has been added, dropped or renamed then it will attempt to rectify the problem automatically. The code that handles this is updateTables.

You may occasionally need to create a table 'manually' yourself. To do this just call the class method createTable as follows:

Contact::createTable ();