Mixing SQL and PHP

In RedBeanPHP you can mix PHP and SQL as if it were just one language. To call an SQL function in PHP simply call it like a PHP function on R::$f. $f is short for 'function' and we mean SQL function here. Here are some examples:

Besides simple SQL functions like now() you can build queries that blend perfectly with your PHP code. This is useful for writing complex dynamic queries. This technique allows you to share queries among functions and methods just like a query builder does. The only difference is, you don't need to learn a new syntax, it's plain old SQL!

This PHP code is the equivalent of:

There are just a couple of rules.
First, you must begin a PHP-SQL query that is longer than just one SQL function with the begin() method. This method prepares the SQL helper for a query. It turns on capture mode; from that moment on it will postpone execution of the query until it's complete.
Now you can chain SQL statements as PHP function calls. To add a value to the parameter list use put(), finally you must end the query with get(), get('row'), get('col') or get('cell') depending on the desired return format. These methods are similar to the default database adapter methods found elsewhere in RedBeanPHP. Thus get() will return a multidimensional array with each row containing an associative array (column=>value), while get('row') returns just one such row, get('col') returns a flat array containing the column values and get('cell') returns a single value.

To add a '(' in a query use: open(), to add a ')' use: close(), like so:

To add a literal piece of SQL use addSQL():

Since RedBeanPHP 3.2: Underscores will be replaced by spaces.

Nesting Queries

You can also build queries out of multiple nested queries:

Simply use the getNew() method to obtain a new instance of the query builder using the same adapter as the current one. Then wrap this query in the nest() method of the parent query. The SQL and parameters of the nested query will be added to the parent query.

Nesting query builders has been implemented as of version 3.4

RedBeanPHP will not generate portable SQL for you. The RedBeanPHP Query Builder has been designed to share queries among functions without having to deal with strings, not to generate database agnostic SQL.

By now, you know how to perform CRUD and find beans, you probably want to learn to map relations among beans. In RedBeanPHP relations are mapped using lists. Instead of thinking about tables and fields you just put beans in a list and they will belong to the owner of the list. This is similar to what is known as 'one-to-many' or '1-N' but easier. Read more about relational mapping using lists.