Working with the Database

In LemonStand, you can work with database tables using two different approaches - direct SQL queries or using model PHP classes. Model classes are PHP classes which extend the ActiveRecord base class (Db_ActiveRecord).

How to choose between direct SQL queries and ActiveRecord

ActiveRecord is a powerful approach to accessing data and was initially implemented in Ruby On Rails. It is a great tool that simplifies many common tasks like creating, updating and deleting database records. LemonStand customer, order, product and many other business objects are implemented using the ActiveRecord approach. You should use ActiveRecord for all database objects supplied by your module. It is especially helpful if you are going to build a user interface in the Administration Area, because LemonStand can create HTML lists and forms automatically, based on data description provided by ActiveRecord objects.

You may need to use direct SQL queries if you want to perform some simple database operations, or if you do no want to use the ActiveRecord approach due to performance considerations, or if it is not possible to accomplish some database manipulation using ActiveRecord, for example when you need to update a database table structure. ActiveRecord objects work slower than direct queries and they use more memory. If you need to fetch names of all countries from the database in order to display them in a drop-down list, it is better to use direct queries, because in this case the power of ActiveRecord is not needed, but loading hundreds of country objects would waste a lot of memory and CPU time. On the other hand, if you want to update a title of a blog post, it is better to use the ActiveRecord approach, because it will require much simpler PHP code. Also the ActiveRecord engine will automatically update some service table fields like the Updated At and Updated By.

Executing direct SQL queries

You can send direct queries to the database using the Db_DbHelper class. This class has methods for fetching and updating data in the database.

• Db_DbHelper::scalar($sql) - fetches and returns a scalar value from the database. Example:

  $customer_count = Db_DbHelper::scalar('select count(*) from shop_customers'); 

• Db_DbHelper::query($sql) - executes a database query. Example:

  Db_DbHelper::query('update abcblog_posts set title="Some title" where id=2');

• Db_DbHelper::queryArray($sql) - returns a list of database records, representing each record as an array. Example:

  $customers = Db_DbHelper::queryArray('select * from shop_customers');

  This query would return an array. Each element in the array would be another array with indexes matching the shop_customers table fields.
• Db_DbHelper::objectArray($sql) - returns a list of database records, representing each record as a PHP object. Example:

  $customers = Db_DbHelper::objectArray('select * from shop_customers');

  This query would return an array. Each element in the array would be an object with properties matching the shop_customers table fields. Returned objects are not ActiveRecord objects and have no methods. You can only read their properties.
• Db_DbHelper::object($sql) - returns a PHP object with fields matching the query result fields. Example:

  $customer = Db_DbHelper::object('select * from shop_customers where id=1');

  This code would return a PHP object (not the ActiveRecord instance) with fields matching the shop_customers table fields.

Using parameters in queries

You will often need to pass parameters to SQL queries. All methods of the Db_DbHelper class accept a second parameter which you can use for setting values for parameters specified in the query string. To specify a parameter use the colon character before a parameter name. For example:

$customer_email = Db_DbHelper::scalar('select email from shop_customers where id=:id', array('id'=>2)); 

or

Db_DbHelper::query('update abcblog_posts set title=:title_param where id=:id', array('title_param'=>$title, 'id'=>32));

ActiveRecord

PHP Road (a core framework of LemonStand) has its own implementation of the Active Record pattern, defined in the Db_ActiveRecord class. All PHP classes for working with database tables should extend the Db_ActiveRecord class. Such classes are called models. There should be one model class for each database table. For example, if you are building a blog module, you would probably have models for blog posts and comments.

We will demonstrate the ActiveRecord concept using the blog module example, which we started in the Developing a simple module article. If you remember, we created the abcblog_posts table in the database for storing blog posts. Now we will create a model object for managing the table content. First we need to create a file for the model class. According to the naming conventions described in the Developing LemonStand modules article, a suitable name for the model class is AbcBlog_Post. And a corresponding file name is abcblog_post.php. Model class scripts in LemonStand modules should be placed to the models directory, which we should create now. After creating the models directory and the abcblog_post.php script, our module has the following file structure:

To define the blog post model class, we should extend the Db_ActiveRecord class and specify a corresponding database table name in the $table_name public field:

<?
  class AbcBlog_Post extends Db_ActiveRecord
  {
    public $table_name = 'abcblog_posts';
  }
?>

Although the class declaration is very simple, the class can now perform all operations on the abcblog_posts table, because it extends the Db_ActiveRecord class. For example, we can create a new blog post record using a very simple code snippet:

$obj = new AbcBlog_Post();
$obj->title = 'My first post';
$obj->content = 'Hello, world!';
$obj->save();

If you execute this code and look to the database you will see that the posts table now contains a single record:

The created_at, updated_at, created_user_id and updated_user_id are service fields which LemonStand can update automatically. The created_at and created_user_id fields contain a time when the record was created and an identifier of a LemonStand user which created the record. The updated_at and updated_user_id fields contain a similar record update information. LemonStand updates these fields each time when you save a record to the database. Time values are usually stored in GMT, for possible future compatibility with multiple user locations. LemonStand can automatically convert time values to a time zone, specified in the config.php file, before displaying them.

Finding and updating records using with ActiveRecord is a simple task as well:

$obj = new AbcBlog_Post();
$obj = $obj->find(1);
$obj->title = 'My first post, updated';
$obj->save();

This code creates an object of the AbcBlog_Post clas and calls its find() method. This method can accept a single parameter - an identifier of a record which you want to find. After finding the record, the code updates its title field and saves the record to the database. The updated database record:

The find() method can return NULL value in case if it cannot find a database record with a specified identifier. You should check a value returned by the method before you try to access its properties or call its methods. The good thing is that LemonStand can take care of all standard data management operations if you are building a user interface for the Administration Area. You can display a list of blog posts and create forms for creating and updating blog posts with a few lines of code. Please read the Adding a back-end user interface article for details.

Code example for deleting a record:

$obj = new AbcBlog_Post();
$obj = $obj->find(1);
$obj->delete();

You can also find more details about the Db_ActiveRecord class in the class reference section.

Next: Creating and Updating Database Tables
Previous: Understanding LemonStand Modules
Return to Extending LemonStand