Fuel LibraryDB

This document and demo application are intended serve the role of Php WebLibraryDB by introducing the FuelPhp database mechanisms. The same MySQL database is assumed and same tables used.

FuelPhp, like other Web Frameworks, supports deployment of the application at multiple levels, which in Fuel's case are: It is common to use a separate database for each level. For example, in development mode, we may need to be able to "start over," seeding the database from scratch, whereas this would never be done in production mode. We are working, obviously, in development mode, and so the equivalent of the include/db.php file used in the RedBeanPhp version is
fuel/app/config/development/db.php
The connection information provided in this file is precisely what the Php/PDO API requires and is what is used in RedBeanPhp.

Installation

Make sure you have the required plugins installed in NetBeans: PHP, Smarty, FuelPHP. Also make sure that Apache rewrite is enabled (it is by default in XAMPP).

Download the source archive FuelLibraryDB.zip. Extract the archive into the folder which holds your "web projects," which we will assume corresponds to the "/default" URL. Windows users are advised to download the archive and extract it using 7-zip.

Understand exactly what is the "php" command shell command on your system.
  1. Create FuelLibraryDB as a NetBeans Php Project from Existing Sources. On the run configuration step, make the Project URL be:
    http://localhost/default/FuelLibraryDB/public
    
  2. On MAC/Linux, open a command shell, navigate to the FuelLibraryDB directory and run:
    $ php oil r install
    
  3. Double-check the RewriteBase in public/.htaccess. It is currently:
    RewriteBase  /default/FuelLibraryDB/public
    It is meant to work as is if you use the "/default" URL as suggested.
  4. Enable the FuelPhp features for the project:
    • Right-click on the FuelLibraryDB project in the Projects window and select Properties.
    • Locate FuelPhp in Frameworks check the enabled checkbox.
  5. Enable FuelPHP code completion in NetBeans. Right-click on the FuelLibraryDB project and select from the menu:
    FuelPHP ⇾ generate auto-completion file

Specify settings in the database config file

Double-check the settings in the FuelPhp database config file:

fuel/app/config/development/db.php
<?php
/**
 * The development database settings. These get merged with the global settings.
 */
 
// set this to either 'mysql' or 'sqlite'
$which = 'mysql';
 
 
$mysql = [
  // change these MySQL database specs if necessary
  'connection'  => [
    'dsn'        => 'mysql:host=localhost;dbname=test',
    'username'   => 'guest',
    'password'   => '',
  ],
];
 
$sqlite = [
  'charset' => NULL,
  'connection'  => [
    'dsn' => 'sqlite:' . APPPATH . 'database/db.sqlite',
  ],
];
 
$choices = [
  'mysql' => $mysql,
  'sqlite' => $sqlite,
];
 
return [
  'which' => $which,
  'default' => $choices[$which],  // makes this the one used
];

Database Initialization

This application, like all the others assumes the usage of the MySQL test database accessible by the guest user with empty password. The tables and data used are identical to that in Php WebLibrary, so you are most likely good to go. To initialize the database in FuelPhp, run this task:
$ php oil r maketables
A Php task is a class which resides in the directory
fuel/app/tasks
Specifically, your are executing the static run function in the class MakeTables which is found in the file
fuel/app/tasks/maketables.php
The run function is broken down into two run functions found in classes contained in the files
createtables.php
populatetables.php
The latter one, populatetables.php offers some very useful examples about how to work with the FuelPHP ORM.
fuel/app/tasks/populatetables.php  

Check the installation

Double-check the validity of the "/" and "/home/index" URLs, assumed here to be:

FuelPhp Database

FuelPhp provides three database APIs: The ORM and DB approaches are comparable. They are both abstract in the sense of not using SQL directly. The ORM is typically easier to use, but the DB class offers capabilities beyond what the ORM can do (or at least can easily do).

The development database

We mentioned above about the execution modes. When you configure that database settings you'll see that there are 5 "db.php" files in fuel/app/config:
test/db.php
development/db.php    (the one you want)
staging/db.php
production/db.php
db.php
Four of these correspond to the four execution modes in an obvious way. The top level db.php is meant for settings which are common among all four modes. These and other files from fuel/app/config are available for editing in NetBeans' Important Files group; unfortunately the files are presented by their base name only, and so you'll see five db.php files with no indication of which is which!

The Model classes

When the FuelPhp ORM is used, the tables are accessed from a dedicated class called a model. See the ORM - Models docs for full information. Here are the Model classes used in this web app:

fuel/app/classes/model/book.php
<?php
 
class Model_Book extends \Orm\Model {
 
  protected static $_table_name = 'book';  // default is 'books'
 
  protected static $_properties = [
      'id',
      'title',
      'quantity',
      'binding',
  ];
 
  protected static $_many_many = [
    'borrowers' => [
       'table_through' => 'borrow',
       'model_to' => 'Model_User',
    ],
  ];
 
  protected static $_has_many = [
    'borrows' => [
        'model_to' => 'Model_Borrow',
    ]
  ];  
 
  public function __toString() {
    $num_borrowers = count($this->borrowers);
    return "$this->title ($num_borrowers)";
  }
}

fuel/app/classes/model/user.php
<?php
 
class Model_User extends \Orm\Model {
 
  protected static $_table_name = 'user';  // default is 'users'
 
  protected static $_properties = [
    'id',
    'name',
    'email',
    'password',
    'is_admin' => [
        'default' => 0,
    ],
  ];
 
  protected static $_many_many = [
    'borrowed_books' => [
       'table_through' => 'borrow',
       'model_to' => 'Model_Book',
    ],
  ];
 
  protected static $_has_many = [
    'borrows' => [
        'model_to' => 'Model_Borrow',
    ]
  ];
 
  public function __toString() {
    return $this->name;
  }
}

fuel/app/classes/model/borrow.php
<?php
 
class Model_Borrow extends \Orm\Model {
 
  protected static $_table_name = 'borrow';
 
  protected static $_properties = [
      'id',
      'book_id',
      'user_id',
      'borrowed_at',
  ];
 
  protected static $_belongs_to = [
    'book' => [
        'model_to' => 'Model_Book',
    ],
    'user' => [
        'model_to' => 'Model_User',
    ]
  ];
}
It is necessary to list all the fields of each table in the $_properties setting. The listing can simply be the field, or it can be a field/map pair as in Model_User:
'is_admin' => [
      'default' => false,
],
There key/value pairs in the map assign properties to the field. In this case, they define the default (false) value for is_admin. Although MySQL sets default properties, FuelPhp effectively ignores the table-specific settings.

It is also necessary in our case to define the table name through the $_table_name setting. You can retrieve this name through the static table member function:
Model_Book::table()
The goal is to express the information about the relationships between tables through the models. The most direct way to create the models is to name them after the tables, but this is not necessary, just the assumed default.

Many-to-many

The many-to-many relationship between books and users is expressed by these symmetric features in Model_User and Model_Book:
class Model_User extends \Orm\Model {
  ...
  protected static $_many_many = [
    'borrowed_books' => [
       'table_through' => 'borrow',
       'model_to' => 'Model_Book',
    ],
  ];
  ...
}
class Model_Book extends \Orm\Model {
  ...
  protected static $_many_many = [
    'borrowers' => [
       'table_through' => 'borrow',
       'model_to' => 'Model_User',
    ],
  ];
  ...
}
According to this setup, we can find the books borrowed by a user like this:
$user->borrowed_books    // array of Model_Book objects
and, vice-versa, the users who borrow a book like this:
$book->borrowers     // array of Model_User objects

Many-to-one

The many-to relationship between books and borrow records is expressed by these asymmetric features in Model_Book and Model_Borrow:
class Model_Book extends \Orm\Model {
  ...
  protected static $_has_many = [
    'borrows' => [
        'model_to' => 'Model_Borrow',
    ]
  ];  
  ...
 
}
class Model_Borrow extends \Orm\Model {
  ...
  protected static $_belongs_to = [
    'book' => [
        'model_to' => 'Model_Book',
    ],
    ...
  ];
  ...
}
According to this setup, we can find the borrow records of a given book like this:
$book->borrows    // array of Model_Borrow objects
Going the other way indicates which book is associated with a given borrow record:
$borrow->book     // a Model_Book object
The analogous setup is made for the user/borrow relationship. We can find the borrow records for a given user: like this:
$user->borrows    // array of Model_Borrow objects
as well as which user is associated with a given borrow record:
$borrow->user     // a Model_Book object

Demo Scripts

Here are the files used in this demo program, including the home controller, the 2 view scripts plus the helper class for reading/writing the borrowed_at date.

fuel/app/classes/controller/home.php
<?php
class Controller_Home extends Controller {
 
  public function action_index() {
    return View::forge('home/index.tpl');
  }
 
  public function action_books() {
    $books = Model_Book::find('all', [
        //'order_by' => [ 'title' ],
        //'order_by' => [ 'quantity' => 'desc' ],
        //'order_by' => [ 'quantity' => 'desc', 'title' ],
        //'where' => [ [ 'quantity', '>=', 2 ] ],
        //'where' => [ [ 'quantity', '>=', 2 ], [ 'binding', '=', 'paper' ] ],
        //'where' => [ [ 'quantity', '>=', 2 ], [ 'binding', 'paper' ] ],
        //'where' => [ [ 'quantity', '>=', 2 ], 'binding' => 'paper' ],
    ]);
    $data = [
        'books' => $books,
    ];
    return View::forge('home/books.tpl', $data);
  }
 
  public function action_getBorrows() {
    $books = null;
    $name = null;
    if (!is_null(Input::post('doit'))) {
      $user_id = Input::post('user_id');
      $user = Model_User::find($user_id);
 
      $books = $user->borrowed_books;
 
//    $books = $user->get( 'borrowed_books', [ 'order_by' => [ 'title'] ] );
 
      $name = $user->name;
    }
    $data = [
        'users' => Model_User::find('all'),
        'user_id' => Input::post('user_id', null),
        'name' => $name,
        'books' => $books,
    ];
    $view = View::forge("home/getBorrows.tpl", $data);
    $view->set('helper', new Helper(), false);
    return $view;
  }
 
  public function action_getBorrowers() {
    $borrows = null;
    $title = null;
    if (!is_null(Input::post('doit'))) {
      $book_id = Input::post('book_id');
      $book = Model_Book::find($book_id);
      $title = $book->title;
 
      // various approaches to getting borrows:
 
      $borrows = $book->borrows;
 
//      $borrows = $book->get('borrows', [ 'order_by' => [ 'borrowed_at' ] ]);
 
//      $borrows = Model_Borrow::find('all', [
//        'where' => [ [ 'book_id', $book_id ] ],
//        'order_by' => [ 'borrowed_at' ],
//      ]);
    }
    $data = [
        'books' => Model_Book::find('all'),
        'book_id' => Input::post('book_id', null),
        'borrows' => $borrows,
        'title' => $title,
    ];
    return View::forge("home/getBorrowers.tpl", $data);
  }
}

fuel/app/views/home/books.tpl
{extends file="layout.tpl"}
 
{block name='localstyle'}
  <style type='text/css'>
    td, th {
      text-align: left;
      padding: 2px 5px;
    }
  </style>
{/block}
 
{block name="content"}
  <h2>Books</h2>
 
  <table>
    <tr>
      <th>id</th><th>title</th><th>binding</th><th>quantity</th>
    </tr>
    {foreach $books as $book}
      <tr>
        <td>{$book->id}</td>
        <td>{$book->title}</td>
        <td>{$book->binding}</td>
        <td>{$book->quantity}</td>
      </tr>
    {/foreach}
  </table>
{/block}

fuel/app/views/home/getBorrows.tpl
{extends file="layout.tpl"}
 
{block name="content"}
 
  <h2>Get Borrowed Books</h2>
 
  {form}
  <select name="user_id">
    {html_options options=$users selected=$user_id}
  </select>
  <button type="submit" name="doit">Select</button>
  {/form}
 
  <hr />
 
  {if $books}
    <h4>{$name}'s borrowed books</h4>
    {foreach $books as $book}
      {$book->title}
      (<b>on</b> {$helper->getBorrowedDate($book->id,$user_id)})
      <br />
    {/foreach}
  {/if}
 
{/block}

fuel/app/views/home/getBorrowers.tpl
{extends file="layout.tpl"}
 
{block name="content"}
 
  <h2>Get Book Borrowers</h2>
 
  {form}
  <select name="book_id">
    {html_options options=$books selected=$book_id}
  </select>
  <button type="submit" name="doit">Select</button>
  {/form}
 
  <hr />
 
  {if $books}
    <h4>Borrowers of <em>{$title}</em></h4>
    {foreach $borrows as $borrow}
      {$borrow->user->name}
      (<b>on</b> {$borrow->borrowed_at})
      <br />
    {/foreach}
  {/if}
 
{/block}

fuel/app/classes/helper.php
<?php
 
class Helper {
  public static function getBorrowedDate($book_id, $user_id) {
    $join = Model_Borrow::find('first', [
      'where' => ['book_id' => $book_id, 'user_id' => $user_id,]
    ]);
    if (is_null($join)) {
      throw new Exception("no join for ($book_id,$user_id)");
    }
    return $join->borrowed_at;
  }
 
  public static function upToNdaysBeforeNow($n) {
    $now = time();
    $before = $now - rand(0,$n-1) * 24 * 3600;
    return date("Y-m-d", $before);
  }
}

Code Observations

  1. In action_books, uncomment each commented line individually, commenting the others. Also uncomment both an order_by line plus a where line.
  2. In action_getBorrows there are both ways to retrieve the books borrowed by a selected user.
  3. In action_getBorrows, there are 3 ways to retrieve the borrow records of a selected book. The table initiation code gives a greater chance of being borrowed, thus giving more borrowers of a given book. You can read the number of borrowers in the option label.
  4. The selection list of users in getBorrows is created by the Smarty construction:
    <select name="user_id">
      {html_options options=$users selected=$user_id}
    </select>
    Smarty takes each Model_User object, $user, obtained by interating through the $users lists and generates an option like this:
    <option value="<?php echo $user->id ?>"><?php echo $user?></option>
    In particular, it is necessary that we can print an individual $user. This is why, in Model_User we need, the function:
    class Model_User extends \Orm\Model {
      ...
      public function __toString() {
        return $this->name;
      }
    }

Basic ORM usage

The features of the FuelPhp ORM are way too many to categorize and list here. The usage in our applications reveals only the tip of the iceberg. You can find many examples of common usage here:
ORM CRUD Docs

Adding records

Here is an example addition:
$book = new Model_Book();  // or, $book = Model_Book::forge();
$book->title = /* some title */;
$book->binding = /* "paper" or "cloth" */;
$book->quantity = /* some non-neg. number */
$book->save();
$book_id = $book->id; // id of newly create record
It appears that this operation may not accept default values, meaning that all non-null fields must be assigned.

Retrieve by id primary key

The Model::find function is the general-purpose way to retrieve on or more record based on the type of its arguments. If given a single integer argument, it treats it as the record id.
$book = Model_Book::find(4);
if (!isset($book)) {   // or (is_null($book))
  // no such book
}

Modify/Delete an existing book

The idea is the same as adding; the difference being that we start with an existing book.
$book = Model_Book::find(4);
$book->quantity += 1;
$book->save();
and
$book = Model_Book::find(4);
$book->delete();

Select multiple records with find

The Model::find operation, used with a non-integer parameter (and possibly others) generates SELECT operations based on queries other than by id. For example, when used like this we get all records:
$books = Model_Book::find('all');
Like RedBeanPhp ORM, it delivers an array map associating each book id to a database object representing the book.

In this case, where, order and limit qualifications can be delivered through a second array map parameter. For example a WHERE clause can be delivered as as the value of the "where" key:
$books = Model_Book::find('all', [
  'where' => [ ['binding', 'paper'] ],
]);
 
// or
 
$books = Model_Book::find('all', [
  'where' => [ ['binding', '=', 'paper'] ],
]);
 
Note the "double array" usage. This is because the outer inner arrays are "and'ed" together to form a query. For example:
$books = Model_Book::find('all', [
  'where' => [ [ 'binding', 'paper' ], [ 'quantity', '>', 7 ] ],
]);
The "ORDER BY" features are delivered through the "order_by" key in the outermost map, e.g.,
$books = Model_Book::find('all', [
  'where' => [ [ 'binding', 'paper' ], [ 'quantity', '>', 7 ] ],
  'order_by' => [ 'title' ],
]);
A single value means to order ascending by that field. Multiple fields can be used. The order_by array value can mix single values and map-style values like
'order_by' => [ 'quantity' => 'desc', 'title' ]
In all cases, the result of find('all') is an array, only empty if none are found.

Fetch a single record, not by id

The Model::find('first') or Model::find('last') operations returns a single record object. This is commonly used for retrieval based on a unique field, e.g.:
$username = "alice";
$user = Model_User::find('first', [
   'where' => [ [ 'name', $username ] ],
]);
 
if (!isset($user)) {
  // no such user
}

Accessing joined records

We described, in the Model classes section above how to access related records through features specified in the Model classes. In particular, with this in place, these expressions represents the array of all books borrowed by a user, and all users who borrow (a copy of) a given book:
$user->borrowed_books
//or
$book->borrowers
Another, more flexible way is through the get member function like this:
$user->get('my_books');

Add/remove joins

The operation of adding a borrow record from a user to a book is this:
$user->borrowed_books[] = $book;
Like RedBeanPhp, using this assumes that extra fields within the join record can either be null, or have default values, so that they don't have to be specified for the borrow record to be created.

Removing a join record is this:
unset($user->borrowed_books[$book->id]);
Repeated adds or removes of joins create no new effects.

Unfortunately, FuelPhp ORM provides no indirect access (at least I know of none) to other fields in a join record. You must go through the join model like this:
$join = Model_Borrow::find('first', [
  'where' => [ 'book_id' => $book->id, 'user_id' => $user->id ]
]);
$join->borrowed_at = /* the date */;
$join->save();
The creation of the join in this way is equivalent to
$join = Model_Borrow::find('first', [
  'where' => [ [ 'book_id', $book->id ], ['user_id', $user->id]  ]
]);
You can create borrows directly (once only):
$join = new Model_Borrow();
$join->book_id = $book->id;
$join->user_id = $user->id;
$join->borrowed_at = /* some date */;
$join->save();
or
$join = new Model_Borrow();
$join->book = $book;
$join->user = $user;
$join->borrowed_at = /* some date */;
$join->save();


© Robert M. Kline