Difference between revisions of "RedBeanPHP Cheat Sheet"

From PaskvilWiki
Jump to: navigation, search
Line 4: Line 4:
  
 
This is by no means complete cheat sheet; many things were left out, see [http://www.redbeanphp.com/index.php full documentation] for all tips and tricks.
 
This is by no means complete cheat sheet; many things were left out, see [http://www.redbeanphp.com/index.php 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 ==
 
== Basics and CRUD ==
Line 23: Line 25:
 
// if it does exist, RBPHP will update the table to hold any new data, if necessary
 
// if it does exist, RBPHP will update the table to hold any new data, if necessary
 
$id = R::store($book);
 
$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
 
// reading a bean by ID, or multiple by array of ID's
Line 47: Line 53:
 
$bean = $bean->fresh();</pre>
 
$bean = $bean->fresh();</pre>
  
== Select / Query ==
+
== Select / Query / Transactions ==
  
 
<pre>$book = R::find('book', 'rating > 4');
 
<pre>$book = R::find('book', 'rating > 4');
Line 70: Line 76:
  
 
// if the bean does not exist, it's created
 
// if the bean does not exist, it's created
$book = R::findOrCreate('book', ['title' => 'my book', 'price' => 50]);</pre>
+
$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..
 +
});</pre>
 +
 
 +
== Other Helpful Stuff ==
 +
 
 +
<pre>// 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');</pre>

Revision as of 18:08, 10 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();

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

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