...
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($value$values)->from("users")->innerJoin(array($sub_query_sql=>"temp"), "temp.user_id", "=", "users.id", false); |
...
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($value$values)->from(array($sub_query_sql=>"temp"))->where("temp.id","=",125); |
...
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($value$values)->select(array("(" . $sub_query_sql . ")"=>"temp"), false)->from("clients")->where("clients.id","=",125); |
...
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)) |
Miscellaneous Select Statements
...
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 Name", 'last_name' => "Last Name"), 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
Delete
Table Management
...