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 |
---|
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.
// Produces SELECT * FROM `clients` $this->Record->select()->from("clients"); |
// Produces SELECT `id`,`id_code`,`user_id` FROM `clients` $this->Record->select(array("id","id_code","user_id"))->from("clients"); |
// 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"); |
// Produces SELECT * FROM `clients` LIMIT 20, 10 $this->Record->select()->from("clients")->limit(10, 20); |
// Produces SELECT * FROM `clients` ORDER BY `clients`.`user_id` ASC $this->Record->select()->from("clients")->order(array("clients.user_id"=>"asc")); |
// Produces SELECT * FROM `clients` WHERE `clients`.`id`='15' $this->Record->select()->from("clients")->where("clients.id", "=", 15); |
// 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"); |
// 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"); |
// Produces SELECT * FROM `clients` WHERE `id` IN ('1','2','3','4') $this->Record->select()->from("clients")->where("id", "in", array(1,2,3,4)); |
// 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%"); |
// 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%"); |
// 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")); |
// 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); |
// Produces SELECT MAX(`id`) AS `largest_id` FROM `clients` $this->Record->select(array("MAX(id)"=>"largest_id"))->from("clients"); |
// 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(); |
// 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); |
// 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); |
// 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); |
// 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"); |
// 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); |
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.
$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; |
// 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); |
// 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); |
// 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); |
// 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); |
$this->Record->select()->from("clients")->numResults(); |
$this->Record->lastInsertId(); |
// 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)); |
// 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"); |
// 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")); |
// 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")); |
$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)); |
// 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")); |
// 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")); |
// 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"); |
// Executes DELETE FROM `clients` $this->Record->from("clients")->delete(); |
// 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.*")); |
// 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"); |
// 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"); |
// Executes TRUNCATE TABLE `feed_reader_feeds` $this->Record->truncate("feed_reader_feeds"); |
// Executes DROP TABLE `feed_reader_feeds` $this->Record->drop("feed_reader_feeds"); |
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.
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. |
try { // Begin transaction $this->Record->begin(); // Run queries... // Commit queries $this->Record->commit(); } catch (PDOException $e) { // Rollback the queries, error occurred. $this->Record->rollback(); } |