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' => 'howcodex_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
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
For example,
$employees = DB::Select('Select * from 'employee'');
$sql = DB::last_query();
select
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
For example,
$query = DB::insert('employee'); // Insert into employee
$query = DB::insert('employee', array('id', 'name')); // Insert into employee (id, name)
update
For example,
$query = DB::update('employee'); // update `employee`
delete
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
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
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
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
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
For example,
$query = DB::query('SELECT * FROM employee', DB::SELECT);
$result = $query->as_assoc()->execute();
foreach ($result as $row) {
echo $row['id'];
}
as_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
For example,
$query = DB::select('name') // select `name`
$query = DB::select(array('first_name', 'name')) // select `first_name` as `name`
from
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
Example
$query = DB::select('name')->from('employee')->join('employee_salary')
// select `name` from `employee` JOIN `employee_salary`
on
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
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 howcodex_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' => 'howcodex_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' => 'howcodex_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
