Versions Compared

Old Version 3

changes.mady.by.user Cody Phillips

Saved on

compared with

New Version 4

changes.mady.by.user Cody Phillips

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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 components component and its common uses.

Table of Contents
Table of Contents
outlinetrue
classtoc

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

Code Block
languagephp
// Produces SELECT * FROM `clients`
$this->Record->select()->from("clients");

Tuples

Code Block
languagephp
// Produces SELECT `id`,`id_code`,`user_id` FROM `clients`
$this->Record->select(array("id","id_code","user_id"))->from("clients");

Tuple Aliasing

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
$this->Record->select()->from("clients")->limit(10, 20);

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

Where

Code Block
languagephp
// Produces SELECT * FROM `clients` WHERE `clients`.`id`='15'
$this->Record->select()->from("clients")->where("clients.id", "=", 15);

And Where

Code Block
languagephp
// 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

Code Block
languagephp
// 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");

Like

Code Block
languagephp
// 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

Code Block
languagephp
// 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

Code Block
languagephp
// 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

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`
$this->Record->select(array("MAX(id)"=>"largest_id"))->from("clients");

Grouping Conditions

Code Block
languagephp
// 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();

 

Insert

Update

Delete

 

Table Management

...