Versions Compared

Old Version 5

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

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 * FROM `clients` HAVING `primary_account_type` IS NOT NULL
$this->Record->select()->from("clients")->having("primary_account_type", "!=", null);

Aggregate Functions

Code Block
languagephp


// Produces SELECT MAX(`id`) AS `largest_id` FROM `clients` * FROM `clients` HAVING `primary_account_type` IS NOT NULL OR `id`>'0'
$this->Record->select(array("MAX(id)"=>"largest_id"))->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_ 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();

...

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


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

 

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

...