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 |
---|
Data Management
Select
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.
All
// Produces SELECT * FROM `clients` $this->Record->select()->from("clients");
Tuples
// Produces SELECT `id`,`id_code`,`user_id` FROM `clients` $this->Record->select(array("id","id_code","user_id"))->from("clients");
Tuple Aliasing
// Produces SELECT `id`,`id_code` AS `key`,`user_id` FROM `clients` $this->Record->select(array("id","id_code"=>"key","user_id"))->from("clients");
Limit
// Produces SELECT * FROM `clients` LIMIT 20, 10 $this->Record->select()->from("clients")->limit(10, 20);
Order By
// Produces SELECT * FROM `clients` ORDER BY `clients`.`user_id` ASC $this->Record->select()->from("clients")->order(array("clients.user_id"=>"asc"));
Where
// Produces SELECT * FROM `clients` WHERE `clients`.`id`='15' $this->Record->select()->from("clients")->where("clients.id", "=", 15);
And Where
// 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");
Or Where
// 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");
Where In
// Produces SELECT * FROM `clients` WHERE `id` IN ('1','2','3','4') $this->Record->select()->from("clients")->where("id", "in", array(1,2,3,4));
Like
// 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%");
Or Like
// 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%");
Group By
// 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"));
Having
// 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);
Aggregate Functions
// Produces SELECT MAX(`id`) AS `largest_id` FROM `clients` $this->Record->select(array("MAX(id)"=>"largest_id"))->from("clients");
Grouping Conditions
// 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();
Joins
Inner Join
// 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);
Left Join
// 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);
Right Join
// 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);
Cross Join
// Produces SELECT `clients`.* FROM `clients`, `contacts` $this->Record->select(array("clients.*"))->from("clients")->from("contacts");
Multiple conditions
// 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
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.
Transactions must be supported by the database engine
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.
Using 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(); }