Database

Introduction

The BF Database Library was created to support and manage one or more connections in a singleton pattern construction based on PDO. Additionally, the library supports a custom statement builder to keep available for developers an additional layer of database abstraction.

 

Connection

All modules in BF has a public variable ($query) inherited from the Module Library. So, by default, when you run your application, and there is any database connection information available in the config.inc.php, the __construct() method calls the Database::getInstance() to get and make your database instance available across the whole module and controllers.

So, by default, developers will have their database instance available in the variable $this->query. But, for custom applications, the developer can use the Database::getIntance() method to connect and keep database instances available all the time during the application execution time.

<?php

namespace modules\Clients;

use bossanova\Module\Module;

​class Clients extends Module
{
    public function __default ()
    {
        // Create a pgsql database instance called pgsql_instance_name
        $db = Database::getInstance("pgsql_instance_name", array("pgsql", "localhost", "db_username_pg", "db_passowrd_pg", "db_name_pg"));

        // Do something...
    }
}

Retrieve an existing database instance

The Database::getInstance() method might be used more than one time. The first time you call the method you should send a identification name for your instance and the connection information. The second time onwards the method will check if any aliases exist then return the instance, for example:

<?php

namespace modules\Clients;

use bossanova\Module\Module;

​class Clients extends Module
{
    public function __construct ()
    {
        // First time call for myconnection
        $myconn = Database::getInstance("myconnection", array("mysql", "localhost", "db_username", "db_passowrd", "db_name"));
    }

    public function test ()
    {
        // Retrieve the existing database instance myconnection
        $my = Database::getInstance("myconnection");
    }

}

Handling multiple connections

The BF Database Library handles multiple database connections, for example:

<?php

namespace modules\Clients;

use bossanova\Module\Module;

​class Clients extends Module
{
    public function __default ()
    {
        // Create and keep mysql instance available during all execution time
        $my = Database::getInstance("mysql_instance_name", array("mysql", "localhost", "db_username_my", "db_passowrd_my", "db_name_my"));

        // Create and keep pgsql instance available during all execution time
        $pg = Database::getInstance("pgsql_instance_name", array("pgsql", "localhost", "db_username_pg", "db_passowrd_pg", "db_name_pg"));

        // Get id
        $id = $this->getParam(1);

        // Insert log
        $my->table("clients_logs");
        $my->column(array('client_id'=>$id, 'client_log_date'=>NOW()));
        $my->insert();
        $my->execute();

        // Get clients
        $pg->table("clients");
        $pg->column("client_name");
        $pg->argument(1, "cliente_id", $id);
        $pg->argument(2, "client_status", 1);
        $pg->select();
        $result = $pg->execute();

        // Fetch results
        $row = $db->fetch_assoc($result);

        // Return
        return $row;
    }
}

 

Simple SQL execution

As explained above, if the connection information is available in the config.inc.php, the default behaviour is the $query to contain your database instance. For example, considering the Clients module application/Clients/Clients.class.php below:
<?php

namespace modules\Clients;

use bossanova\Module\Module;

​class Clients extends Module
{
    public function show ()
    {
        $this->query->setQuery("SELECT * FROM clients");
        $result = $this->execute();

        while ($row = $this->query->fetch_assoc($result)) {
             echo $row['client_name'];
        }
    }
}

For this example, the user will get all client names by simply calling the method
http://localhost/clients/show
 

Fetching results

There are three methods available to fetch the results from a query. The fetch_assoc() returns one record as an associative array. The fetch_row() returns one record in as a standard array. The fetch_assoc_all() returns all records as an associative array. For example:

<?php

namespace modules\Clients;

use bossanova\Module\Module;

​class Clients extends Module
{
    public function products ()
    {
        // Select data
        $this->query->table("products p");
        $this->query->argument(1, "p.product_category", "'music'");
        $this->query->select();
        $result = $this->query->execute();

        // Retrieve the data for an associative array
        while ($row = $this->query->fetch->assoc($result)) {
            // Print all columns
            print_r($row);
        }

        // Select data
        $this->query->table("products p");
        $this->query->argument(1, "p.product_category", "'music'");
        $this->query->select();
        $result = $this->query->execute();

        // Retrieve the data for an array
        while ($row = $this->query->fetch->rows($result)) {
            // Print all columns
            print_r($row);
        }
    }
}

Num rows

Get the numbers of records found from a query. Bear in mind thatm there are differences on how MySQL and PostgreSQL work, therefore the method argument differs depending on which database you are using.

<?php
namespace modules\Clients; 

use bossanova\Module\Module;

class Clients extends Module
{
    public function products ()
    {
        // PostgreSQL connection
        $pg = Database::getInstance("pgsql_instance_name", array("pgsql", "localhost", "db_username_pg", "db_passowrd_pg", "db_name_pg"));

        // Select data
        $pg->table("products p");
        $pg->argument(1, "p.product_category", "'music'");
        $pg->select();
        $result = $pg->execute();

        // Retrieve the number of rows from PostgreSQL just send the primary key from the main table
        echo $num = $pg->num_rows('p.product_id');

        // (...)

        // For mysql it is even easier
        $my = Database::getInstance("mysql_instance_name", array("mysql", "localhost", "db_username_my", "db_passowrd_my", "db_name_my"));

        // Select data
        $my->table("products p");
        $my->argument(1, "p.product_category", "'music'");
        $my->select();
        $result = $my->execute();

        // Just execute the command return the last number of rows
        echo $num = $my->num_rows();
    }
}

Last inserted ID

The insert_id() method returns the last inserted id performed by an INSERT statement. The method works slightly different from MySQL to PostgreSQL, since the latter works with sequences and, in that case, the method receives an argument that defines the sequence name.
<?php
namespace modules\Clients; 

use bossanova\Module\Module;
use bossanova\Database\Database;

class Clients extends Module
{
    public function products()
    {
        // PostgreSQL connection
        $pg = Database::getInstance("pgsql_instance_name", array("pgsql", "localhost", "db_username_pg", "db_passowrd_pg", "db_name_pg"));

        // Add a new client record on postgresql
        $pg->table("clients");
        $pg->column(array("client_name"=>"'Paul'", "client_status"=>1));
        $pg->insert();
        $pg->execute();

        // Retrieve the last id based on the postgresql sequence
        $id = $pg->insert_id("clients_client_id_seq");


        // Mysql connection
        $my = Database::getInstance("mysql_instance_name", array("mysql", "localhost", "db_username_my", "db_passowrd_my", "db_name_my"));

        // Add a new client record on mysql
        $my->table("products");
        $my->column(array("product_name"=>"'Album'", "product_status"=>1));
        $my->insert();
        $my->execute();

        // Retrieve the last id in mysql
        $id = $my->insert_id();
    }
}

 

Statement Builder

Select Statement

// Simple query with more then one argument
$result = $this->query->table("clients")
    ->column("client_name")
    ->argument(1, "client_category", 1)
    ->argument(2, "client_status", 1)
    ->select()
    ->execute();

% SELECT client_nome FROM clients WHERE (client_category = 1) AND (client_status = 1)

// If you want to use custom where logical combinations
$this->query->table("clients");
$this->query->column("client_name");
$this->query->argument(1, "client_category", 1);
$this->query->argument(2, "client_status", 1);
$this->query->argument(3, "client_status", 2);
$this->query->where("(1) AND ((2) OR (3))");
$this->query->select();
$result = $this->query->execute();

% SELECT client_nome FROM clients WHERE (client_category = 1) AND ((client_status = 1) OR (client_status = 2))

// Using different operators
$this->query->table("clients");
$this->query->column("client_name");
$this->query->argument(1, "client_category", 1, "!=");
$this->query->argument(2, "client_status", "(1,2)", "IN");
$this->query->order("client_name");
$this->query->select();
$result = $this->query->execute();

% SELECT client_nome FROM clients WHERE (client_category != 1) AND ((client_status IN (1,2)) ORDER client_name

// Using Group By
$this->query->table("clients");
$this->query->column("client_category");
$this->query->argument(1, "client_category", 1, "!=");
$this->query->group("client_category");
$this->query->select();
$result = $this->query->execute();

% SELECT client_category FROM clients WHERE (client_category != 1) GROUP client_category

// Database functions
$this->query->table("clients c");
$this->query->column("c.client_name, p.product_id, p.product_name");
$this->query->argument(1, "created_date", "BETWEEN '2015-01-01' AND '2015-02-01'", "");
$this->query->select();
$result = $this->query->execute();

% SELECT c.client_name, p.product_id, p.product_name FROM clients c
	WHERE created_date BETWEEN '2015-01-01' AND '2015-02-01'

// Querying join tables
$this->query->table("clients c");
$this->query->Innerjoin("products p", "c.client_id = p.client_id");
$this->query->column("c.client_name, p.product_id, p.product_name");
$this->query->argument(1, "p.product_id", 10);
$this->query->select();
$result = $this->query->execute();

% SELECT c.client_name, p.product_id, p.product_name FROM clients c
	INNER JOIN products p ON (c.client_id = p.client_id) WHERE (p.product_id = 10)

// Using subqueries
$this->query->table("equipment");
$this->query->column("equipment_id");
$this->query->argument(1, "status", 1);
$subquery = $this->query->select();

$this->query->table("cars");
$this->query->argument(1, "client_id", 1);
$this->query->argument(2, "equipment_id", "NOT IN ($subquery)", "");
$this->query->select();
$result = $this->query->execute();

% SELECT * FROM cars WHERE (client_id = 1) AND
	(equipment_id NOT IN (SELECT equipment_id FROM equipment WHERE status = 1))

// Union All
$this->query->table("products");
$this->query->column("product_id AS code, product_title AS title");
$this->query->argument(1, "product_status", 1);
$sql1 = $this->query->select();

$this->query->table("softdrinks");
$this->query->column("softdrink_id AS code, softdrink_title AS title");
$this->query->argument(1, "softdrink_category", "'products%'", "LIKE");
$sql2 = $this->query->select();

$this->query->table("(($sql1) UNION ALL ($sql2)) t");
$this->query->argument(1, "code", "IN(1,2,3,4,5)", "");
$result = $this->query->execute();

% SELECT ((SELECT product_id, product_title FROM products WHERE product_status = 1) UNION ALL (SELECT softdrink_id, softdrink_title FROM softdrinks WHERE softdrink_category LIKE 'products%')) t WHERE code IN (1,2,3,4,5)

Update Statement

// Simple Record Update
$data = array('client_status'=>0);

$this->query->table("clients")
    ->column($data)
    ->argument(1, "client_id", 1)
    ->update()
    ->execute();

% UPDATE clients SET client_status = 0 WHERE (client_id = 1)

// Updating records
$data = array();
$data['client_name'] = $_POST['client_name'];
$data['client_phone'] = $_POST['client_phone'];
$data['client_status'] = $_POST['client_status'];

$data = $this->query->bind($data);

$this->query->table("clients")
    ->column($data)
    ->argument(1, "client_id", 68)
    ->update()
    ->execute();

% UPDATE clients SET client_name, "'Paul Hodel'",  client_phone, "'07473725104'", client_status = 0 WHERE (client_id = 68)

Insert Statement

// Insert a new record
$data = array("client_name"=>"Jorge Alberto", "client_phone"=>"703 88748224", "client_status"=>1);

// Binding data
$data = $this->query->Bind($data);

$this->query->table("clients");
$this->query->column($data);
$this->query->insert();
$this->query->execute();

% INSERT INTO clients (client_name, client_phone, client_status) VALUES ('Jorge Alberto', '703 88748224', 1)

Delete Statement

// Simple Delete 

$this->query->table("products");
$this->query->argument(1, "product_status", 1);
$this->query->delete();
$this->query->execute();

% DELETE FROM products WHERE product_status = 1

// Logical OR Delete 

$this->query->table("products");
$this->query->argument(1, "product_status", 1);
$this->query->argument(2, "product_category", 99);
$this->query->where("(1) OR (2)");
$this->query->delete();
$this->query->execute();

% DELETE FROM products WHERE (product_status = 1) OR (product_category = 99)

Binding

The bind() method works to prepare your strings and prevent SQL injections. The method can filter a string or all strings available in an array.

<?php

namespace modules\Clients;

use Bossanova\Module\Module;

​class Clients extends Module
{
    public function __default()
    {
        // Updating records
        $data = array();
        $data['client_name'] = $_POST['client_name'];
        $data['client_phone'] = $_POST['client_phone'];
        $data['client_status'] = $_POST['client_status'];

        // Prepare strings to be added in the database
        $data = $this->query->bind($data);

        // Build SQL and peform the update
        $this->query->table("clients");
        $this->query->column($data);
        $this->query->argument(1, "client_id", 68);
        $this->query->update();
        $this->query->execute();
    }
}

Debugging

The Database Library has four different ways to debug a statement execution, such as:

Simple SQL return

It is a simple way to return the final query before any execution.

$query->table("products");
$query->argument(1, "product_status", 1);
$query->argument(2, "product_category", 99);
$query->where("(1) OR (2)");
echo $query->getSelect();

// The SQL will be printed

$query->table("products");
$query->argument(1, "product_status", 1);
$query->argument(2, "product_category", 99);
$query->where("(1) OR (2)");
echo $query->getDelete();

// The SQL will be printed

SQL execute and return

This form executes the SQL and returns the statement and the execution time.

$query->table("products");
$query->argument(1, "product_status", 1);
$query->argument(2, "product_category", 99);
$query->where("(1) OR (2)");
$query->select();
$query->execute(2); // Note the argument number 2, it means execute and print the SQL statement

// The SQL will be executed and printed, including the information about the execution time

Global print all

The setDebug() method set the Library Database to print all SQL statements including the execution time and errors of all SQL executed by the framework.

<?php

namespace modules\Clients;

use Bossanova\Module\Module;
use Bossanova\Database\Database;

​class Clients extends Module
{
    public function __construct ()
    {
        // Connect to your database and keep the instance available across your module and controllers
        $this->query = Database::getInstance("myconnection", array("mysql", "localhost", "db_username", "db_passowrd", "db_name"));

        // From this point all SQL error will trigger an automatic notification message
        $this->query->setDebug(true);
    }
}
NOTE: In debug mode, might be useful to set the property $debug = true within the library/Database.class.php line 34, so all queries can be printed to the developer.

 

Automatic error notification

The Library Database can trigger automatic debug notifications by email when any SQL statement error happens. There is two ways to set up this feature.

The first way is to set the constant DATABASE_DEBUG_EMAIL in your config.inc.php. So, by default, an email message will trigger in the event of an SQL error.

The second way is to set the debug email by the calling the method setDebugMail([string]$email), for example:

<?php

namespace modules\Clients;

use Bossanova\Module\Module;
use Bossanova\Database\Database;

​class Clients extends Module
{
    public function __construct()
    {
        // Connect to your database and keep the instance available across your module and controllers
        $this->query = Database::getInstance("myconnection", array("mysql", "localhost", "db_username", "db_passowrd", "db_name"));

        // From this point all SQL error will trigger an automatic notification message
        $this->query->setDebugMail("[email protected]");
    }
}

Transactions

The Library Database uses PDO transactions, for example:

<?php

namespace application\Clients;

use Bossanova\Module\Module;

​class Clients extends Module
{
    public function edit()
    {
        try
        {
            // Start the transaction
            $this->query->begin();

            // Update Record 
            $this->query->table("clients")
                ->column(array("client_name"=>"'John Lennon'"))
                ->argument(1, "cliente_id", $id)
                ->update()
                ->execute();

            // New record
            $this->query->table("clients")
                ->column(array("client_name"=>"'Paul'", "client_status"=>1))
                ->insert()
                ->execute();

            // Any errors?
            if ($this->query->error) {
                thrown Exception("An error has occorred" . $this->query->error);
            }

            // Commmit the changes
            $this->query->commit();
        } catch (Exception $e) {
            // In case something wrong
            $this->query->rollback();
        }
    }
}

Standalone usage

The Database Library can be used in standalone mode within any PHP project, for example:

<?php

// Minimum full standalone usage
require "vendor/bossanova/Database/Database.class.php";

// First time call, send connection information
$query = Database::getInstance("myconnection", array("mysql", "localhost", "db_username", "db_passowrd", "db_name"));

$query->table("devices");
$query->column("device_serial, device_model");
$query->argument(1, "device_id", $device_id);
$query->select();
$result = $query->execute();

if ($row = $query->fetch_assoc($result)) {
    echo $row['device_model'];
}

// Testing the database in another scope
function getClientByName ($id)
{
    // Retrieve the instance to be used in another scope, so it is not necessary to send
    // any connection information since the instance is already exists
    $query = Database::getInstance("myconnection");

    // execute the SQL 
    $query->table("clients");
    $query->column("client_name");
    $query->argument(1, "cliente_id", $id);
    $query->argument(2, "client_status", 1);
    $query->select();
    $result = $query->execute();

    // Fetch results
    $row = $query->fetch_assoc($result);

    return $row;
}