In this tutorial I will show how to use RedBeanPHP to easily store Gmail emails retrieved by PHP IMAP. RedBeanPHP supports CUBRID Database so we will use it as a database storage.

Scenario

This tutorial will have several steps.

First, we will make the appropriate preparation.

Install PHP IMAP extension.

Create a CUBRID database where we want to store our emails.

Referring to this database we will setup RedBean and establish a database connection.

Then retrieve all emails from our Gmail account.

INSERT and LOAD: To illustrate how to insert records into a table using RedBean, we will first create a bean (a new table record) and store only one attribute of an email (message_id). Once the bean is created, we will try to load it back from the database based on the returned id property.

Auto CREATE COLUMN and FIND: To show how RedBean automatically adjusts your database schema based on your code, we will add a second email attribute to the existing bean (subject). To find the existing bean, we will show how to find beans from the database.

Save special types: In RedBean Date is a special type. In this step we will add the date attribute of an email to the existing bean.

Step 1: Preparation

Install PHP IMAP

The extension which allows to work with IMAP protocol using PHP is not installed by default. In Ubuntu you can install it from the main repository:

sudo apt-get install php5-imap

On Mac OS X you can use Macports to install PHP IMAP extension:

sudo port install php5-imap

Create a CUBRID Database

This step assumes you have already installed CUBRID. Now, let's create a CUBRID database which we will call imap_redbean. In your terminal type:

cubrid createdb imap_redbean

This will create an imap_redbean database with a user dba and empty password (you can change the password later). Now make sure CUBRID Service is running, and then start the database.

cubrid service start
cubrid server start imap_redbean

Setup RedBean

Download RedBeanPHP. It is a one-file PHP script (rb.php). Extract the archive to find this file.

Now using your favorite source code editor (I use PHPStorm), create a PHP file (eg. imap_redbean.php). Include the rb.php and establish a database connection.

Step 3: Insert and Load using RedBeanPHP

To avoid copying all the above code all over again for every step, I will just provide the working code extracted from the place where it says "HERE COMES THE REDBEAN".

In this step we will demonstrate how to insert records into a table using RedBean, we will first create a bean (a new table record) and store only one attribute of an email message_id. But suffix _id is reserved by RedBeanPHP to handle relationships, we need to store it int messageID columns instead. Once the bean is created, we will try to load the saved record from the database based on the returned id property.

INSERT a record

To create a record we need to call R::dispense() function with a name of the bean, which will become the name of the table where this type of beans will be stored.

$bean = R::dispense('email');

To add columns to our email table, all you have to do is reference an attribute of the bean.

$bean->messageID = $email_overview->message_id;

When you store this record, it will save the value into a column called messageID.

At the end, store the bean.

$id = R::store($bean);

The first time you call store on this type of the bean, ReBean will check whether or not there exists a table called email. If not, it will create one with the columns set as attributes of the $bean object.

LOAD the record

To retrieve a record from a database, we need to call R::load() function passing the bean type name (the table name) as well as the id of the record.

$email = R::load('email', $id);

To confirm that the record has been successfully stored, let's output the messageID we've just stored.

The reason we used htmlentities() PHP function here is that the message_id in Gmail is wrapped in chevrons (<>) like <E1LgAU8-0007Y8-Pb@server.hosting24.com> which must be converted into true HTML character, or they will be interpreted by the browser as tags.

We use R::findOne() function because we are certain that there can be only one unique email with our message_id.

If we do not find such an email, we will create a new bean for it. As we have found our email, let's assign a new column to store the subject of the email.

$bean->subject = $email_overview->subject;

When we store this bean, it will alter our email table and add a new column called subject. See the below schema generated for our table. See how correct it is. Later we have a chance to manually adjust the schema if necessary, for example by setting the maximum length for messageID or subject.

However, there is one limitation on this matter in RedBeanPHP. It recognizes so far only YYY-MM-DD format for DATE and YY-MM-DD HH:MM:SS format for DATETIME. If you enter dates in other format, eg. separated with a slash, the column will be stored as VARCHAR. I hope in the future, RedBean will support other formats as well, because in CUBRID, users can omit some parts in DATE and DATETIME data types.

Conclusion

As you can see, using RedBean as an ORM for your prototype development makes the work go much faster. You no longer have to prepare your database schema (how do you know what it will look like?). You focus on the actual application development. Once you are done developing and ready to go production, you may freeze RedBean which will no longer make any changes to the database schema. Very simple, very convenient!

In this tutorial I haven't covered many aspects of RedBeanPHP but I hope this tutorial will give you a good place to start. Below you can find the full code of the example we have created in this tutorial.