Imap or database to get access to emails?

[mark107]

Hi all,

I am working on PHP to create a email client using imap. I have got a problem with using imap because it will get very slow so I want to find a way to make it to go faster. I have been researching that I have seen alot of people are using mysql database to store their emails and get access to their emails pretty quick than using imap. And I have also seen that roundcube are using mysql database to store contacts, emails...etc. I am not really sure what to do but I need your advice and I need your opinion.

I have got questions for you. Do you think if I am better off to use imap or use mysql database to get access to my emails pretty quicker?

If you think I am better off to use mysql database, then I want to know how I could store my emails in mysql and do I need to delete the emails in imap after I store the emails in mysql?

Please let me know your opinion so I will decide what to do next.

Thank you.

Edited September 15, 2019 by mark107

[requinix]

If you're fine with your client being the only means to access emails then sure, go ahead and store them in a database. The process depends on your mail server, but generally they have some sort of plugin that would be relevant - run a script when a message arrives, something like that.

If not, or if you don't have control over your mail server, then stick with PHP. You can cache email content in the database in order to reduce the bandwidth between PHP and the IMAP server, but you should still be querying it for basic information (eg, message lists) in case that's modified by some other mail client.

[mark107]

Thank you for your advice. I am going to use database to store my emails to make things easy and quicker, but I have got few problems here.

When I store my emails in a database, do I need to delete the emails on the server?

I have the attachments stored on my server but how I can store the attachments? do i need to store the files name in a database or what?

And when I open my emails, do I need to use imap or database to fetch my emails?

 

[requinix]

7 minutes ago, mark107 said:

When I store my emails in a database, do I need to delete the emails on the server?

Storing the emails in the database does not mean copying them. It means that is the source of emails. There is no IMAP server anymore. There is nothing to delete.

7 minutes ago, mark107 said:

I have the attachments stored on my server but how I can store the attachments? do i need to store the files name in a database or what?

Attachments are not separate files. They're part of the email message itself.

7 minutes ago, mark107 said:

And when I open my emails, do I need to use imap or database to fetch my emails?

...I'm not sure you actually understand what you're talking about anymore.

[ginerjm]

Sounds like an AWFUL LOT OF WORK because "it will get very slow".  Who or what is providing you email services right now?  A shared host? Your own setup?  If the former, have you told them of your concerns?  If your own setup, maybe you need to think about using a different setup. How about switching to  a POP3 account instead of IMAP? 

You are going to have an awful lot of work to do in re-inventing the email wheel here.  I'd certainly try to resolve the issue instead of trying to emulate it.

[mark107]

On 9/15/2019 at 4:33 PM, requinix said:

Storing the emails in the database does not mean copying them. It means that is the source of emails. There is no IMAP server anymore. There is nothing to delete.

Attachments are not separate files. They're part of the email message itself.

...I'm not sure you actually understand what you're talking about anymore.

Oh right, so I don't need to delete the emails after I stored them in a database?

I do understand what I am talking about but you don't understand what I am trying to do.

[requinix]

10 hours ago, mark107 said:

Oh right, so I don't need to delete the emails after I stored them in a database?

No, I'm saying there isn't anything to delete anymore.

Look. IMAP server software is something that runs and gives people on the internet access to the emails stored on the server. The IMAP software does not "have" the emails. It does not possess them. It merely has access to them. The actual place the emails could be stored is absolutely anywhere - files, database, monkeys, whatever - and as long as the IMAP software knows how to get them then you can connect to it and read your email.

So hopefully you are understanding now that the place where the emails and the IMAP software are two different but related things, yes?

What I'm saying about deleting is that the emails, when they're received by the email processing service, go into your database. That is where the emails get stored. The emails get stored immediately in the database. This has absolutely nothing to do with IMAP. IMAP is about accessing the emails. I'm not talking about accessing the emails. I'm talking about where the email messages go the SMTP software receives them.

Okay? So now you have the emails being stored in a database, right?

With them in the database you now have to set up the IMAP service to connect to and read from that database. How you do that depends on the service. Whether you can do that easily or have to install something new or, worst case, have to write some code and/or scripts to do the work because there's no built-in support to do this, depends on the service.

[mark107]

On 9/23/2019 at 7:17 AM, requinix said:

No, I'm saying there isn't anything to delete anymore.

Look. IMAP server software is something that runs and gives people on the internet access to the emails stored on the server. The IMAP software does not "have" the emails. It does not possess them. It merely has access to them. The actual place the emails could be stored is absolutely anywhere - files, database, monkeys, whatever - and as long as the IMAP software knows how to get them then you can connect to it and read your email.

So hopefully you are understanding now that the place where the emails and the IMAP software are two different but related things, yes?

What I'm saying about deleting is that the emails, when they're received by the email processing service, go into your database. That is where the emails get stored. The emails get stored immediately in the database. This has absolutely nothing to do with IMAP. IMAP is about accessing the emails. I'm not talking about accessing the emails. I'm talking about where the email messages go the SMTP software receives them.

Okay? So now you have the emails being stored in a database, right?

With them in the database you now have to set up the IMAP service to connect to and read from that database. How you do that depends on the service. Whether you can do that easily or have to install something new or, worst case, have to write some code and/or scripts to do the work because there's no built-in support to do this, depends on the service.

 

I understand that where you are coming from, look there is no need for you to tell me as I know how it works. When someone send me an email, the imap software will pick up the emails that sent from the sender and the imap software will store the emails for me so I can pick up and read my emails.

Yes I have the emails being stored in a database. The reason I have asked you about the delete emails is I have a small issue right here. When I stored the emails in a database and when I delete them, the script will delete the rows in a database and the script will call imap to check that if the email ids is not exist in the database so it will readd the same emails that I have deleted in a database.

So when I delete the emails in a database, do I need to call imap to delete the emails as well?

 

[requinix]

1 hour ago, mark107 said:

I understand that where you are coming from, look there is no need for you to tell me as I know how it works.

So you claim, but then you turn around and say in the very next sentence

1 hour ago, mark107 said:

When someone send me an email, the imap software will pick up the emails that sent from the sender and the imap software will store the emails for me so I can pick up and read my emails.

something that is factually incorrect.

IMAP software does not receive emails. SMTP software is what does that part of the process. Most of the time mail servers use one software suite to provide all the mail services needed: SMTP, IMAP, sendmail, all of it. (Not necessarily bundled together.) Like on Linux, postfix is more or less the reigning king, so after installing postfix itself (which provides SMTP/mailbox and sendmail functionality) if you wanted IMAP support as well then you'd install a postfix-compatible IMAP service (eg, courier).

1 hour ago, mark107 said:

So when I delete the emails in a database, do I need to call imap to delete the emails as well?

If your emails are being stored in the database in the way that I described, and therefore the IMAP server is loading the emails from the database, IMAP will no longer show those emails because they were deleted.

[mark107]

On 9/25/2019 at 5:45 PM, requinix said:

So you claim, but then you turn around and say in the very next sentence

something that is factually incorrect.

IMAP software does not receive emails. SMTP software is what does that part of the process. Most of the time mail servers use one software suite to provide all the mail services needed: SMTP, IMAP, sendmail, all of it. (Not necessarily bundled together.) Like on Linux, postfix is more or less the reigning king, so after installing postfix itself (which provides SMTP/mailbox and sendmail functionality) if you wanted IMAP support as well then you'd install a postfix-compatible IMAP service (eg, courier).

If your emails are being stored in the database in the way that I described, and therefore the IMAP server is loading the emails from the database, IMAP will no longer show those emails because they were deleted.

Ok, the IMAP software doesn't deleted my emails after I have stored them in a database.

Here is the PHP that I use to fetch my emails and store them in a database.

<?php

///error_reporting(E_ALL);
//error_reporting(-1);
//ini_set('error_reporting', E_ALL);

require_once "Mail.php";
require_once('Mail/IMAPv2.php');

// Initialize the session
session_start();

//Connect to the database
include('config.php');

// Connect to the server:
$username = 'USERNAME';
$password = 'PASSWORD';
$attached_files = array();

// Check if the user is logged in, if not then redirect him to login page
if(!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true) {
    header("location: login.php");
    exit;
}
else
{
    // Connect to the server:
    $username = 'myusername';
    $password = '**********';
    $inbox = '{imap.example.com:993/imap/ssl/novalidate-cert}INBOX';
    $inbox = imap_open($inbox, $username, $password) or die("Can't connect: " . imap_last_error());
    $emailMessageBody = "";
    
    if (PEAR::isError($inbox)) {
        echo "<span style='font-weight: bold;'>Error:</span> Unable to build a connection.";
    }
    else
    {
        $inbox_email = imap_sort($inbox, SORTARRIVAL, 1, SE_UID);

        if($inbox_email !== false) {
            $res_inbox_emails = $inbox_email;
            $html = '';
            $i++;

            foreach($res_inbox_emails as $email_number) {
                //$email_number = 'randomnumbers';
                $structure = imap_fetchstructure($inbox, $email_number, FT_UID);
                $overview = imap_fetch_overview($inbox, $email_number, FT_UID);
                $inbox_from = $overview[0]->from;
                $inbox_to = $overview[0]->to;
                $inbox_subject = quoted_printable_decode($overview[0]->subject);
                $emailMessage = getEmailMessage($inbox, $email_number, $inbox_subject);
                $attachment = getAttachment($emailMessage, $inbox, $email_number);
                $inbox_date = $overview[0]->date;
                $is_spam = imap_fetchheader($inbox, $email_number, FT_UID) . imap_body($inbox, $email_number, FT_UID | FT_PEEK);
                $header = '<pre style="word-wrap: break-word; white-space: pre-wrap;">';
                $header .= htmlspecialchars(imap_fetchbody($inbox, $email_number, "", FT_UID|FT_PEEK));
                $header .= '</pre>';
                $email_encryption = my_simple_crypt($email_number, 'e');
                $readtype = '';
                $inbox_message = '';


                if ($emailMessage->mailtype == 'plain')
                {
                    $inbox_message = $emailMessage->plainText;
                }
                else
                {
                    $inbox_message = $emailMessage->htmlText;
                }


                if (strpos($inbox_message, '=<br>') !== false) {
                    $inbox_message = str_replace('=<br>', '', $inbox_message);
                }


                if (strpos($inbox_subject, '=?UTF-8?B?') !== false) {
                    $inbox_subject = mb_decode_mimeheader($inbox_subject);
                }

                if (strpos($inbox_subject, '=?utf-8?Q') !== false || strpos($inbox_subject, '=?UTF-8?Q') !== false) {
                    $inbox_subject = html_entity_decode(htmlentities(imap_utf8($overview[0]->subject)));
                }

                if (strpos($inbox_subject, '=?windows') !== false) {
                    $inbox_subject = mb_decode_mimeheader($inbox_subject);
                }


                if (strpos($inbox_to, 'undisclosed-recipients') !== false) {
                    $inbox_to_name = str_replace('undisclosed-recipients:', $_SESSION['firstname'], $inbox_to);
                    $inbox_to = $inbox_to_name . ' <'.$_SESSION['email'].'>';
                }


                if (strpos($is_spam, 'X-Spam-Status: Yes') !== false) {
                    imap_mail_move($inbox, $email_number, 'INBOX.spam');
                    imap_expunge($inbox);
                }
                else {

                    if($overview[0]->seen == 0) {
                        $readtype = 'unread';
                    } else {
                        $readtype = 'read';
                    }
                    $inbox_from = str_replace('"', "'", $inbox_from);
                    $inbox_to = str_replace('"', "'", $inbox_to);
                    $inbox_subject = str_replace('"', "'", $inbox_subject);
                    $inbox_dates1 = date('Y-m-d H:i:s', strtotime($inbox_date));

                    $inbox_sql = 'SELECT * FROM inbox WHERE uid = ? AND from_email = ? AND to_email = ? AND subject = ? AND received_date = ?';
                    $inbox_query = $link->prepare($inbox_sql);
                    $inbox_query->execute([$email_number,$inbox_from,$inbox_to,$inbox_subject,$inbox_dates1]); 
                    $check_emails = $inbox_query->fetchAll();
    

                    if ($inbox_query->rowCount() == 0)
                    {
                        $inbox_dates = date('Y-m-d H:i:s', strtotime($inbox_date));
                        $inbox_query1 = $link->prepare("INSERT INTO inbox (uid, from_email, to_email, subject, message, attachments, header, readtype, encryption, received_date) VALUES (?,?,?,?,?,?,?,?,?,?)");
                        //$inbox_query1->execute([$email_number,$inbox_from,$inbox_to,$inbox_subject,$inbox_message,$attachment,$header,$readtype,$inbox_dates]);
                            
                        if ($inbox_query1->execute([$email_number,$inbox_from,$inbox_to,$inbox_subject,$inbox_message,$attachment,$header,$readtype,$email_encryption,$inbox_dates]))
                        {
                            echo "data have been added to the database";
                            echo "<br>";
                        }
                        else
                        {
                            echo "data have failed to add";
                            echo "<br>";
                        }

                        //Close connection
                        $inbox_query1 = null;
                    }

                    //Close connection
                    $inbox_query = null;
                }
            }
        }
    }
}

 

Edited September 27, 2019 by requinix
please don't post login credentials on a public website