This is the first article in a series that I will use to document the process of creating a fully featured web application in CakePHP. At the moment the whole series comes in at around 8 articles but I'm sure this will grow as I write up each one. My aim is to publish one article a week detailing the steps involved with setting up and coding a full working online DVD Catalog Application that will keep track of all my DVDs. I've been meaning to create such an application for a long time so I thought it would be a great project to put on my website.
In this first article I'm going to go through the steps of setting up your new CakePHP project, go through the requirements of the system, create the database structure and finally setup the Model's and associations. For this project I'm going to be using the newest 1.2 version of CakePHP, I haven't used it before so this will be a learning process for me as well.

Brief

With any new application I'm working on I like to outline a pretty simple brief or overview of what I want the app to achieve along with some of the functionality that the app will offer. For my DVD Catalog I want an application that will manage my ever growing collection of DVDs ( films and TV shows ). For each DVD that is entered into the application I want to add an image ( preferably a cover image ), a website URL, the Imdb website entry and a personal rating.
Along with being able to add / edit / delete DVDs I would like a front end display so that other people can go online and browse my collection as well. This front end will have search and filter functionality so that I can search by name or filter by format type etc. The front end also needs to be fully customisable so I'm going to implement a templating system where I can easily change the layout in the admin panel.

Requirements

Good planning is paramount to any system so here are the requirements for the DVD Catalog Application:

• Formats
  • These are the different formats of DVD that will exist in the system.
  • A few examples of format types include dvd, dvdcopy, dvdrip, tvrip and hd.
  • Be able to add / edit / delete format types.
  • Be able to sort and filter DVDs by format type.
• Types
  • These are the different types of DVD in the system.
  • Examples include Films and TV Shows.
  • Be able to add / edit / delete types.
  • Be able to sort and filter DVDs by Type.
• Locations
  • These are the different locations where your DVDs will be stored.
  • Examples include spindle 1, spindle 2, etc.
  • Be able to add / edit / delete locations.
  • Be able to sort and filter DVDs by Location.
• DVDs
  • These are the actual DVDs that you will enter into the system.
  • Each DVD will have a number of attributes:
    • The Format Type, is it a DVD? a Copied DVD? A DVD Rip?.
    • The Type, is it a Film? Is it a TV Show?
    • The Location.
    • Name.
    • Image URL.
    • Official Website.
    • Imdb Link.
    • Rating.
    • Number of Discs.
    • Number of Episodes if its a TV Show.
• Genres
  • A DVD will be able to belong to a number of genres.
  • A Genre will have multiple DVDs.
  • An Admin will be able to add /edit / delete Genres from the system
  • A user will be able to:
    • Filter by Genre.
    • Search by Genre.
• Admin Section
  • The application will have a password protected admin area where a logged in admin will be able to interact with the system.
  • Admins can add / edit / delete:
    • Formats
    • Types
    • Locations
    • DVDs
    • Templates
• Front End
  • The application will have a front end that will be viewable to anyone.
  • The main front end will show all the DVDs in the system.
  • The number of displayed DVDs will be limited and so pagination will be required.
  • A user will be able to filter the DVDs by:
    • Format
    • Type
    • Location
    • DVD Rating
  • A user will also be able to search for DVDs.
  • A user will be able to bookmark search and filters.
  • A user will be able to select different templates, changing the appearance of the system.
• Templates
  • The system will be able to incorporate numerous templates.
  • A template will be added by the admin of the application
  • The template name will be used to identify images and css used by the template
  • A user will be able to select the template to change the design.

Although I've tried to be as thorough as possible with the requirements at this early stage a few may be added during development. Going through the requirements before building an application will give you a good idea of how the system should work and how it all fits together. It also helps with the database design process and give you an idea of what tables you will need and the types of associations that the tables have with each other.

Database Structure

From the list of requirements I've come up with 8 different tables for the database which include Formats, Types, Locations, DVDS, Genres, Templates and Admins.

• Formats
  • id, name, slug, description, created, modified, status
• Types
  • id, name, slug, description, created, modified, status
• Locations
  • id, name, slug, description, created, modified, status
• DVDs
  • id, format_id, type_id, location_id, name, slug, image, website, imdb, rating, discs, episodes, created, modified, status
• Genres
  • id, name, slug, created, modified, status
• DVDS_Genres
  • id, dvd_id, genre_id
• Templates
  • id, name, layout_name, description, image, created, modified, status
• Admins
  • id, username, password, last_login, status

First create the new database either using PhpMyAdmin or by using this statement:

CREATE DATABASE `dvdcatalog`;

Formats, Types and Locations have exactly the same database fields but I've not combined them into one table and used a flag to identify them because I wanted to seperate out the logic for each type and it also makes it easier to sort and filter by the different tables when our system is up and running. The model associations between these tables and the DVD table will also be easier to maintain and establish. A "slug" has been entered that will be used for user friendly URLs so instead of seeing "/formats/view/1" you will see "/formats/view/dvdrip".

CREATE TABLE `formats` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 255 ) NOT NULL ,
`slug` VARCHAR( 255 ) NOT NULL ,
`desc` TEXT NOT NULL ,
`created` DATETIME NOT NULL ,
`modified` DATETIME NOT NULL ,
`status` TINYINT( 1 ) NOT NULL DEFAULT '1',
PRIMARY KEY ( `id` )
);
CREATE TABLE `types` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 255 ) NOT NULL ,
`slug` VARCHAR( 255 ) NOT NULL ,
`desc` TEXT NOT NULL ,
`created` DATETIME NOT NULL ,
`modified` DATETIME NOT NULL ,
`status` TINYINT( 1 ) NOT NULL DEFAULT '1',
PRIMARY KEY ( `id` )
);
CREATE TABLE `locations` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 255 ) NOT NULL ,
`slug` VARCHAR( 255 ) NOT NULL ,
`desc` TEXT NOT NULL ,
`created` DATETIME NOT NULL ,
`modified` DATETIME NOT NULL ,
`status` TINYINT( 1 ) NOT NULL DEFAULT '1',
PRIMARY KEY ( `id` )
);

The DVD table will store all our information for each DVD and will also contain "foreign keys" that will link a DVD to a Format, Type and Location.

CREATE TABLE `dvds` (
`id` INT NOT NULL AUTO_INCREMENT ,
`format_id` INT NOT NULL ,
`type_id` INT NOT NULL ,
`location_id` INT NOT NULL ,
`name` VARCHAR( 100 ) NOT NULL ,
`slug` VARCHAR( 100 ) NOT NULL ,
`image` VARCHAR( 255 ) NOT NULL ,
`website` VARCHAR( 255 ) NOT NULL ,
`imdb` VARCHAR( 255 ) NOT NULL ,
`rating` TINYINT NOT NULL ,
`discs` TINYINT NOT NULL ,
`episodes` TINYINT NOT NULL ,
`created` DATETIME NOT NULL ,
`modified` DATETIME NOT NULL ,
`status` TINYINT( 1 ) NOT NULL DEFAULT '1',
PRIMARY KEY ( `id` )
);

The Genre table will contain the name of the Genre along with a slug for friendly URLs. A Join table will also be needed to link up Genres with DVDs, this will be displayed in much more detail in our model associations but this allows DVDs to have multiple Genres and to allow a Genre to have multiple DVDs.

CREATE TABLE `genres` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 255 ) NOT NULL ,
`slug` VARCHAR( 255 ) NOT NULL ,
`created` DATETIME NOT NULL ,
`modified` DATETIME NOT NULL ,
`status` TINYINT( 1 ) NOT NULL DEFAULT '1',
PRIMARY KEY ( `id` )
);

CREATE TABLE `dvds_genres` (
`id` INT NOT NULL AUTO_INCREMENT ,
`dvd_id` INT NOT NULL ,
`genre_id` INT NOT NULL ,
PRIMARY KEY ( `id` )
);

The templates table will store all the information relatating to the template, the layout name will be used to choose the images that the template will use along with the css file that will change the look and appearence of the front end system.

CREATE TABLE `templates` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL,
  `layout_name` varchar(100) NOT NULL,
  `description` text NOT NULL,
  `image` varchar(255) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `status` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`id`)
);

The final table (admins) will be used to store the username and password of any user that has access to the admin area of the application.

CREATE TABLE `admins` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(100) NOT NULL,
  `password` varchar(100) NOT NULL,
  `last_login` datetime NOT NULL,
  `status` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`id`)
);

Using CakePHP conventions our database table names are all plural and our join table is the plural names of the two tables in alphabetical format. This ensures that CakePHP will automatically be able to connect to the database and create associated data with Cake's inbuilt Automagic methods.

Setting up CakePHP

First thing to do is download the latest version of CakePHP at the time of writing this is currently 1.2.0.6311. I do all my development locally using Xampp and I'm going to setup a virtual host so that I can simulate an online enviornment. For information on how do this please visit my previous article on setting up a local web server and using virtual hosts.
Now I have created my virtual host called "dvdcatalog" and I've extracted CakePHP 1.2 beta to the directory. To check that everything is working goto "http://catalog" and you should be greeted with the default screen.
The first thing its telling us to do is change the value of "Security.salt" in app/config/core.php, so open up core.php and change the default value of the security.salt variable. I've used this excellent website to generate a random string.

// app/config/core.php line: 153
Configure::write('Security.salt', 'DYhG93b0qyJfIxfs2guVoUubWwvniR2G0FgaC9mi');

Whilst I have the core.php file open I've changed the security setting from high to low for development purposes so that I can stay logged in longer. Note that I need to change this back when the system is live. I've also enable admin routing, which will allow us to easily create an admin section, this will be discussed in detail later in development.

// app/config/core.php line: 149

// from
Configure::write('Security.level', 'high');
// to
Configure::write('Security.level', 'low');

// uncomment this line:69
Configure::write('Routing.admin', 'admin');

Now we need to make sure that our application can connect to the database, the default cake screen is telling us that the database configuration file is not present and that we need to rename the file located in the config folder. Open up your renamed "database.php" file and enter your database username, password and table name.

var $default = array(
    'driver' => 'mysql',
    'persistent' => false,
    'host' => 'localhost',
    'port' => '',
    'login' => 'username',
    'password' => 'password',
    'database' => 'dvdcatalog',
    'schema' => '',
    'prefix' => '',
    'encoding' => ''
);

Reload the application in your browser and if everything went well CakePHP can now connect to your database.

Creating the Model files and Associations

The next stage is to create the model files for the system and setup the associations between the tables. Before you go any further please read and re-read the Associations section of the Models chapter in the manual. This section is essential to understanding how tables relate to each other.
From our database tables we have a number of relationships:

• A DVD belongs to:
  • A Format
  • A Type
  • A Location
• A DVD has and belongs to many:
  • Genres
• A Format has many:
  • DVDs
• A Type has many:
  • DVDs
• A Location has many:
  • DVDs
• A Genre has many and belong to:
  • DVDs

With a "belongsTo" relationship we need to specifiy the foreign key in the database and so each DVD in the database has a format_id, type_id and location_id that will relate to the primary key of the coresponding table. A "hasAndBelongsToMany" is a special type of association that requires an extra "join" table, this is because a DVD an belong to many genres and a single genre can have many DVDs. This is also called a "many-to-many" relationship and the extra table keeps track of all the associations. The beauty of using CakePHP is that if your database and associations are setup correctly the finding and saving of related data will be done automatically for you, which makes the development process much easier.
Generally you have a model file for each of the tables in the database so here are the files:

/**
 * file: app/model/dvd.php
 *
 * Dvd Model
 */
class Dvd extends AppModel {
    // good practice to include the name variable
    var $name = 'Dvd';
   
    // setup the belongs to relationships
    var $belongsTo = array(
        'Format'=>array(
            'className'=>'Format'
        ),
        'Type'=>array(
            'className'=>'Type'
        ),
        'Location'=>array(
            'className'=>'Location'
        )
    );
   
    // setup the has and belongs to many relationship
    var $hasAndBelongsToMany = array(
        'Genre'=>array(
            'className'=>'Genre'
        )
    );
}

/**
 * file: app/model/format.php
 *
 * Format Model
 */
class Format extends AppModel {
    // good practice to include the name variable
    var $name = 'Format';
   
    // setup the has many relationships
    var $hasMany = array(
        'Dvd'=>array(
            'className'=>'Dvd'
        )
    );
}

/**
 * file: app/model/type.php
 *
 * Type Model
 */
class Type extends AppModel {
    // good practice to include the name variable
    var $name = 'Type';
   
    // setup the has many relationships
    var $hasMany = array(
        'Dvd'=>array(
            'className'=>'Dvd'
        )
    );
}

/**
 * file: app/model/location.php
 *
 * Location Model
 */
class Location extends AppModel {
    // good practice to include the name variable
    var $name = 'Location';
   
    // setup the has many relationships
    var $hasMany = array(
        'Dvd'=>array(
            'className'=>'Dvd'
        )
    );
}

/**
 * file: app/model/genre.php
 *
 * Genre Model
 */
class Genre extends AppModel {
    // good practice to include the name variable
    var $name = 'Genre';
   
    // setup the has and belongs to many relationship
    var $hasAndBelongsToMany = array(
        'Dvd'=>array(
            'className'=>'Dvd'
        )
    );
}

/**
 * file: app/model/template.php
 *
 * Template Model
 */
class Template extends AppModel {
    // good practice to include the name variable
    var $name = 'Template';
}

Wrapping Up

Ok so I've covered quite a lot of ground in the first article from an inital brief I've created a number of requirements for the system and from those I've created a database structure that hopefully won't change much as I create the system. I've downloaded and installed the latest version of CakePHP to a virtual host on my local development setup so when the time comes to migrating the system online it should be relatively easy. From the database tables I've created the Model files and setup the associations between them, initially these have been configured to the minimum but as the application progresses I will be covering the more advanced features.
At the moment the application doesn't do anything but this will soon change as I add new features and functionality over the upcoming weeks. What I have done is gone through the process of starting a new project and the planning and thinking that needs to go into a project before you even start coding. With a strong base of requirements and a well thought out database design your application will be easier to code & maintain and the time spent at this stage will save you time in the later stages of development.