sFire PHP Framework
A DBtable is the connection between you business layer (Controller) and data layer (Model / DBTable) and is used to reduce the complexity we find when binding the model and communicating with entities. The Mapper is used to format data retrieved from the data layer before giving it back to the business layer.
Below is the blueprint / example of how to create a new DBTable instance. The constructor will be used to set the default adapter and to set the default database table. The database table is used in different methods like delete, insert and update. It is also used to retrieve entities from that type.
<?php
namespace App\models\dbtable;
use sFire\Adapter\MySQL\TableGateway;
class DBTableCustomer extends TableGateway {
public function __construct() {
$this -> setAdapter($this -> service('database'));
$this -> setTable('customer');
}
}
?>
The data that will be retrieved from the database, will be retrieved by a Mapper. Below is an example of how to do so.
<?php
namespace App\models\mapper;
use sFire\MVC\Mapper;
class MapperCustomer extends Mapper {
public function __construct() {
$this -> setDBTable('customer');
}
public function getCustomerById($id) {
return $this -> dbtable() -> getCustomerById($id) -> current();
}
}
?>
Note: Our application name is "App". Therefore we use the "App\models\mapper" namespace.
The select method accepts three parameters with the last two being optional. The method will return a sFire\Adapter\ResultSet which extends the ArrayIterator of PHP.
$this -> select(String $select, Array $params, Boolean $type);
//Simple select statement
$this -> select('SELECT * FROM customer WHERE id = 25');
//Select statement while escaping variables using the questionmark sign
$this -> select('SELECT * FROM customer WHERE id = ?', [25]);
//Select statement while escaping variables using the colon sign
$this -> select('SELECT * FROM customer WHERE id = :id', ['id' => 25]);
Note: You can not use the two methods for escaping data simultaneously. Note: The colon method is best used if you have many variables to escape or you have to use the same variables so you can call them by key name.
By default, this method will try to convert the result set into entities. The entity that will be used is the on you set with the setTable method. For example, you set the table name to "customer" then "modules/{YOUR-MODULE-NAME}/models/entity/EntityCustomer.php" will be loaded. By default, the name of an entity begins with "Entity" (you may change this configuration in the app.php.
if you don't want to convert the resultset to entities, you can set the third parameter. This parameter lets you convert the data to a specific type shown in the table below.
| Type | value | Returns |
|---|---|---|
| TYPE_ARRAY | array | Array |
| TYPE_OBJECT | object | STD Class |
| TYPE_JSON | json | JSON string |
use sFire\Adapter\ResultSet;
//Select as Array
$this -> select('SELECT * FROM customer', null, ResultSet :: TYPE_ARRAY);
//Select as JSON
$this -> select('SELECT * FROM customer', null, ResultSet :: TYPE_JSON);
The update method accepts five parameters with the last four being optional. The method will return a Boolean true for success or false on failure.
$this -> update(Array $fields, String $where, Array $params, Integer $limit, String $orderby);
//Update the lastname for every row
$this -> update(['lastname' => 'Smith']);
//Adding a where condition
$this -> update(['lastname' => 'Smith'], 'id = 25');
//Adding a where condition while escaping variables using the questionmark sign
$this -> update(['lastname' => 'Smith'], 'id = ?', [25]);
//Adding a where condition while escaping variables using the colon sign
$this -> update(['lastname' => 'Smith'], 'id = :id', ['id' => 25]);
//Adding a limit to the update statement
$this -> update(['lastname' => 'Smith'], null, null, 5);
//Adding a order by condition
$this -> update(['lastname' => 'Smith'], null, null, 5, 'firstname DESC');
The insert method accepts one parameter and one optional parameter. The method will return a Boolean true for success or false on failure. The first parameter is an Array with fields to update while the second optional parameter is a Boolean to convert the insert statement to an "insert ignore" statement.
$this -> update(Array $fields, Boolean $ignore);
//Insert a new row
$this -> insert(['firstname' => 'John', 'lastname' => 'Smith']);
//Insert multiple rows
$this -> insert([['firstname' => 'John', 'lastname' => 'Smith'], ['firstname' => 'William', 'lastname' => 'Jones']]);
//Insert ignore for suppressing duplicate keys
$this -> insert(['firstname' => 'John', 'lastname' => 'Smith'], true);
The delete method accepts four optional parameters. The method will return a Boolean true for success or false on failure.
$this -> delete(String $where, Array $params, Integer $limit, String $order);
//Deletes all rows
$this -> delete();
//Adding a where condition
$this -> delete('id > 10');
//Adding a where condition while escaping variables using the questionmark sign
$this -> delete('id > ?', [10]);
//Adding a where condition while escaping variables using the colon sign
$this -> delete('id > :amount', ['amount' => 10]);
//Adding a limit to the delete statement
$this -> delete(null, null, 5);
//Adding a order by condition
$this -> delete(null, null, 5, 'firstname DESC');
The rows method accepts three parameters which are all optional. The method will return a number or a Boolean false on failure.
$this -> rows(String $column, String $where, Array $params);
//Select the amount of rows
$amount = $this -> rows();
//Select the column to use for counting
$amount = $this -> rows('id');
//Adding a where condition
$amount = $this -> rows('id', 'DATE(date_of_birth) = DATE(NOW())');
//Adding a where condition while escaping variables using the questionmark sign
$amount = $this -> rows('id', 'role = ?', ['administrator']);
//Adding a where condition while escaping variables using the colon sign
$amount = $this -> rows('id', 'role = :role', ['role' => 'administrator']);
The call method accepts one parameter and one optional parameter. The method will return a Boolean true for success or false on failure.
$this -> call(String $name, Array $params);
//Call stored procedure function by it's name
$this -> call('foo');
//Adding variables
$this -> call('foo', ['John', '25']);
To retrieve the last inserted id you can use the getLastId method. This method will return the last inserted id (usually a number).
//Insert a new row
$this -> insert(['firstname' => 'John', 'lastname' => 'Smith']);
$id = $this -> getLastId();
echo $id; //Output similar to "52"
If you want to execute a custom SQL query, you can use the getAdapter method. This will return the default adapter set with the setAdapter method in the constructor. In the example below, you see how to execute a query with the MySQLi adapter.
$this -> getAdapter() -> query('SELECT * FROM sales LEFT JOIN customer ON sales.customer_id = customer.id');
For more information about executing custom queries, you should see all the different adapters.