LemonStand Wiki

Working with the database

In LemonStand you can work with database tables using two different approaches - the direct SQL queries and 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 which has been initially implemented in Ruby On Rails. It is a great tool which 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, basing on data description provided by ActiveRecord objects.

You may need to use direct SQL queries if you want to perform some simple database operation, 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 slowly 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 much 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 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 fields matching the shop_customers table fields. Returned objects are not ActiveRecord objects, and have no any methods. You can only read their fields.
• 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 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:

id	created_at	updated_at	created_user_id	updated_user_id	title	description	content
1	2010-04-01 01-10-10	NULL	1	NULL	My first post	NULL	Hello, world!

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:

id	created_at	updated_at	created_user_id	updated_user_id	title	description	content
1	2010-04-01 01-10-10	2010-04-01 01-14-10	1	1	My first post, updated	NULL	Hello, world!

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 about 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 [link]Adding a back-end user interface[/link] 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 [link]Db_ActiveRecord[/link] class in the [link]class reference section[/link].

Next: Creating data relations
Previous: Creating and updating database tables
Return to Developing LemonStand modules