Use the RedBean PHP ORM in WordPress to Work with the Database

Working with the database directly through $wpdb can feel intimidating, especially for newer developers. In the PHP world, one interesting alternative is RedBeanPHP. It is small, powerful, and unusually easy to use. One of its most distinctive features is that it can create the database structure it needs automatically based on the data you store.

That makes it very attractive for WordPress-based applications that need custom tables and richer data structures.

Load rb.php and create the database connection

RedBeanPHP is lightweight enough that the framework is basically contained in a single rb.php file. To use it, just include that file and configure the database connection. In WordPress, the needed database settings are already defined in wp-config.php, so we can reuse them directly.

// Load the RedBean PHP ORM
require_once( dirname( __FILE__ ) . '/rb.php' );
// Configure the database connection
R::setup('mysql:host='. DB_HOST .';dbname='. DB_NAME , DB_USER, DB_PASSWORD);
R::setAutoResolve( TRUE );

Once code like this is added to the theme’s functions.php file, the RedBeanPHP database connection is established automatically during WordPress initialization. After that, RedBeanPHP can be used freely throughout the application.

Add the WordPress table prefix

By default, RedBeanPHP uses underscores to represent relationships in table names, and WordPress also uses underscores in its database prefix. If you need custom tables that match WordPress naming conventions, it helps to create a small helper extension and define the custom table names as constants.

R::ext('model', function( $type ){
   return R::getRedBean()->dispense( $type );
});

define( 'BOOKS', 'wp_books' );

// When creating records, use model() instead of dispense()
$book = R::model( 'wp_books' );

Create models and store data

One of RedBeanPHP’s nicest features is that it can create table structure automatically. In the following example, the books table does not exist beforehand. We simply create a bean, assign fields, and store it. RedBeanPHP takes care of the underlying structure automatically.

// Create a model
$post = R::dispense( 'books' );
$post->text = 'Hello World';
$post->content = '你好,世界';

// Store data
$id = R::store( $post );

// Delete data
R::trash( $book );

// Load by ID
$books = R::load( 'books', $id );

// Load one row by condition
$book = R::findOne( 'book', ' title = ? ', [ 'SQL Dreams' ] );

// Get all rows
$books = R::findAll( 'book' );
$books = R::findAll( 'book', ' ORDER BY title DESC LIMIT 10 ' );

// Query with multiple conditions
R::find( 'books', ' course_id = ? AND member_id = ? ', [ $course->ID, $member->ID ] );

// Find or create
R::findOrCreate( 'books', [ 'member_id' => 2, 'lesson_id' => 3, 'course_id' => 3 ] );

// Count
$count = R::count( 'book', ' pages > ? ', [ 250 ] );

// Fuzzy matching
R::findLike( 'flower', [
        'color' => ['yellow', 'blue']
   ], ' ORDER BY color ASC ' );

Implement paginated queries

Pagination needs three pieces of information: the total number of records, the number of items per page, and the current page number. The original article uses Nette Paginator to help handle this.

use NetteUtilsPaginator;
$paginator = new Paginator;

// All rows
$all = R::find( PAY, ' pid = ? ', [ 79 ] );

// Current page
$page = ( isset( $_GET['page'] ) ) ? $_GET['page'] : 1;

// Configure pagination
$paginator->setItemCount( count( $all ) );
$paginator->setItemsPerPage( 2 );
$paginator->setPage( $page );

// Get limit and offset
$limit  = $paginator->getLength();
$offset = $paginator->getOffset();

$payments = R::find( PAY, ' pid = ? LIMIT ? OFFSET ?', [ 79, $limit, $offset ] );

With an ORM like this, WordPress application development no longer has to stay limited to the default WordPress tables. You can create dedicated tables for the application’s own data and still work with them in a much more comfortable way than raw SQL.