Database Access

Database Access is made available through the use of the Record component, which utilizes PHP's PDO data-access library. This section provides an overview of the Record component and its common uses.

Table of Contents

Data Management

Select

This section describes common select statements. Follow each example with ->fetch() to retrieve 1 record, or ->fetchAll() to retrieve all records, or follow with ->get() to retrieve the SQL for that statement. You may also echo $this->Record to print a query without resetting the query.

All

// Produces SELECT * FROM `clients`
$this->Record->select()->from("clients");

Tuples

// Produces SELECT `id`,`id_code`,`user_id` FROM `clients`
$this->Record->select(array("id","id_code","user_id"))->from("clients");

Tuple Aliasing

// Produces SELECT `id`,`id_code` AS `key`,`user_id` FROM `clients`
$this->Record->select(array("id","id_code"=>"key","user_id"))->from("clients");

Limit

// Produces SELECT * FROM `clients` LIMIT 20, 10
$this->Record->select()->from("clients")->limit(10, 20);

Order By

// Produces SELECT * FROM `clients` ORDER BY `clients`.`user_id` ASC
$this->Record->select()->from("clients")->order(array("clients.user_id"=>"asc"));

Where

// Produces SELECT * FROM `clients` WHERE `clients`.`id`='15'
$this->Record->select()->from("clients")->where("clients.id", "=", 15);

And Where

// Produces SELECT * FROM `clients` WHERE `user_id`='15' AND `id_code`='USR15'
$this->Record->select()->from("clients")->where("user_id", "=", 15)->where("id_code", "=", "USR15");

Or Where

// Produces SELECT * FROM `clients` WHERE `user_id`='15' OR `id_code`='USR15'
$this->Record->select()->from("clients")->where("user_id", "=", 15)->orWhere("id_code", "=", "USR15");

Where In

// Produces SELECT * FROM `clients` WHERE `id` IN ('1','2','3','4')
$this->Record->select()->from("clients")->where("id", "in", array(1,2,3,4));

Like

// Produces SELECT * FROM `clients` WHERE `id_code` LIKE 'USR15'
$this->Record->select()->from("clients")->like("id_code", "USR15");

// Produces SELECT * FROM `clients` WHERE `id_code` LIKE '%USR15%'
$this->Record->select()->from("clients")->like("id_code", "%USR15%");

Or Like

// Produces SELECT * FROM `clients` WHERE `user_id`='15' OR `id_code` LIKE '%USR15%'
$this->Record->select()->from("clients")->where("user_id", "=", 15)->orLike("id_code", "%USR15%");

Group By

// Produces SELECT * FROM `clients` WHERE `id_code` LIKE '%USR15%' GROUP BY `clients`.`user_id`, `clients`.`id_code`
$this->Record->select()->from("clients")->like("id_code", "%USR15%")->group(array("clients.user_id", "clients.id_code"));

Having

// Produces SELECT * FROM `clients` HAVING `primary_account_type` IS NOT NULL
$this->Record->select()->from("clients")->having("primary_account_type", "!=", null);

// Produces SELECT * FROM `clients` HAVING `primary_account_type` IS NOT NULL OR `id`>'0'
$this->Record->select()->from("clients")->having("primary_account_type", "!=", null)->orHaving("id", ">", 0);

Aggregate Functions

// Produces SELECT MAX(`id`) AS `largest_id` FROM `clients`
$this->Record->select(array("MAX(id)"=>"largest_id"))->from("clients");

Grouping Conditions

// Produces SELECT * FROM `clients` WHERE ((`id_code` LIKE '%USR15%' OR `id_code` LIKE '%USR18%') AND `name` LIKE 'Firstname%')
$this->Record->select()->from("clients")->
    open()->
        open()->
            like("id_code", "%USR15%")->
            orLike("id_code", "%USR18%")->
        close()->
        like("name", "Firstname%")->
    close();

Joins

Inner Join

// Produces SELECT `clients`.* FROM `clients` INNER JOIN `contacts` ON `contacts`.`user_id`=`clients`.`user_id`
$this->Record->select(array("clients.*"))->from("clients")->innerJoin("contacts", "contacts.user_id", "=", "clients.user_id", false);

Left Join

// Produces SELECT `clients`.* FROM `clients` LEFT JOIN `contacts` ON `contacts`.`user_id`=`clients`.`user_id`
$this->Record->select(array("clients.*"))->from("clients")->leftJoin("contacts", "contacts.user_id", "=", "clients.user_id", false);

Right Join

// Produces SELECT `clients`.* FROM `clients` right JOIN `contacts` ON `contacts`.`user_id`=`clients`.`user_id`
$this->Record->select(array("clients.*"))->from("clients")->rightJoin("contacts", "contacts.user_id", "=", "clients.user_id", false);

Cross Join

// Produces SELECT `clients`.* FROM `clients`, `contacts`
$this->Record->select(array("clients.*"))->from("clients")->from("contacts");

Multiple conditions

// Produces SELECT `clients`.* FROM `clients` LEFT JOIN `contacts` ON `contacts`.`user_id`=`clients`.`user_id` AND `contacts`.`id`!='0'
$this->Record->select(array("clients.*"))->from("clients")->on("contacts.id", "!=", 0)->leftJoin("contacts", "contacts.user_id", "=", "clients.user_id", false);

// Produces SELECT `clients`.* FROM `clients` LEFT JOIN `contacts` ON `contacts`.`user_id`=`clients`.`user_id` OR `contacts`.`id`!='0'
$this->Record->select(array("clients.*"))->from("clients")->orOn("contacts.id", "!=", 0)->leftJoin("contacts", "contacts.user_id", "=", "clients.user_id", false);

Insert

Update

Delete

Table Management

Create

Alter

Truncate

Drop

Transactions

Transactions offer a method of executing queries in series, with the ability to roll back to a previous state if an error occurs. Any database error will throw a PDOException.

Transactions must be supported by the database engine

Blesta uses the InnoDB engine for all of its tables, and you are encouraged to do the same. Transactions will fail on database engines that do not support transactions.

Using Transactions

A Model

try {
    // Begin transaction
    $this->Record->begin();

    // Run queries...

    // Commit queries
    $this->Record->commit();
}
catch (PDOException $e) {
    // Rollback the queries, error occurred.
    $this->Record->rollback();
}

Page tree