FuelPHP - Models & Database

---

---

Model plays an important role in FuelPHP web framework. It represents the business entities of the application. They are either provided by customers or fetched from backend database, manipulated according to the business rules and persisted back into the database. Let us learn about models and how they interact with back-end system in this chapter.

Creating a Model

In FuelPHP, model is simply plain PHP class extending built-in Model class. By default, models may be prefixed with Model_ similar to controllers and should be placed in fuel/app/classes/model/ folder. Let us create a basic employee model and extend it as we proceed.

fuel/app/classes/model/employee.php

<?php 
   namespace Model; 

   class Model_Employee extends \Model { 
      public static function fetchAll() { 
         // Code to fetch employee from database 
      } 
   }

Access a Model

Once a model is defined, it can be freely used in any controller just by including it in the controller as follows.

use \Model\Employee; 

class Controller_Employee extends Controller { 
   public function action_index() { 
      $employees = Employee::fetchAll(); 
   } 
}

Database Overview

FuelPHP provides its own database abstraction layer to fetch data from the database. It provides both basic as well as advanced ORM based tool. The basic toolkit consists of DB, DBUtil and Query_Builer based classes. The advanced toolkit is Orm. Orm toolkit is derived from the base toolkit and bundled as a separate package.

Database Configuration

FuelPHP separates the database settings from the main configuration file and the file is fuel/app/config/db.php. It supports a separate setting for each environment. Currently, FuelPHP supports MySQL, MySQLi, and PDO drivers. The sample setting is as follows −

<?php  
   return array ( 
      'development' => array ( 
         'type'           => 'mysqli', 
         'connection'     => array ( 
            'hostname'    => 'localhost', 
            'port'        => '3306', 
            'database'    => 'tutorialspoint_fueldb', 
            'username'    => 'root', 
            'password'    => 'password', 
            'persistent'  => false, 
            'compress'    => false, 
         ), 
         
         'identifier'     => '`', 
         'table_prefix'   => '', 
         'charset'        => 'utf8', 
         'enable_cache'   => true, 
         'profiling'      => false, 
         'readonly'       => false, 
      ), 
   )

DB-based Toolkit

The DB class is the simplest option to access database from the application. It provides options to build the database query, execute it against the target database, and finally fetch the result. The DB class interacts with the following classes and provides a comprehensive database API.

• Database_Connection − Singleton and main class to interact with the database

• Database_Query − Base, concrete class to execute the SQL query and fetch result

• Database_Query_Builder − Base, abstract class to build SQL query

• Database_Query_Builder_Join − Class to build SQL joins

• Database_Query_Builder_Where − Abstract class to build SQL query conditions

• Database_Query_Builder_Select − Concrete class to build SQL select query

• Database_Query_Builder_Insert − Abstract class to build SQL insert query

• Database_Query_Builder_Update − Abstract class to build SQL update query

• Database_Query_Builder_Delete − Abstract class to build SQL delete query

The following diagram depicts the relationship between classes and the methods provided by the classes.

DB API

Let us learn the most important methods available in the DB class in this section.

instance

• Purpose − Creates and returns the new Database_Connection instance.

• Parameter −

  • $db − Database connection name defined in configuration file, optional.

• Returns − Returns the Database_Connection object

For example,

$db = DB::instance(); 
$db = DB::instance('test');

query

• Purpose − Prepare the provided SQL statement and returns the Database_Query object, which can be used to insert, update, delete, or fetch the data from the database.

• Parameter −

  • $query − SQL statement, may contain placeholders;

  • $type − SQL type, optional (DB::SELECT, DB::INSERT, DB::UPDATE and DB::DELETE)

• Returns − Returns the Database_Query object

For example,

$query = DB::query('SELECT * FROM 'employees'');

last_query

• Purpose − To get the last executed query

• Parameter − None

• Returns − Returns the last executed query

For example,

$employees = DB::Select('Select * from 'employee''); 
$sql = DB::last_query();

select

• Purpose − Generate the select part of the query

• Parameter −

  • $columns − List of the database column names

• Returns − Returns the Database_Query_Builder_Select object

For example,

$query = DB::select();              // Select *
$query = DB::select('id', 'name'); // Select id, name 

select_array (DB)

It is similar to select except we can send columns as array.

$query = DB::select_array(array('id', 'name')); // Select id, name 

insert

• Purpose − Generate the insert part of the query

• Parameter −

  • $table_name − name of the database table;

  • $columns − array of table columns

• Returns − Returns the Database_Query_Builder_Insert object

For example,

$query = DB::insert('employee');  // Insert into employee 
$query = DB::insert('employee', array('id', 'name')); // Insert into employee (id, name)

update

• Purpose − Generate the update part of the query

• Parameter −

  • $table_name − name of the database table

• Returns − Returns the Database_Query_Builder_Update object

For example,

$query = DB::update('employee'); // update `employee`

delete

• Purpose − Generate the delete part of the query

• Parameter −

  • $table_name − name of the database table

• Returns − Returns the Database_Query_Builder_Delete object

For Example

$query = DB::delete('employee');  // delete from 'employee'

Query API

Database_Query provides an option to set database connection, execute the query, and fetch the result as associative array or object. Let us see the methods provided by Database_Query class.

set_connection

• Purpose − To set the database (database connection details) against which to execute the query

• Parameter − $db - database connection name

• Returns − Returns the Database_Query object

For example,

$query = DB::query('DELETE * FROM employee', DB::DELETE); 
$query->set_connection('2nd-db');

param

• Purpose − To set the value of the parameter defined in the Query object

• Parameter −

  • $param − parameter name;

  • $value − value of the parameter

• Returns − Returns the Database_Query object

For example,

// set some variables
$table = 'employee';
$id = 1;
$name = 'Jon';

// don't use
$query = DB::query('SELECT * FROM '.$table.'. WHERE id = '.$id.' AND name = "'.$name.'"');

// but use
$query = DB::query('SELECT * FROM :tablename WHERE id = :id AND name = :name');
$query->param('tablename', 'employee');
$query->param('id', $id);
$query->param('name', $name);

Similar Methods

parameters is a similar object except it provides option to give multiple value at once.

$query->parameters (array( 
   'tablename' => $table, 
   'id' => $id, 
   'name' => $name 
}); 

bind

• Purpose − To set a variable to the parameter defined in the Query object

• Parameter −

  • $param − parameter name

  • $var − the variable to bind the parameter to

• Returns − Returns the Database_Query object

For example,

// bind a query parameter 
$table = 'employee'; 
$query = DB::query('DELETE * FROM :tablename', DB::DELETE); 
$query->bind('tablename', $table);  

// update the variable 
$table = 'employee_salary'; 

// DELETE * FROM `employee_salary`; 
$sql = $query->compile();

compile

• Purpose − To compile the query object defined into SQL query

• Parameter −

  • $db − connection string, optional

• Returns −

For example,

// assign a value to a query parameter 
$table = 'employee'; 
$query = DB::query('DELETE * FROM :tablename', DB::DELETE); 
$query->param('tablename', $table);

// compile the query, returns: DELETE * FROM employee 
$sql = $query->compile(); 

execute

• Purpose − To execute the query defined in the Query object and return the result

• Parameter −

  • $db − database connection name

• Returns − Returns the result

For example,

// assign a value to a query parameter 
$table = 'employee'; 
$query = DB::query('DELETE * FROM :tablename', DB::DELETE); 
$query->param('tablename', $table);  

// execute the query 
$query->execute();

as_assoc

• Purpose − To set return type as associative array instead of objects

• Parameter − None

• Returns − Returns the current object

For example,

$query = DB::query('SELECT * FROM employee', DB::SELECT); 
$result = $query->as_assoc()->execute(); 
foreach ($result as $row) { 
   echo $row['id']; 
}

as_object

• Purpose − To set return type as object instead of associative array

• Parameter − None

• Returns − Returns the current object

For example,

$query = DB::query('SELECT * FROM employee', DB::SELECT); 
$result = $query->as_object()->execute(); 
foreach ($result as $row) { 
   echo $row->id; 
}  

// have ORM model objects return instead 
$result = $query->as_object('Model_Employee')->execute();

Query Builder API

Query builder (Query_Builder) based classes provide options to build SQL queries dynamically. It has four classes, each one to select (Query_Builder_Select), insert (Query_Builder_Insert), update (Query_Builder_Update) and delete (Query_Builder_Delete) queries. These classes are derived from Query_Builder_Where class (option to generate conditions), which itself is derived from Query_Builder, base of all classes.

Let us look at the methods provided by Query_Builder class.

select

• Purpose − To generate the columns of select queries.

• Parameter −

  • $columns − list of columns, optional

• Returns − Returns the current instance

For example,

$query = DB::select('name')  // select `name` 
$query = DB::select(array('first_name', 'name')) // select `first_name` as `name`

from

• Purpose − To generate the table details of select queries

• Parameter −

  • $tables − list of tables

• Returns − Returns the current instance

For example,

$query = DB::select('name')->from('employee') // select `name` from `employee`

where

• Purpose − To generate the conditions of select, insert and update queries

• Parameters −

  • $column − column name or array ($column, $alias);

  • $op − logic operators, =, !=, IN, BETWEEN and LIKE, optional;

  • $value − column value

• Returns − Returns the current instance

For example,

$query = DB::select('name')->from('employee')  
$query = $query->where('name', '=', 'Jon'); 
// select `name` from `employee` where `name` = `Jon`;

Similar Methods

The similar methods are where_open(), and_where_open(), or_where_open(), where_close(), and_where_close(), or_where_close(). They are similar to where() methods except that they add extra keywords and brackets around conditions. Following is a sample code.

$query = DB::select('*')->from('employee');  
$query->where('email', 'like', '%@gmail.com'); 
$query->or_where_open(); 
$query->where('name', 'Jon'); 
$query->and_where('surname', 'Peter');
$query->or_where_close();  
// SELECT * FROM `employee` WHERE `email` LIKE "%gmail.com" OR 
   (`name` = "Jon" AND `surname` = "Peter")

join

• Purpose − To generate the table joins of select queries

• Parameters −

  • $table − table name or array($table, $alias);

  • $type − join type (LEFT, RIGHT, INNER, etc.,)

• Returns − Returns the current instance

Example

$query = DB::select('name')->from('employee')->join('employee_salary') 
// select `name` from `employee` JOIN `employee_salary`

on

• Purpose − To generate the condition of joins in select queries

• Parameters −

  • $c1 − table name or table name with alias in array;

  • $op − logical operator;

  • $c2 − table name or table name with alias in array

• Returns − Returns the current instance

For example,

$query = DB::select('name')->from('employee')->join('employee_salary') 
$query = $query->on('employee.employee_id', '=', 'employee_salary.employee_id') 
// select `name` from `employee` JOIN `employee_salary` on 
// `employee.employee_id` = `employee_salary.employee_id`

Similar Methods

The related methods are and_on() and or_on(). They are similar to on() except that they add extra keyword and brackets around joins.

group_by

• Purpose − To generate group by queries

• Parameter − $columns − Column name by which to group the result

• Returns − Returns the current instance

For example,

$query = DB::select('name')->from('employee')  
$query = $query->group_by('name'); 
// select `name` from `employee` group by `name`

having

• Purpose − To generate the group by conditions of SQL queries

• Parameter − $column − column name or array( $column, $alias ); $op − logic operators, =, !=, IN, BETWEEN and LIKE, optional; $value − column value

• Returns − Returns the current instance

Example

$query = DB::select('name')->from('employee')
$query = $query->group_by('name');
$query = $query->having('name', '!=', 'Jon');
// select `name` from `employee` group by `name` having `name` != `Jon`

Similar Methods

The similar methods are having_open(), and_having_open(), or_having_open(), having_close(), and_having_close(), or_having_close(). They are similar to having() methods except that they add extra keywords and brackets around conditions.

reset

• Purpose − To reset the query

• Parameter − None

• Returns − Returns the current instance

For example,

$query = DB::select('name')->from('employee')  
$query->reset() 
$query = DB::select('name')->from('employee_salary') 
// select `name` from `employee_salary`

DBUtil class

DBUtil class provides an option to manage and perform routine database operations. Some of the important methods are as follows −

• set_connection - Sets the default connection

DBUtil::set_connection('new_database');

• create_database - Creates a database.

DBUtil::create_database('my_database');

• drop_database - Drops a database.

DBUtil::drop_database('my_database');

• table_exists - Checks if a given table exists.

if(DBUtil::table_exists('my_table')) { 
   // Table exists 
} else { 
   // Table does NOT exist, create it! 
} 

• drop_table - Drops a table.

DBUtil::drop_table('my_table');

• create_table - Creates a table.

\DBUtil::create_table ( 
   'users', 
   array ( 
      'id' => array('type' => 'int', 'auto_increment' => true), 
      'name' => array('type' => 'text'), 
   ), 
); 

Orm Toolkit

FuelPHP provides advanced database layer using ORM concept based on the popular Active record pattern. The toolkit is included in the application but not configured by default. It is bundled as a package and the package name is orm. We can add the following configuration in the main configuration file, fuel/app/config/config.php to load the orm toolkit.

'always_load' => array ( 
   'packages' => array (
      'orm', 
   ), 
),

Creating Models

Orm provides base model class Orm\Model. We need to extend our models with the orm model to use the ORM features. Following is a sample code.

class Model_Employee extends Orm\Model {}

Configuration

Orm provides a set of settings to configure the model to use the ORM features. They are as follows −

connection − Set a static _connection property in the model to specify the connection name.

class Model_Employee extends Orm\Model { 
   protected static $_connection = "production"; 
}

table name − Set a static _table_name property in the model to specify the table name of the backend table.

class Model_Employee extends Orm\Model { 
   protected static $_table_name = 'employee'; 
} 

primary key − Set a static _primary_key property in the model to specify the primary key of the backend table.

class Model_Employee extends Orm\Model { 
   protected static $_primary_key = array('id'); 
} 

Columns − Set a static _properties property in the model to specify the columns of the backend table. It supports data_type, label, validation, form elememts, etc.

class Model_Employee extends Orm\Model { 
   protected static $_properties = array ( 
      'id',  
      'name' => array ( 
         'data_type' => 'varchar', 
         'label' => 'Employee Name', 
         'validation' => array ( 
            'required',  
            'min_length' => array(3),  
            'max_length' > array(80) 
         ), 
         
         'form' => array ( 
            'type' => 'text' 
         ), 
      ),  

      'age' => array ( 
         'data_type' => 'int', 
         'label' => 'Employee Age', 
         'validation' => array ( 
            'required',  
         ),  
         
         'form' => array ( 
            'type' => 'text' 
         ), 
      ),  
   ); 
}

Conditions − Set a static _conditions property to set the conditions and order by options.

class Model_Employee extends Orm\Model { 
   protected static $_conditions = array ( 
      'order_by' => array('id' => 'desc'), 
      'where' => array ( 
         array('is_active', > true), 
      ), 
   ); 
}

Observers − Orm provides observer based event system to add behavior to specific events. To add a behavior, first set a _observers property in the model. Then, define the behavior as a class and set it in the _observers property along with events. If no event is specified, the behavior will be invoked for all events. We can specify multiple behavior as well.

class Model_Employee { 
   protected static $_observers = array ( 
      'example',  // will call Observer_Example class for all events 
      'Orm\\Observer_CreatedOn' => array ( 
         'events' => array('before_insert'),  
         // will only call Orm\Observer_CreatedOn at before_insert event 
      ) 
   ); 
} 

Create

Once we configure the model, we can start using the methods straightaway. Orm provides a save method to save the object into the database. We can set the data using configured properties as follows −

// option 1 
$new = new Model_Employee(); 
$new->name = 'Jon'; 
$new->save();  

// option 2, use forge instead of new 
$new = Model_Employee::forge();
$new->name = 'Jon'; 
$new->save();  

// option 3, use array for properties 
$props = array('name' => 'Jon'); 
$new = Model_Employee::forge($props); 
$new>save();

Read

Orm provides a method, find to get fetch the data from the database and bind into the object. find method works depending on the input parameter. Let us look at the different options −

by primary key − Specifying the primary key returns the record by matching the primary key of the configured table.

$employee = Model_Employee::find(1);

first / last record − Specifying ‘first’ or ‘last’ will fetch the first record or the last record respectively. We can pass the order by option as well.

$entry = Model_Employee::find('first'); 
$entry = Model_Article::find('last', array('order_by' => 'id'));

All − Specifying ‘all’ will fetch all the records from the configured table. We can specify order by option as well as conditions.

$entry = Model_Employee::find('all');  
$entry = Model_Article::find ('all', array ( 
   'where' => array ( 
      array ('name', 'Jon'), 
   ), 
   'order_by' => array ('id' => 'desc'), 
));

We can use Query API of basic database toolkit along with model for advanced search option as follows.

$query = Model_Employee::query()->where('category_id', 1)->order_by('date', 'desc');
$number_of_employees = $query->count(); 
$latest_employee = $query->max('id'); 
$young_employee = $query->min('age'); 
$newest_employee = $query->get_one(); 
$employees = $query->limit(15)->get();

Update

Updating the model is the same as creating, except instead of creating a new model just fetch the model to be updated using the find method, update the property and then call the save method as follows.

$entry = Model_Employee:find(4);
$entry->name = 'Peter'; 
$entry->save();

Delete

Orm provides a delete method to delete the model. Just fetch the object and call the delete method.

$entry = Model_Employee:find(4); 
$entry->delete();

Working Example

Let's create a working example in this chapter to understand the model and database.

Create a Database

Create a new database in MySQL server, using the following command.

create database tutorialspoint_fueldb

Then, create a table inside the database using the following command.

create table employee(id int primary key, name varchar(20), age int not null);

Configure the Database

Let us configure the database using database configuration file, *fuel/app/config/db.php. Add the following changes to connect MySQL server.

<?php  
   return array ( 
      'development' => array ( 
         'type'           => 'mysqli', 
         'connection'     => array ( 
            'hostname'       => 'localhost', 
            'port'           => '3306', 
            'database'       => 'tutorialspoint_fueldb', 
            'username'       => 'root', 
            'password'       => 'pass', 
            'persistent'     => false, 
            'compress'       => false, 
         ), 
         
         'identifier'     => '`', 
         'table_prefix'   => '', 
         'charset'        => 'utf8', 
         'enable_cache'   => true, 
         'profiling'      => false, 
         'readonly'       => false, 
      ),  
      
      'production' => array ( 
         'type'           => 'mysqli', 
         'connection'     => array ( 
            'hostname'       => 'localhost', 
            'port'           => '3306', 
            'database'       => 'tutorialspoint_fueldb', 
            'username'       => 'root', 
            'password'       => 'pass', 
            'persistent'     => false, 
            'compress'       => false, 
         ), 
         
         'identifier'     => '`', 
         'table_prefix'   => '', 
         'charset'        => 'utf8', 
         'enable_cache'   => true, 
         'profiling'      => false, 
         'readonly'       => false, 
      ), 
   );

Include ORM Package

Update the main configuration file, fuel/app/config/config.php to include ORM package by adding the following configuration.

'always_load' => array ( 
   'packages' => array ( 
      'orm' 
   ), 
),

Now, ORM is enabled in your application

Create Employee Model

Create a new model, Employee under the model folder “fuel/app/classes/model”. It is defined as follows.

Employee.php

<?php  
   class Model_Employee extends Orm\Model { 
      protected static $_connection = 'production'; 
      protected static $_table_name = 'employee'; 
      protected static $_primary_key = array('id'); 
      protected static $_properties = array ( 
         'id',  
         'name' => array ( 
            'data_type' => 'varchar', 
            'label' => 'Employee Name', 
            'form' => array (
               'type' => 'text' 
            ), 
         ),  
         
         'age' => array ( 
            'data_type' => 'int', 
            'label' => 'Employee Age', 
            'form' => array ( 
               'type' => 'text' 
            ), 
         ),  
      ); 
   } 

Create Action

Create new action, action_model in Employee controller located at fuel/app/classes/controller/employee.php as follows.

class Controller_Employee extends Controller { 
   public function action_model() { 
      
      // db based sql command to delete all employees 
      $query = db::query('delete from `employee`'); 
      $query->execute('production');  
      
      // orm based query to add new employees 
      $model = new model_employee(); 
      $model->name = "john"; 
      $model->age = 25; 
      $model->save();  
      $model = new model_employee(); 
      $model->name = "peter"; 
      $model->age = 20; 
      $model->save(); 
      
      // orm based query to fetch all employee data 
      $data = array(); 
      $data['emps'] = model_employee::find('all');  
      return response::forge(view::forge('employee/model', $data)); 
   } 
} 

Create View

Now, create a view file model.php located at “fuel/app/views/employee”. Add the following changes in the file.

<ul> 
   <?php 
      foreach($emps as $emp) {  
   ?> 
   <li><?php echo $emp['name']; ?></li> 
   
   <?php 
   } 
   ?> 
</ul> 

Now, request the URL, http://localhost:8080/employee/model and it will produce the following result.

Result