PhpRiot
Become Zend Certified

Prepare for the ZCE exam using our quizzes (web or iPad/iPhone). More info...


When you're ready get 7.5% off your exam voucher using voucher CJQNOV23 at the Zend Store

Managing Your Data With DatabaseObject

The Complete User Class

Now that we’ve covered the various functionality DatabaseObject provides, we’ll go over the entire class and actually putting it to use. This really just brings together the code created in previous pages (with some slight modifications). Additionally, we’ve removed some bits of functionality, again, just to simplify the class.

Firstly, the database schemas.

PostgreSQL database schema

Listing 16 listing-16.sql
create table users (
    user_id         serial          not null,
 
    username        varchar(255)    not null,
    password        varchar(255)    not null,
 
    first_name      varchar(255)    not null,
    last_name       varchar(255)    not null,
    email           varchar(255)    not null,
 
    active          boolean         not null,
    ts_created      timestamp       not null,
 
    primary key (user_id)
);
 
create table users_links (
    link_id         serial          not null,
    user_id         int             not null,
 
    url             varchar(255)    not null,
 
    primary key (link_id),
    foreign key (user_id) references users (user_id)
);

MySQL database schema

Listing 17 listing-17.sql
create table users (
    user_id         int             not null    auto_increment,
 
    username        varchar(255)    not null,
    password        varchar(255)    not null,
 
    first_name      varchar(255)    not null,
    last_name       varchar(255)    not null,
    email           varchar(255)    not null,
 
    active          enum('f','t')   not null,
    ts_created      timestamp       not null,
 
    primary key (user_id)
);
create table users_links (
    link_id         int             not null    auto_increment,
    user_id         int             not null,
 
    url             varchar(255)    not null,
 
    primary key (link_id),
    foreign key (user_id) references users (user_id)
);

Class User extends DatabaseObject

Listing 18 User.class.php
<?php
    require_once('DatabaseObject.class.php');
 
    class User extends DatabaseObject
    {
        function User(&$conn)
        {
            parent::DatabaseObject($conn, DBO_POSTGRESQL, 'users', 'user_id');
            $this->defineProperties('username,password,first_name,last_name,email,active,ts_created');
 
            $this->setType('active', DBO_TYPE_BOOLEAN);
            $this->setType('ts_created', DBO_TYPE_TIMESTAMP);
 
            $this->setProperty('active', true);
            $this->setProperty('ts_created', time());
        }
 
        function loadRecordByUsername($username)
        {
            $query = sprintf("select %s from %s where username = '%s'",
                             join(', ', $this->getSelectFields()),
                             $this->table,
                             $this->escape($username));
 
            return $this->_loadRecord($query);
        }
 
        function loadCallback()
        {
            $this->loadLinks();
        }
 
        function insertCallback()
        {
            $this->setProperty('ts_created', time());
            return true;
        }
 
        function deleteCallback()
        {
            $query = sprintf('delete from users_links where user_id = %d', $this->id);
            $this->query($query);
 
            return false;
        }
 
        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 = $this->nextRow($result))
                $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);
 
            // finally, rehash the links array
            $this->loadLinks();
        }
 
        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);
 
            // finally, rehash the links array
            $this->loadLinks();
        }
 
        function _checkEmail($email)
        {
            return preg_match('/^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*$/i', $email);
        }
    }
?>

So that’s the class in all its glory. You would probably want further functionality to ensure new usernames are unique also, but hopefully this article has demonstrated how easily your database data can be managed.

An example of using this class would be as follows (assuming you placed the above PHP code in a file called User.class.php):

Listing 19 listing-19.php
<?php
    require_once('User.class.php');
 
    // create a new user
    $user = new User($db);
    $user->setProperty('username', 'johndoe');
    $user->setProperty('password', 'foo');
    $user->saveRecord();
 
    $user->addLink('http://www.phpriot.com');
?>

Just a final note on the above example. Before you can add the link with the addLink() call, you must save the record. This is because the record isn’t in the database until you call saveRecord().

In This Article


Additional Files