...
Code Block |
---|
|
// Produces SELECT `id`,`id_code` AS `key`,`user_id` FROM `clients`
$this->Record->select(array("id","id_code"=>"key","user_id"))->from("clients"); |
Limit
Code Block |
---|
|
// Produces SELECT * FROM `clients` LIMIT 20, 10`id`, 'customer' AS `type` FROM `clients`
$this->Record->select(array("id"))->from("clients")->limit(10, 20);>select(array('\'customer\''=> "type"), false)->from("clients"); |
Limit
Code Block |
---|
|
// Produces SELECT * FROM `clients` LIMIT 20, 10
$this->Record->select()->from("clients")->limit(10, 20); |
Order Order By
Code Block |
---|
|
// Produces SELECT * FROM `clients` ORDER BY `clients`.`user_id` ASC
$this->Record->select()->from("clients")->order(array("clients.user_id"=>"asc")); |
...
Code Block |
---|
|
// 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 |
---|
|
// 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
...
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 |
---|
|
$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 |
---|
|
// 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 |
---|
|
// 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 |
---|
|
// 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 |
---|
|
// 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 |
---|
|
$this->Record->select()->from("clients")->numResults(); |
Last Insert ID
Code Block |
---|
|
$this->Record->lastInsertId(); |
Custom SQL
Code Block |
---|
|
// 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 |
---|
|
// 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 |
---|
|
// 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 |
---|
|
// 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 |
---|
|
$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 |
---|
|
// 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 |
---|
|
// 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 |
---|
|
// 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 |
---|
|
// Executes DELETE FROM `clients`
$this->Record->from("clients")->delete(); |
Multi-delete
Code Block |
---|
|
// 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 |
---|
|
// 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 |
---|
|
// 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 |
---|
|
// Executes TRUNCATE TABLE `feed_reader_feeds`
$this->Record->truncate("feed_reader_feeds"); |
Drop
Code Block |
---|
|
// 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.
...