Versions Compared

Old Version 15

changes.mady.by.user Cody Phillips

Saved on

compared with

New Version Current

changes.mady.by.user Cody Phillips

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 5.3

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
Table of Contents
outlinetrue
classtoc

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

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

Tuples

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

Tuple Aliasing

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

// Produces SELECT `id`, 'customer' AS `type` FROM `clients`
$this->Record->select(array("id"))->select(array('\'customer\''=> "type"), false)->from("clients");

Limit

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

Order By

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

Where

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

And Where

Code Block
languagephp
// 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

Code Block
languagephp
// 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

Code Block
languagephp
// 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

Code Block
languagephp
// 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

Code Block
languagephp
// 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

Code Block
languagephp
// 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

Code Block
languagephp
// 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

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

Grouping Conditions

Code Block
languagephp
// 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
Code Block
languagephp
// 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
Code Block
languagephp
// 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
Code Block
languagephp
// 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
Code Block
languagephp
// Produces SELECT `clients`.* FROM `clients`, `contacts`
$this->Record->select(array("clients.*"))->from("clients")->from("contacts");

// Produces SELECT `clients`.* FROM `clients` JOIN `contacts`
$this->Record->select(array("clients.*"))->from("clients")->join("contacts");
Multiple conditions
Code Block
languagephp
// 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);

Subqueries

All subqueries start first with the subquery. The idea is to construct the query from the inside out, and as each layer is added the subquery becomes part of the parent query.

Code Block
languagephp
$client_id = 12;

// Produces SELECT `clients`.*, `contacts`.* FROM `clients` INNER JOIN `contacts` ON `contacts`.`client_id`=`clients`.`id` WHERE `clients`.`id`='12' AND `contacts`.`user_id`=`clients`.`user_id`
$sub_query_record = new Record();
$sub_query_record->select(array("clients.*","contacts.*"))->from("clients")->
    innerJoin("contacts", "contacts.client_id", "=", "clients.id", false)->
    where("clients.id", "=", $client_id)->
    where("contacts.user_id", "=", "clients.user_id", false);

// Build the SQL for the sub_query
$sub_query_sql = $sub_query_record->get();

// Fetch any values set from the subquery 
$values = $sub_query_record->values;
Join on Subquery
Code Block
languagephp
// Produces SELECT `users`.* FROM `users` INNER JOIN (SELECT `clients`.*, `contacts`.* FROM 
// `clients` INNER JOIN `contacts` ON `contacts`.`client_id`=`clients`.`id` WHERE `clients`.`id`='12' AND `contacts`.`user_id`=`clients`.`user_id`) 
// AS `temp` ON `temp`.`user_id`=`users`.`id`
$this->Record->select("users.*")->appendValues($values)->from("users")->innerJoin(array($sub_query_sql=>"temp"), "temp.user_id", "=", "users.id", false);
Subquery as a Tuple
Code Block
languagephp
// Produces SELECT `temp`.`id` FROM (SELECT `clients`.*, `contacts`.* FROM 
// `clients` INNER JOIN `contacts` ON `contacts`.`client_id`=`clients`.`id` WHERE `clients`.`id`='12' AND `contacts`.`user_id`=`clients`.`user_id`) 
// AS `temp` WHERE `temp`.`id`='125'
$this->Record->select("temp.id")->appendValues($values)->from(array($sub_query_sql=>"temp"))->where("temp.id","=",125);
Subquery as a Column
Code Block
languagephp
// Produces SELECT `clients`.`id`, (SELECT `clients`.*, `contacts`.* FROM `clients` INNER JOIN `contacts` ON `contacts`.`client_id`=`clients`.`id` 
// WHERE `clients`.`id`='12' AND `contacts`.`user_id`=`clients`.`user_id`) AS `temp` FROM `clients` WHERE `clients`.`id`='125'
$this->Record->select("clients.id")->appendValues($values)->select(array("(" . $sub_query_sql . ")"=>"temp"), false)->from("clients")->where("clients.id","=",125);
Where not in Subquery
Code Block
languagephp
// Produces SELECT * FROM `clients` WHERE `id` NOT IN (SELECT `clients`.*, `contacts`.* FROM `clients` INNER JOIN 
// `contacts` ON `contacts`.`client_id`=`clients`.`id` WHERE `clients`.`id`='12' AND `contacts`.`user_id`=`clients`.`user_id`)
$this->Record->select()->from("clients")->appendValues($values)->where("id", "notin", array($sub_query_sql), false);

Miscellaneous Select Statements

Result Count

Code Block
languagephp
$this->Record->select()->from("clients")->numResults();

Last Insert ID

Code Block
languagephp
$this->Record->lastInsertId();

Custom SQL

Code Block
languagephp
// Select with single parameter
$this->Record->query("SELECT `clients`.* FROM `clients` WHERE `clients`.`id`=?", 12);

// Select with multiple parameters
$this->Record->query("SELECT `clients`.* FROM `clients` WHERE `clients`.`id`=? OR `clients`.`id`=?", 12, 15);

// Select with array of parameters
$this->Record->query("SELECT `clients`.* FROM `clients` WHERE `clients`.`id`=? OR `clients`.`id`=?", array(12, 15));

Insert

Simple Insert

Code Block
languagephp
// Executes INSERT INTO `clients` (`user_id`,`id_code`) VALUES ('15','USR15')
$this->Record->insert("clients", array('user_id' => 15,'id_code' => "USR15"));

// Executes INSERT INTO `clients` (`user_id`,`id_code`) VALUES ('15','USR15')
$this->Record->set("id_code","USR15")->set("user_id", 15)->insert("clients");

Insert with Filter

Code Block
languagephp
// Executes INSERT INTO `clients` (`user_id`,`id_code`) VALUES ('15','USR15')
$this->Record->insert("clients", array('user_id' => 15,'id_code' => "USR15", 'first_name' => "First", 'last_name' => "Last"), array("user_id","id_code"));

On Duplicate

Code Block
languagephp
// Executes INSERT INTO `clients` (`user_id`,`id_code`) VALUES ('15','USR15') ON DUPLICATE KEY UPDATE `id_code`='USR15'
$this->Record->duplicate("id_code", "=", "USR15")->insert("clients", array('user_id' => 15,'id_code' => "USR15"));

From Subquery

Code Block
languagephp
$sub_query_record = new Record(); 
$sub_query_record->select(array("clients.id"))->from("clients")->where("clients.id", "=", 12);

// Executes INSERT INTO `some_table` (`value`) (SELECT `clients`.`id` FROM `clients` WHERE `clients`.`id`='12')
$this->Record->insert("some_table", array('value'=>$sub_query_record));

Update

Simple Update

Code Block
languagephp
// Executes UPDATE `clients` SET `user_id`='15', `id_code`='USR15'
$this->Record->update("clients", array('user_id' => 15, 'id_code' => "USR15"));

// Executes UPDATE `clients` SET `id_code`='USR15' WHERE `user_id`='15'
$this->Record->where("user_id", "=", 15)->update("clients", array("id_code"=>"USR15"));

Update with Filter

Code Block
languagephp
// Executes UPDATE `clients` SET `user_id`='15', `id_code`='USR15'
$this->Record->update("clients", array('user_id' => 15,'id_code' => "USR15", 'first_name' => "First", 'last_name' => "Last"), array("user_id","id_code"));

Update with Increment

Code Block
languagephp
// Executes UPDATE `clients` SET `user_id`=user_id+3 WHERE `user_id`='15'
$this->Record->set("user_id","user_id+3", false, false)->where("user_id", "=", 15)->update("clients");

Delete

Simple Delete

Code Block
languagephp
// Executes DELETE FROM `clients`
$this->Record->from("clients")->delete();

Multi-delete

Code Block
languagephp
// Executes DELETE `clients`.*, `contacts`.* FROM `clients` INNER JOIN `contacts` ON `contacts`.`client_id`=`clients`.`id` WHERE `clients`.`id`='15'
$this->Record->from("clients")->innerJoin("contacts", "contacts.client_id", "=", "clients.id")->where("clients.id", "=", 15)->delete(array("clients.*","contacts.*"));

Table Management

Create

Code Block
languagephp
// Executes CREATE TABLE `feed_reader_feeds` (
// `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
// `url` varchar(255) NOT NULL,
// `updated` datetime DEFAULT NULL,
// PRIMARY KEY (`id`),
// UNIQUE KEY `url` (`url`)
// ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
$this->Record->
     setField("id", array('type' => "int",'size' => 10,'unsigned' => true,'auto_increment' => true))->
     setField("url", array('type' => "varchar", 'size' => 255))->
     setField("updated", array('type' => "datetime", 'is_null' => true, 'default' => null))->
     setKey(array("id"), "primary")->
     setKey(array("url"), "unique")->
     create("feed_reader_feeds");

Alter

Code Block
languagephp
// Executes ALTER TABLE `feed_reader_feeds`
// ADD `title` varchar(255) NOT NULL,
// DROP UNIQUE KEY `url`
$this->Record->
     setField("title", array('type' => "varchar", 'size' => 255))->
     setKey(array("url"), "unique", null, false)->
     alter("feed_reader_feeds");

Truncate

Code Block
languagephp
// Executes TRUNCATE TABLE `feed_reader_feeds`
$this->Record->truncate("feed_reader_feeds");

Drop

Code Block
languagephp
// Executes DROP TABLE `feed_reader_feeds`
$this->Record->drop("feed_reader_feeds");

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.

Note
titleTransactions 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

Code Block
languagephp
titleA 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();
}