Related Books

Professional Linux Programming

By Neil Matthew and Richard Stones, Brad Clements, Andrew Froggatt, David J. Goodger, Ivan Griffin, Jeff Licquia, Ronald van Loon, Harish Rawat, Udaya Ranawake, Marius Sundbakken, Deepak Thomas, Stephen J. Turnbull, David Woodhouse, Richard Stones, Christopher Browne

As Linux increases its presence throughout the world as a target platform for professional...

Managing Your Data With DatabaseObject

By Quentin Zervaas, 2 October 2005, MySQL, PHP, PostgreSQL

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

Next: Summary