Model plays an important role in FuelPHP web framework. It represents the business entities of the application of FuelPHP – Models & Database. 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 Models & Database 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' => 'Adglob_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 of FuelPHP – Models & Database.
- 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 of FuelPHP – Models & Database.
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. FuelPHP – Models & Database 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 Models & Database 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 adglob_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' => 'adglob_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' => 'Adglob_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
Next Topic : Click Here
Pingback: FuelPHP - Presenters | Adglob Infosystem Pvt Ltd