Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Retrieve and Store Gmail Emails Using PHP IMAP and RedBeanPHP


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.

  1. First, we will make the appropriate preparation.
    1. Install PHP IMAP extension.
    2. Create a CUBRID database where we want to store our emails.
    3. Referring to this database we will setup RedBean and establish a database connection.
  2. Then retrieve all emails from our Gmail account.
  3. 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.
  4. 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.
  5. 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.

<?php
    /* first, we need to include redbean */
    require_once('rb.php');

    /* now connect to a CUBRID database */
    R::setup('cubrid:host=localhost;port=30000;dbname=imap_redbean', 'dba', '');
?>

Step 2: Retrieve all emails from Gmail

<?php
    /* first, we need to include redbean */
    require_once('rb.php');

    /* now connect to a CUBRID database */
    R::setup('cubrid:host=localhost;port=30000;dbname=imap_redbean', 'dba', '');

    /* connect to gmail */
    $hostname = '{imap.gmail.com:993/imap/ssl/novalidate-cert}INBOX';
    $username = 'mygmailusername';
    $password = 'mygmailpassword';

    /* try to connect */
    $inbox = imap_open($hostname, $username, $password) or die('Cannot connect to Gmail: ' . imap_last_error());

    /* grab ALL emails */
    $emails = imap_search($inbox, 'ALL');

    /* if emails are returned, cycle through each... */
    if($emails)
    {
        /* for every email... */
        foreach($emails as $email_number)
        {
            /*  get information specific to this email
                returns an array with the following properies:
                subject, from, to, date, message_id, references,
                in_reply_to, size, uid, msgno, recent, flagged, answered,
                deleted, seen, draft, and udate.
            */
            $overview = imap_fetch_overview($inbox, $email_number);
            $email_overview = &$overview[0];

            /*********************************************************
            ********** HERE COMES THE REDBEAN ************************
            *********************************************************/
        }
    }

    /* to make sure the script was run */
    echo 'done';

    /* close the connection */
    imap_close($inbox);

?>

 

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.

echo $id . ' -> messageID: ' . htmlentities($email->messageID)
         . '<br /><br />';

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.

To glue everything up, here is the code:

/* insert an email bean with only a message_id */
$bean = R::dispense('email');
$bean->messageID = $email_overview->message_id;

/* save the bean */
$id = R::store($bean);

/* reload the bean */
$email = R::load('email', $id);

/* display the messageID */
echo $id . ' -> messageID: ' . htmlentities($email->messageID)
        . '<br /><br />';

This will generate the following table schema:

CREATE TABLE "email"(
      "id" integer AUTO_INCREMENT,
      "messageID" character varying(1073741823),
      CONSTRAINT pk_email_id PRIMARY KEY("id")
);

 

Step 4: Auto create columns and Find

Now we will see how RedBean automatically adjusts our database schema based on our code. We will add a second email attribute to the existing bean (subject). First, we need to find the existing bean.

$bean = R::findOne('email', ' messageID = :messageID', array(':messageID'=>$email_overview->message_id));

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.

CREATE TABLE "email"(
      "id" integer AUTO_INCREMENT,
      "messageID" character varying(1073741823),
      "subject" character varying(1073741823),
      CONSTRAINT pk_email_id PRIMARY KEY("id")
);

To confirm that everything has been stored correctly, we will load the bean again based on its id. Here is the code for this step.

/* check if an email with such message_id already exists */
$bean = R::findOne('email', ' messageID = :messageID', array(':messageID'=>$email_overview->message_id));

/* if not, create one */
if (!$bean)
{
      /* insert an email bean with only a message_id */
      $bean = R::dispense('email');
      $bean->messageID = $email_overview->message_id;
}

/* now assign the subject for this email bean */
$bean->subject = $email_overview->subject;

/* save the bean */
$id = R::store($bean);

/* reload the bean */
$email = R::load('email', $id);

/* display the messageID and the subject */
echo
      $id . ' -> messageID: ' . htmlentities($email->messageID)
      . '<br />subject: ' . $email->subject
      . '<br /><br />';

 

Step 5: Save Special Types

In RedBean Date, Datetime, and Spatial types are considered as special types. In this step we will add the date attribute of an email to the existing bean and see how RedBean handles this.

/* check if an email with such message_id already exists */
$bean = R::findOne('email', ' messageID = :messageID', array(':messageID'=>$email_overview->message_id));

/* if not, create one */
if (!$bean)
{
    /* insert an email bean with only a message_id */
    $bean = R::dispense('email');
    $bean->messageID = $email_overview->message_id;

    /* now assign the subject for this email bean */
    $bean->subject = $email_overview->subject;
}

/* now assign the date for this email bean */
$bean->date = date('Y-m-d H:i:s', $email_overview->udate);

/* save the bean */
$id = R::store($bean);

/* reload the bean */
$email = R::load('email', $id);

/* display the messageID, the subject, and the date */
echo
    $id . ' -> messageID: ' . htmlentities($email->messageID)
    . '<br />subject: ' . $email->subject
    . '<br />date: ' . $email->date
    . '<br /><br />';

After we store the date attribute, we've got the following schema. Notice that the date column has been stored as DATE type.

CREATE TABLE "email"(
      "id" integer AUTO_INCREMENT,
      "messageID" character varying(1073741823),
      "subject" character varying(1073741823),
      "date" date,
      CONSTRAINT pk_email_id PRIMARY KEY("id")
);

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.

<?php
    /* first, we need to include redbean */
    require_once('rb.php');

    /* now connect to a CUBRID database */
    R::setup('cubrid:host=localhost;port=30000;dbname=imap_redbean', 'dba', '');

    /* connect to gmail */
    $hostname = '{imap.gmail.com:993/imap/ssl/novalidate-cert}INBOX';
    $username = 'mygmailusername';
    $password = 'mygmailpassword';

    /* try to connect */
    $inbox = imap_open($hostname, $username, $password) or die('Cannot connect to Gmail: ' . imap_last_error());

    /* grab ALL emails */
    $emails = imap_search($inbox, 'ALL');

    /* if emails are returned, cycle through each... */
    if($emails)
    {
        /* for every email... */
        foreach($emails as $email_number)
        {
            /*  get information specific to this email
                returns an array with the following properies:
                subject, from, to, date, message_id, references,
                in_reply_to, size, uid, msgno, recent, flagged, answered,
                deleted, seen, draft, and udate.
            */
            $overview = imap_fetch_overview($inbox, $email_number);
            $email_overview = &$overview[0];

            /*********************************************************
            ********** HERE COMES THE REDBEAN ************************
            *********************************************************/

            /* check if an email with such messageID already exists */
            $bean = R::findOne('email', ' messageID = :messageID', array(':messageID'=>$email_overview->message_id));

            /* if not, create one */
            if (!$bean)
            {
                /* insert an email bean with only a messageID */
                $bean = R::dispense('email');
                $bean->messageID = $email_overview->message_id;

                /* now assign the subject for this email bean */
                $bean->subject = $email_overview->subject;
            }

            /* now assign the date for this email bean */
            $bean->date = date('Y-m-d H:i:s', $email_overview->udate);

            /* save the bean */
            $id = R::store($bean);

            /* reload the bean */
            $email = R::load('email', $id);

            /* display the messageID, the subject, and the date */
            echo
                $id . ' -> messageID: ' . htmlentities($email->messageID)
                . '<br />subject: ' . $email->subject
                . '<br />date: ' . $email->date
                . '<br /><br />';
        }
    }

    /* to make sure the script was run */
    echo 'done';

    /* close the connection */
    imap_close($inbox);

?>
comments powered by Disqus
Page info
viewed 11125 times
translations en
Author
posted 2 years ago by
CUBRID
Contributors
updated 11 months ago by
View revisions
Share this article