Database Basics

In PHPR you have two different approaches to working with databases:

Both approaches have their merit for a given situation. Generally speaking, direct SQL will perform faster and should be used for simple tasks or tasks that cannot be achieved with the Active Record approach, such as creating database tables.

Active Record is an approach that is used by the popular framework, Ruby On Rails. It allows an easy interface for performing repetitive tasks like creating, reading, updating and deleting database records. You can learn more about the Active record pattern on Wikipedia.

Direct SQL

You can manipulate your database by sending direct queries through the helper class, Db_Helper. This class has various methods for communicating to the database. A most common example is the scalar() method, which returns a single value:

// Returns the number of records in the user table
$user_count = Db_DbHelper::scalar('select count(*) from users');

You can find more methods for direct SQL by reading the database_helper article.

Active Record

All model classes used for accessing the database should extend the Db_ActiveRecord class, this enables the usage of the active record pattern. You should create one model class for each database table. For example if you were building a blog module, you might use models for the blog posts and the user comments.

Before we begin you should have already created a module or are working on an existing module. If you haven't created a module you can find instructions for this in the Creating a Module article.

Decide on a suitable model class, such as BossBlog_Post, and create a corresponding file name which would be bossblog_post.php. This file should be placed inside the models directory. Inside the file we will define the model class and the table name used:

<?php
class BossBlog_Post extends Db_ActiveRecord
{
	public $table_name = 'bossblog_posts';
}

Even though this code is very simple, we can now access the database table using all the methods and properties found in the Db_ActiveRecord class. For example, to add a new blog post entry:

$post = BossBlog_Post::create();
$post->title = 'Hello World!';
$post->content = 'I am writing to you from my website...';
$post->save();

After you execute this code you should see a record in your database with the specified fields populated.

Finding and updating records is just as simple:

$post = BossBlog_Post::create()->find(1);
$post->title = 'Hi there, World!';
$post->save();

Here is a code example for deleting a record:

$post = BossBlog_Post::create()->find(1);
$post->delete();

You can find more details about accessing models via the Db_ActiveRecord class located in the Class Reference section.

There are also ways you can define how fields should be displayed and validated by reading the column_definitions and Form Field Definitions articles.