DLib Library

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

  "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:

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