Difference between revisions of "RedBeanPHP Cheat Sheet"

Latest revision as of 22:45, 11 January 2016

RedBeanPHP site

I love RBPHP since it's single file light-weight x-DB and clean ORM. -- my opinion (R) --

This is by no means complete cheat sheet; many things were left out, see full documentation for all tips and tricks.

In code samples, PHP 5.4+ array notation is used; replace with array(...) notation of PHP < 5.4.

Basics and CRUD

Download RedBeanPHP

require 'rb.php';

R::setup();                                                            // creates test SQLite DB in /tmp
R::setup('mysql:host=localhost;dbname=mydatabase', 'user', 'pass');    // MySQL and MariaDB
R::setup('pgsql:host=localhost;dbname=mydatabase', 'user', 'pass');    // PostgreSQL
R::setup('sqlite:/tmp/dbfile.db');                                     // SQLite
R::setup('cubrid:host=localhost;port=30000;dbname=mydb', 'U','P');     // CUBRID (requires [https://github.com/gabordemooij/RB4Plugins plugin pack])

$is_connected = R::testConnection();

// when done (not yet :) - disconnect
R::close();

// create a new bean, and setting some properties (member and array access supported);
// the bean name has to be lowercase alphabetical;
// properties names have to contain only alphanumeric and underscore
$book = R::dispense('book');
$book->title = 'Learn to Program';
$book['rating'] = 10;
$book['price'] = 29.99;

// save it - if table does not exist, RBPHP will create it based on added properties;
// if it does exist, RBPHP will update the table to hold any new data, if necessary
$id = R::store($book);

// MySQL/MariaDB compatible DB's provide insert ID
R::exec('INSERT INTO ... ');
$id = R::getInsertID();

// reading a bean by ID, or multiple by array of ID's
$book = R::load('book', $id);
$books = R::loadAll('book', $ids);

// updating a bean
$book->title = 'Learn to fly';
$book->rating = 'good';             // rating will be changed from integer to varchar
$book->published = '2015-02-15';    // column will be added, type 'date'; R::isoDate() and R::isoDateTime() generate current date(time)
R::store($book);

// deleting a bean or multiple beans
R::trash($book);
R::trashAll($books);

// delete all beans of given type
R::wipe('book');

// destroy the whole DB
R::nuke();

// reload bean from DB
$bean = $bean->fresh();

Fluid and Freeze

In all of the above examples, schema is automatically updated by RBPHP. This is good when developing and testing, but unwanted on production. You can freeze the schema, and force RBPHP to work only with what it has.

Note that the schema RBPHP generates is typically far from production style - always review the schema generated, and update to your needs; then freeze and go on with stable DB schema.

// freeze all tables, no alterations possible
R::freeze(true);
// freeze just some tables, others are fluid
R::freeze(['book','page','book_page']);
// back to (default) fluid mode
R::freeze(false);

Select / Query / Transactions

$book = R::find('book', 'rating > 4');
$books = R::find('book', 'title LIKE ?', ['Learn to%']);    // with bindings
$promotions = R::find('person', 'contract_id IN ('.R::genSlots($contractIDs).')', $contractIDs);    // R::genSlots() generates ?'s for bindings
$book = R::findOne('book', 'title = ?', ['SQL Dreams']);    // returns single bean, not array; NULL if none found

// find all, no WHERE's
$books = R::findAll('book');
$books = R::findAll('book' , 'ORDER BY title DESC LIMIT 10');

// all of find(), findOne(), and findAll() support named slots
$books = R::find('book', 'rating < :rating', [':rating' => 2]);

// using cursors - saves on loading
$collection = R::findCollection('page', 'ORDER BY content ASC LIMIT 5');
while($item = $collection->next())
    // process bean $item

// find with multiple possible values
R::findLike('flower', ['color' => ['yellow', 'blue']], 'ORDER BY color ASC');

// if the bean does not exist, it's created
$book = R::findOrCreate('book', ['title' => 'my book', 'price' => 50]);

// raw queries
R::exec('UPDATE page SET title="test" WHERE id = 1');
// results as multidimensional array, and with parameters binding; returns array of arrays
R::getAll('SELECT * FROM page');
R::getAll('SELECT * FROM page WHERE title = :title', [':title' => 'home']);
// single row, as array
R::getRow('SELECT * FROM page WHERE title LIKE ? LIMIT 1', ['%Jazz%']);
// single column, as array
R::getCol('SELECT title FROM page');
// single cell, as value
R::getCell('SELECT title FROM page LIMIT 1');
// use first column as key of the array, and second column as value
R::getAssoc('SELECT id, title FROM page');

// transactions - store(), trash() etc. throw exceptions, so when you "catch" you can perform rollback:
R::begin();
try
{
    R::store( $page );
    R::commit();
}
catch( Exception $e )
{
    R::rollback();
}

// transaction closure
R::transaction(function() {
    ..store some beans..
});

Other Helpful Stuff

// get tables in current DB
$listOfTables = R::inspect();
// get columns of the table
$fields = R::inspect('book');

// add a new DB connection
R::addDatabase('DB1', 'sqlite:/tmp/d1.db', 'user', 'pass', $frozen);
// use DB connection; to use the one connected to in R::setup(), use 'default' as DB alias
R::selectDatabase('DB1');