Managing Your Data With DatabaseObject

Linked Tables

In this article, we are creating a DatabaseObject sub-class to manage users. These users can have their own list of links. This now poses a problem, as we require two tables using a one-to-many relationship (one user can have many links), whereas DatabaseObject is only for managing records of a single table. To get around this, we must do one of two things:

1. Create a new DatabaseObject sub-class for managing user links
2. Manually handle the user’s links within the User DatabaseObject class.

In this particular case, the ideal solution is probably to create another DatabaseObject sub-class, however, for the purposes of this example we will use the second method. It won’t always be convenient to create a separate DatabaseObject class, especially if you need to manage a table that links a many-to-many relationship.

Not to make it too confusing, but an example of this would be if instead of having a list of links, a user could belong to a team. A user may belong to many teams, and a team may have many users. In this case, you would have a DatabaseObject class for Users, one for Teams, and then you would need some manual handlers to link users to teams. These handlers may be housed either in the Users class or the Teams class… depending on what is most convenient for you.

Anyway, back to our users/links example. We need the following functions:

• Add a link
• Delete a link

We also would move the ‘load links’ functionality we created in the loadCallback() to its own method, so it can easily be called again later from other points in the code.

<?php
    class User extends DatabaseObject
    {
        /* ... other code ... */
 
        function loadLinks()
        {
            $this->links = array();
 
            if (!$this->isSavedRecord())
                return;
 
            $query = sprintf('select * from users_links where user_id = %d', $this->id);
            $result = $this->query($query);
 
            while ($row = $result->nextRow())
                $this->links[] = $row;
        }
 
        function addLink($url)
        {
            if (!$this->isSavedRecord())
                return;
 
            $query = sprintf("insert into users_links (user_id, url) values (%d, '%s')",
                             $this->id,
                             $this->escape($url));
            $this->query($query);
        }
 
        function deleteLink($url)
        {
            if (!$this->isSavedRecord())
                return;
 
            $query = sprintf("delete from users_links where user_id = %d and url = '%s'",
                             $this->id,
                             $this->escape($url));
            $this->query($query);
        }
    }
 
    $user = new User($db);
    $user->loadRecord(123);
 
    if ($user->isSavedRecord()) {
        $user->addLink('http://www.phpriot.com');
        $user->deleteLink('http://www.example.com');
    }
?>

Perhaps as an exercise, try replacing the addLink()/deleteLink() functionality with a separate DatabaseObject sub-class for links. You would call the class as follows:

<?php
    $user = new User($db);
    $user->loadRecord(123);
 
    if ($user->isSavedRecord()) {
        $link = new Link($db);
        $link->setProperty('user_id', $user->id);
        $link->setProperty('url', 'http://www.phpriot.com');
        $link->saveRecord();
 
        $link2 = new Link($db);
        if ($link2->loadRecordByUrl('http://www.example.com'))
            $link2->deleteRecord();
    }
?>

Based on what has been covered previously, you should be able to implement the Link class fairly simply based on the various functionality of the User class.

For the remainder of this article, we will continue to use the addLink() and deleteLink() methods to simplify the example as much as possible.

• Next: The Complete User Class

Additional Files

• DatabaseObject.class.php
  (22KB)