sFire PHP Framework

Database MySQLi Adapter

One of the database adapters in sFire is the sFire\DB\Adapter\MySQLi\Database adapter. This adapter let's you connect to MySQL databases and execute queries and statements. Selecting data to Array, Objects, JSON or even Entities, execute update or delete statements, calling stored procedures and execute transaction are just a few examples of this adapter.

In this section we will handle:

  • How to create a new instance
  • Retrieve database connection
  • Manually connect to database
  • Close database connection
  • Prepare queries/statements
  • Fetch data from database
  • Execute queries (for inserts, updates, deletes and more)
  • Get the amount of a executed query
  • Get the last inserted id
  • Manually escape data
  • Using transactions
  • Debugging errors and performance

Create new instance

To create a new instance you should create a new sFire\DB\Adapter\MySQLi\Database instance.

use sFire\DB\Adapter\MySQLi\Database;

//Set connect options
new Database(String $host, String $username = null, String $password = null, String $db = null, Integer $port = 3306, Boolean $connect = true, String $charset = null);

//Using an Array with connect options which represents the parameters above
new Database(Array $options);

//Give the connect method a existing Database connection
new Database(\MySQLi $connection);

The connect parameter is a Boolean. If this is set to true, than the connection will be made directly (more about the connect option in the "Manually connect to database" section).

The connection options are best saved in the module config.php file as described below.

config.php

Add this in the config.php of your module.

return [
    'database' => [
        'host'      => '127.0.0.1',
        'username'  => 'username',
        'password'  => 'password',
        'db'        => 'database',
        'charset'   => 'utf8',
        'port'      => 3306,
        'connect'   => true
    ]
];

Retrieve database connection

Because it's common that this database connection is used throughout your entire application, it is a good idea to save the connection as a service. To do so, you need to add the connection as a service in the app.php.

app.php

Add this in the app.php into the services options.

use sFire\DB\Adapter\MySQLi\Database;

//Service providers
Application :: add('services', [

    'database' => function() {

        //Get current module
        $module = Router :: getRoute() -> getModule();

        //Retrieve database options
        $config = Config :: get([$module, 'database']);

        if(null !== $config) {
            return new Database($config);
        }
    }
]);

As you can see, the current module is retrieved to return the config of this module. In the config we have set an Array with database options.

Now you can use the adapter almost anywhere in your code by calling the service method. sFire DBTables are designed to interact with adapters te retrieve ans store data in databases.

If you create a new DBTable, your constructor can set the adapter.

public function __construct() {
    $this -> setAdapter($this -> service('database'));
}

Manually connect to database

You may also connect to the database manually if preffered. You should set the connect option to Boolean false in your database connection options. Then you can use the connect method to connect to the database.

$this -> service('database') -> connect();

Close database connection

You can manually close the database connection by calling the close method.

$this -> service('database') -> close();

Prepare queries/statements

You can prepare one or multiple SQL statements with the query method. This method will not execute the query. To execute the query you can use the fetch, toArray, toJSON, toObject and execute methods which are described below.

The first parameter of this method is the SQL query in String format, while the second parameter is an Array with variables to escape which is optional.

The escape Array can be used two different ways:

  • Escape the variables with the the questionmark sign (?) and use an one dimensional Array.

  • Escape the variables with the colon sign (:) and use an two dimensional Array.

//Prepare the sql query
$this -> service('database') -> query('SELECT * FROM employee');

//Prepare the sql query and escaping variables using questionmarks
$this -> service('database') -> query('SELECT * FROM employee WHERE id = ?', [2]);

//Prepare the sql query and escaping variables using colons
$this -> service('database') -> query('SELECT * FROM employee WHERE id = :id', ['id' => 2]);

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.

Fetch data from database

To retrieve data from the database you can use the fetch method. This method returns a sFire\Adapter\ResultSet which extends the ArrayIterator class from PHP.

It accepts one optional parameter which is a type as a string.

Type Returns Default
TYPE_ARRAY Array Yes
TYPE_OBJECT STD Class No
TYPE_JSON JSON string No
Example 1, simple fetch array
$results = $this -> service('database') -> query('SELECT * FROM customer') -> fetch();

foreach($results as $result) {
    print_r($result);
}

//Output similar to:
Array
(
    [id] => 1
    [firstname] => John
    [lastname] => Smith
)
Example 2, fetch objects

sFire will convert the result set automaticly to a results set with STD Objects.

use sFire\Adapter\ResultSet;

$results = $this -> service('database') -> query('SELECT * FROM customer') -> fetch(ResultSet :: TYPE_OBJECT);

foreach($results as $result) {
    print_r($result);
}

//Output similar to:
stdClass Object
(
    [id] => 1
    [firstname] => John
    [lastname] => Smith
)
Example 3, fetch with Entity (method 1)

sFire will convert the result set automaticly to a result set with Entities that reflects the given Entity as a String.

$results = $this -> service('database') -> query('SELECT * FROM customer') -> fetch('App\\models\\entity\\EntityCustomer');

foreach($results as $result) {
    print_r($result);
}

//Output similar to:
App\models\entity\EntityCustomer Object
(
    [firstname:App\models\entity\EntityCustomer:private] => John
    [lastname:App\models\entity\EntityCustomer:private] => Smith
    [id:App\models\entity\EntityCustomer:private] => 1
)
Example 4, fetch with Entity (method 2)

sFire will convert the result set automaticly to a results set with Entities that reflects the given Entity a new Object.

use App\models\entity\EntityCustomer;

$results = $this -> service('database') -> query('SELECT * FROM customer') -> fetch(new Customer());

foreach($results as $result) {
    print_r($result);
}

//Output similar to:
App\models\entity\EntityCustomer Object
(
    [firstname:App\models\entity\EntityCustomer:private] => John
    [lastname:App\models\entity\EntityCustomer:private] => Smith
    [id:App\models\entity\EntityCustomer:private] => 1
)
Example 5, fetch multiple statements

In this example we prepare multiple SQL statements and execute them all at once. This will result in a result set with a multidimensional Array. Because of the fact that it can return data from multiple sources/tables, they can not be converted to other types.

$this -> service('database') -> query('SELECT * FROM customer');
$this -> service('database') -> query('SELECT * FROM posts');
$results = $this -> service('database') -> fetch();

print_r($results);

//Output similar to:
sFire\Adapter\ResultSet Object
(
    [entity:sFire\Adapter\ResultSet:private] => 
    [storage:ArrayIterator:private] => Array
        (
            [0] => Array
                (
                    [0] => Array
                        (
                            [id] => 1
                            [firstname] => John
                            [lastname] => Smith
                        )
                )
            [1] => Array
                (
                    [0] => Array
                        (
                            [id] => 1
                            [title] => Blog title
                            [author] => William Jones
                        )
                )
        )
)

To Array

The toArray method is a shorthand to the fetch method with "array" as first and only parameter.

$this -> service('database') -> query('SELECT * FROM employee') -> toArray();

To JSON

The toJson method is a shorthand to the fetch method with "json" as first and only parameter.

$this -> service('database') -> query('SELECT * FROM employee') -> toJSON();

To SDT Object

The toObject method is a shorthand to the fetch method with "object" as first and only parameter.

$this -> service('database') -> query('SELECT * FROM employee') -> toObject();

Pluck

The pluck method retrieves all of the values for a given key:

$this -> service('database') -> query('SELECT * FROM employee') -> toArray() -> pluck('firstname');
$this -> service('database') -> query('SELECT * FROM employee') -> toJson() -> pluck('firstname');
$this -> service('database') -> query('SELECT * FROM employee') -> toObject() -> pluck('firstname');

Execute queries (for inserts, updates, deletes and more)

To execute a statement you can use the execute method. This method returns a Boolean true for success or false for failure

$success = $this -> service('database') -> query('INSERT INTO customer (firstname, lastname) VALUES(:firstname, :lastname)', ['firstname' => 'John', 'lastname' => 'Smith') -> execute();

var_dump($success); //Output true or false

Get amount

By default the getAmount method returns the number of rows of the last statement. It will only return the number of rows of the last statement, but you can set the first and only parameter of this method to Boolean false to return the amount of each statement as an Array.

Example 1, return number of rows
$this -> service('database') -> query('SELECT * FROM customer') -> execute();
$amount = $this -> service('database') -> getAmount();

print_r($amount); //Output similar to "2"
Example 2, return number of rows of each statement
$this -> service('database') -> query('SELECT * FROM customer');
$this -> service('database') -> query('SELECT * FROM employee');
$this -> service('database') -> execute();

//Set this to false
$amount = $this -> service('database') -> getAmount(false);

print_r($amount);

//Output similar to:
Array
(
    [0] => 2
    [1] => 12
)

Get the last inserted id

When you insert a new record into the database, you can retrieve the last inserted id with the getLastId method. When executing multiple statements you can return all inserted id's by setting the first and only parameter of this method to Boolean false to return the id of each statement as an Array.

Example 1, return last inserted id
$this -> service('database') -> query('INSERT INTO customer (firstname, lastname) VALUES("John", "Smith")');
$id = $this -> service('database') -> getLastId();

print_r($id); //Output similar to "25"
Example 2, return last inserted id of each statement
$this -> service('database') -> query('INSERT INTO customer (firstname, lastname) VALUES("John", "Smith")');
$this -> service('database') -> query('INSERT INTO customer (firstname, lastname) VALUES("William", "Jones")');
$this -> service('database') -> execute();

//Set this to false
$ids = $this -> service('database') -> getLastId(false);

print_r($ids);

//Output similar to:
Array
(
    [0] => 6
    [1] => 7
)

Get the number of affected rows

When you insert, delete or update a new record into the database, you can retrieve the number of affected rows with the getAffected method. When executing multiple statements you can return the number of affected rows of all statements setting the first and only parameter of this method to Boolean false to return the affected rows of each statement as an Array.

Example 1, return the number of affected rows
$this -> service('database') -> query('INSERT INTO customer (firstname, lastname) VALUES("John", "Smith")');
$amount = $this -> service('database') -> getAffected();

print_r($amount); //Outputs "1"
Example 2, return number of affected rows of each statement
$this -> service('database') -> query('INSERT INTO customer (firstname, lastname) VALUES("John", "Smith")');
$this -> service('database') -> query('UPDATE customer SET firstname = "William" WHERE id > 3');
$this -> service('database') -> execute();

//Set this to false
$ids = $this -> service('database') -> getAffected(false);

print_r($ids);

//Output similar to:
Array
(
    [0] => 1
    [1] => 6
)

Manually escape data

sFire lets you escape your data with easy built-in function of the query method. But if you want to escape data manually, you can use the escape method. This method will accepts a String or number and will return the same variable only escaped and thus save to use for a query.

$this -> service('database') -> escape('John');

Using transactions

A transaction is a logical unit of work that contains one or more SQL statements. Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.

By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent. The change cannot be rolled back. sFire disables this feature so every sql statement needs to be committed first and can be rolled back using the rollback method.

Methods:

  • transaction start a new transaction.

  • commit commits the current transaction, making its changes permanent.

  • rollback rolls back the current transaction, canceling its changes.

Example:

Example of using a transaction to commit multiple statements and rolling back changes

$this -> service('database') -> transaction();
$this -> service('database') -> query('INSERT INTO customer (firstname, lastname) VALUES("John", "Smith")');
$this -> service('database') -> query('INSERT INTO customer (firstname, lastname) VALUES("William", "Jones")');

//Execute all statements
if(true === $this -> service('database') -> execute()) {

    //Everything went ok, so we commit the queries to make them permanent
    $this -> service('database') -> commit();
}
else {

    //Something went wrong, so we rollback the changes
    $this -> service('database') -> rollback();
}

Debugging errors and performance

sFire comes with built-in debugging tools.

Get the last error

Will return the last error as text.

$this -> service('database') -> getLastError();
Get the last error number

Will return the last error number which correspondent the MySQL error numbers.

$this -> service('database') -> getLastErrno();
Get the execution time

Will return the execution time of the last executed queries in seconds.

$this -> service('database') -> getTrace(); //Ouput similar to "0.03721380233"