Versions Compared

Old Version 7

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

...

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");

Tuple

Limit

Code Block
languagephp


// 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
languagephp
// Produces SELECT * FROM `clients` LIMIT 20, 10
$this->Record->select()->from("clients")->limit(10, 20);

Order 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"));

...

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


// Produces SELECT `clients`.* FROM `clients` LEFT 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);

...

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);

// FetchBuild anythe valuesSQL setfor from the subquery
$valuessub_query
$sub_query_sql = $sub_query_record->values>get();

// Fetch Buildany thevalues SQLset forfrom the subquery sub_query
$sub_query_sql
$values = $sub_query_record->get()>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($value$values)->from("users")->innerJoin(array($sub_query_sql=>"temp"), "temp.user_id", "=", "users.id", false);

...

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($value$values)->from(array($sub_query_sql=>"temp"))->where("temp.id","=",125);

...

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($value$values)->select(array("(" . $sub_query_sql . ")"=>"temp"), false)->from("clients")->where("clients.id","=",125);

...

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

...

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

...

language

Table Management

Create

Alter

Truncate

Drop

...

php
// 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.

...