MySQL and Sqlite

• Connecting to a database
• Selecting a table
• Selecting rows
• Counting rows
• Inserting rows
• Updating rows
• Deleting rows
• Pagination
• Fetching each row into a model class

Connecting to a database

MySQL

$db = new Database(array(
  'type'     => 'mysql',
  'host'     => '127.0.0.1',
  'database' => 'mydb',
  'user'     => 'mydb_user',
  'password' => 'supersecret'
));

Sqlite

$db = new Database(array(
  'type'     => 'sqlite',
  'database' => '/var/data/mydb.sqlite'
));

Selecting a table

$users = $db->table('users');

Example Table

Users

Selecting rows

Selecting all rows

$results = $users->all();

foreach($results as $user) {
  echo $user->username();
}

Limit the number of returned rows

$results = $users->limit(10)->all();

foreach($results as $user) {
  echo $user->username();
}

Order rows

$results = $users->order('username DESC')
                 ->limit(10)
                 ->all();

foreach($results as $user) {
  echo $user->username();
}

Set an offset

$results = $users->order('username DESC')
                 ->offset(10)
                 ->limit(10)
                 ->all();

foreach($results as $user) {
  echo $user->username();
}

Select specific columns

$results = $users->select(array('username', 'email'))
                 ->order('username DESC')
                 ->offset(10)
                 ->limit(10)
                 ->all();

foreach($results as $user) {
  echo $user->username();
}

Setting a where clause

Example 1

$results = $users->where(array('username' => 'homer'))
                 ->all();

foreach($results as $user) {
  echo $user->username();
}

Example 2

$results = $users->where('username', '=', 'homer')
                 ->all();

foreach($results as $user) {
  echo $user->username();
}

Example 3

$results = $users->where('email', 'LIKE', '%@simpson.com')
                 ->all();

foreach($results as $user) {
  echo $user->username();
}

Fetching a single row

Example 1

$user = $users->first();

Example 2

$user = $users->where('email', 'LIKE', '%@simpsons.com')->first();

Fetching a single row by id

$user = $users->find(1);

Fetching a single row by another field

$user = $users->findBy('username', 'homer');

Counting rows

Example 1

echo $users->count();

Example 2

echo $users->where('username', 'LIKE', 'H%')->count();

Inserting rows

if($id = $users->insert(array(
  'username' => 'moe',
  'email'    => 'moe@szyslak.com'
))) {
  echo 'Moe has been added and has the ID: ' . $id;
}

Updating rows

Example 1

$update = $users->where('id', '=', 2)->update(array(
  'username' => 'margery'
));

if($update) echo 'The user has been updated';

Deleting rows

Deleting a single row

if($users->where('id', '=', 1)->delete()) {
  echo 'The user has been deleted';
}

Deleting multiple rows

if($users->where('email', 'LIKE', '%@simpsons.com')->delete()) {
  echo 'All users with a simpsons email address have been deleted';
}

Deleting all rows

if($users->delete()) {
  echo 'All users have been deleted';
}

Pagination

$results    = $users->page(1, 20);
$pagination = $results->pagination();

foreach($results as $users) {
  echo $user->username();
}

Read more about the Pagination object.

Fetching each row into a model class

class UserModel extends Obj {
  public function isHomer() {
    return $this->username == 'homer';
  }
}

$results = $users->fetch('UserModel')->all();

foreach($results as $user) {
  if($user->isHomer()) {
    echo 'This is Homer';
  }
}