Fetch the images filename and post

[mark107]

Hi all,

I am working on my PHP script to open my emails by fetching the data from mysql database. When I open my emails I want to fetch the filename data next to `alt` in the img tag, example:  what-is-bootstrap.png so I can then send the filename data to `/project/u/index` to display the images.

Here is what my image will show as for example:

     <img src="/project/u/?id=47394f41507856374130672f6d65534430734f7177513d3d&attid=0.2&msgid=1630808059112201633&view=attachment&display=view" alt="what-is-bootstrap.png" width="452" height="302" class="gmail-a6T" tabindex="0">

Here is the index.php code:

   

<?php
    
    //ini_set('display_errors', 1);
    //ini_set('display_startup_errors', 1);
    //error_reporting(E_ALL);
    
    require_once "Mail.php";
    require_once('Mail/IMAPv2.php');
    
    $key = "**************";
    $email_number = openssl_decrypt(hex2bin($_GET['id']),'AES-128-CBC', $key);
    $attach_id = $_GET['attid'];
    $attachment_display = $_GET['display'];
    
    $attachments = array();
    $body_attachments = array();
    $attachment_number = 0;
    $inline_number = 0;
    $body_number = 0;
    
    
    if ($attachments >= 1) {
        
        if (($attach_id == 0) or ($attach_id > 0.9))  {
    
            if($attachments[$attach_id])
            {
                $filename = $attachments[$attach_id]['name'];
                $filePath = "attachments/" .$filename;
    
                if($attachment_display == 'download') {
                    header("Content-Type: application/octet-stream");
                    header("Content-Transfer-Encoding: Binary");
                    header("Content-disposition: attachment; filename=".basename($filename));
                    echo readfile($filePath);
                }
                else if($attachment_display == 'view') {
                    $type = 'image/jpeg';
                    header('Content-Type:'.$type);
                    header('Content-Length: ' . filesize($filePath));
                    readfile($filePath);
                }
            }
        }
    }
    
    if ($body_attachments >= 1) {
    
        if (($attach_id >= 0.1) && ($attach_id <= 0.9))  {
    
            if ($attach_id == '0.1') {
                $attach_id = 0;            
            }
            else if ($attach_id == '0.2') {
                $attach_id = 1;            
            }
            else if ($attach_id == '0.3') {
                $attach_id = 2;            
            }
            else if ($attach_id == '0.4') {
                $attach_id = 3;            
            }
            else if ($attach_id == '0.5') {
                $attach_id = 4;            
            }
            else if ($attach_id == '0.6') {
                $attach_id = 5;            
            }
            else if ($attach_id == '0.7') {
                $attach_id = 6;            
            }
            else if ($attach_id == '0.8') {
                $attach_id = 7;            
            }
            else if ($attach_id == '0.9') {
                $attach_id = 8;            
            }
            else if ($attach_id == '0.10') {
                $attach_id = 9;            
            }
            else if ($attach_id == '0.11') {
                $attach_id = 10;            
            }
            else if ($attach_id == '0.12') {
                $attach_id = 11;            
            }
            else if ($attach_id == '0.13') {
                $attach_id = 12;            
            }
            else if ($attach_id == '0.14') {
                $attach_id = 13;            
            }
            else if ($attach_id == '0.15') {
                $attach_id = 14;            
            }
            else if ($attach_id == '0.16') {
                $attach_id = 15;            
            }
            else if ($attach_id == '0.17') {
                $attach_id = 16;            
            }
            else if ($attach_id == '0.18') {
                $attach_id = 17;            
            }
            else if ($attach_id == '0.19') {
                $attach_id = 18;            
            }
            else if ($attach_id == '0.20') {
                $attach_id = 19;            
            }
            else if ($attach_id == '0.21') {
                $attach_id = 20;            
            }
            else if ($attach_id == '0.22') {
                $attach_id = 21;            
            }
            else if ($attach_id == '0.23') {
                $attach_id = 22;            
            }
            else if ($attach_id == '0.24') {
                $attach_id = 23;            
            }
            else if ($attach_id == '0.25') {
                $attach_id = 24;            
            }
            else if ($attach_id == '0.26') {
                $attach_id = 25;            
            }
            else if ($attach_id == '0.27') {
                $attach_id = 26;            
            }
            else if ($attach_id == '0.28') {
                $attach_id = 27;            
            }
            else if ($attach_id == '0.29') {
                $attach_id = 28;            
            }
            else if ($attach_id == '0.30') {
                $attach_id = 29;            
            }
            
            
            if(!$body_attachments[$attach_id]) {
                $attach_id++;
            }
    
    
            if($body_attachments[$attach_id])
            {
                $filename = $body_attachments[$attach_id]['name'];
                $filePath = "attachments/" .$filename;
    
                if($attachment_display == 'download') {
                    header("Content-Type: application/octet-stream");
                    header("Content-Transfer-Encoding: Binary");
                    header("Content-disposition: attachment; filename=".basename($filename));
                    echo readfile($filePath);
                }
                else if($attachment_display == 'view') {
                    $type = 'image/jpeg';
                    header('Content-Type:'.$type);
                    header('Content-Length: ' . filesize($filePath));
                    readfile($filePath);
                }
            }
        }
    }
    
    ?>

Here is what I use to open my emails using database:

   

<?php
    
    // Initialize the session
    session_start();
    
    //Connect to the database
    include('config.php');
    
    // 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
    {
        $id = $_POST['email_number'];
        $mailfolder = $_POST['mailfolder'];
        $readMail = 'read';
        $mailbox = $link->prepare("UPDATE " . $mailfolder . " SET readtype = ? WHERE id = ?");
        $mailbox->execute([$readMail, $id]);
        
        // set the resulting array to associative
        $row = $mailbox->fetch(PDO::FETCH_ASSOC);
        if($mailbox->rowCount() == 1)
        {
            $results = array();
            $mailbox_from = $row['from_email'];
            $mailbox_from_ = explode('<', $mailbox_from);
            $mailbox_from_name = $mailbox_from_[0];
            $mailbox_from_email = '<' . $mailbox_from_[1];
            $mailbox_from_email = htmlspecialchars($mailbox_from_email);
            $mailbox_to = $row['to_email'];
            $mailbox_to_name = $mailbox_to;
            $mailbox_subject = quoted_printable_decode($row['subject']);
            $mailbox_fulldate = $row['received_date'];
            $mailbox_date = convDate($mailbox_fulldate);
            $mailbox_message = $row['message'];
            $mailbox_attachment = $row['attachments'];
            $mailbox_folder = $_POST['folder'];
            $total = $_POST['total'];
            $email_encrypt = $row['encryption'];
            $send_to = $mailbox_from_email;
    
            $results['mailbox_from'] = $mailbox_from;
            $results['mailbox_from_'] = $mailbox_from_;
            $results['mailbox_from_name'] = $mailbox_from_name;
            $results['mailbox_from_email'] = $mailbox_from_email;
            $results['mailbox_to'] = htmlspecialchars($mailbox_to);
            $results['mailbox_subject'] = $mailbox_subject;
            $results['mailbox_fulldate'] = $mailbox_fulldate;
            $results['mailbox_date'] = $mailbox_date;
            $results['mailbox_message'] = $mailbox_message;
            $results['mailbox_attachment'] = $mailbox_attachment;
            $results['mailbox_folder'] = $mailbox_folder;
            $results['total'] = $total;
            $results['email_encrypt'] = $email_encrypt;
            $results['send_to'] = $send_to;
            $results['time_elapsed'] = $time_elapsed;
            $results['mailed_by_date'] = $mailed_by_date;
            $results['mailed_by'] = $mailed_by;
            $results['mailbox_to_name'] = $mailbox_to_name;
            $results['attach_test'] = $attach_test;
            $data = array("start"=>$start_idx,"total"=>$total,"encryption"=>$email_encrypt);
            $response = array("data"=>$data,"html"=>$html, "total_inbox"=>$total_inbox_unread, "total_draft"=>$total_draft_unread, "total_spam"=>$total_spam_unread, "emailBodyMessage"=>$results, "emailHeader"=>$header);
            echo json_encode($response);
        }
    }
    
    //Close connection
    $mailbox = null;
    ?>

 

I want to fetch the filename data next to `alt` so I can send the data to /project/u/index.php to display the image.

I dont know how I could do this using PHP.  Can you please show me how I could do this?

Thanks in advance.

[requinix]

Well you can't get the alt from the <img> tag itself. Your script will have to get the filename from, presumably, the same place that the code filling in the alt got it from.

[Zane]

I don't understand from your code: Which part contains the image tag?  Or, do you just have a column for filenames or something and want to create a relative URL for it?

[mark107]

1 hour ago, Zane said:

I don't understand from your code: Which part contains the image tag?  Or, do you just have a column for filenames or something and want to create a relative URL for it?

What you mean by which part contains the image tag? i am only using the image tag like <img where you can see in the attached pics.

No I dont have a column for filenames but I have a column called attachments that I have stored the html tags in a database with the filename like this:

<a id='download_attachment' role='link' class='aQy e' href='http://example.com/project433q325/u/?id=71664b6a654a6176434c346c773348337054733779413d3d&attid=0&msgid=1630808059112201633&view=attachment&display=download' data-tooltip-align='t,c' data-tooltip-class='a1V' tabindex='0' ><div aria-hidden='true'><div class='aSH'><div class='attachment_top'><div class='attachment_center' style='height: 115px;'><img id='0' class='attachment_image' src='http://example.com/project433q325/u/?id=71664b6a654a6176434c346c773348337054733779413d3d&attid=0&msgid=1630808059112201633&view=attachment&display=view' alt=noname.gif></div></div></div></div><div class='aSI'><div id='q5' class='aSJ' style='border-color: #fff'></div></div></a><a id='download_attachment' role='link' class='aQy e' href='http://example.com/project433q325/u/?id=71664b6a654a6176434c346c773348337054733779413d3d&attid=1&msgid=1630808059112201633&view=attachment&display=download' data-tooltip-align='t,c' data-tooltip-class='a1V' tabindex='0' ><div aria-hidden='true'><div class='aSH'><div class='attachment_top'><div class='attachment_center' style='height: 115px;'><img id='1' class='attachment_image' src='http://example.com/project433q325/u/?id=71664b6a654a6176434c346c773348337054733779413d3d&attid=1&msgid=1630808059112201633&view=attachment&display=view' alt=what-is-bootstrap.png></div></div></div></div><div class='aSI'><div id='q5' class='aSJ' style='border-color: #fff'></div></div></a>

 

I guess i might have to use jquery to do this job??

[requinix]

Okay, then your first problem is that you're storing the attachments as HTML in your database.

Don't do that. You see how hard it is to work with those, right?

Store the individual pieces of information about each attachment. The message ID, attachment ID, name, whatever.

[mark107]

10 minutes ago, requinix said:

Okay, then your first problem is that you're storing the attachments as HTML in your database.

Don't do that. You see how hard it is to work with those, right?

Store the individual pieces of information about each attachment. The message ID, attachment ID, name, whatever.

Yep, I am storing the attachments as HTML in a database. Why I don't do that?

Do I have to remove the HTML tags and store the filenames, message-id, attach-id in a database just like this?

Quote

 

message-id: 4124 - attach-id: 5768 filename: what-is-bootstrap.png

message-id: 9041 - attach-id: 1690 filename: noname.gif

 

How I can make the html tags?

Do I have to make the html tags when I click on my emails to fetch the message-id, attach-id and filename from the database to make the html tags to put them on my page?
 

[requinix]

9 minutes ago, mark107 said:

Yep, I am storing the attachments as HTML in a database. Why I don't do that?

Because extracting the filename, and other information, from that HTML is really really painful. And who's to say you want that exact markup all the time? What if you need to change it to something different?

The concept we're touching on here is separating data from presentation. Attachment IDs and filenames are data, DIVs and IMGs are presentation. The data comes from an email and had nothing to do with HTML, and HTML can quite happily exist without dealing with your attachments. They are two different things and need to be kept separate. What you do later is use HTML to show the attachments.

9 minutes ago, mark107 said:

Do I have to remove the HTML tags and store the filenames, message-id, attach-id in a database just like this?

Basically, yes. When you want that HTML on your page you output it at that moment - the same way you're already doing for everything else.

9 minutes ago, mark107 said:

How I can make the html tags?

I... don't know why this is a question. Surely you've written PHP code for a webpage? You know, HTML tags here, PHP tags there, assorted echo statements...

9 minutes ago, mark107 said:

Do I have to make the html tags when I click on my emails to fetch the message-id, attach-id and filename from the database to make the html tags to put them on my page?

...What?

[mark107]

2 minutes ago, requinix said:

Because extracting the filename, and other information, from that HTML is really really painful. And who's to say you want that exact markup all the time? What if you need to change it to something different?

The concept we're touching on here is separating data from presentation. Attachment IDs and filenames are data, DIVs and IMGs are presentation. The data comes from an email and had nothing to do with HTML, and HTML can quite happily exist without dealing with your attachments. They are two different things and need to be kept separate. What you do later is use HTML to show the attachments.

Basically, yes. When you want that HTML on your page you output it at that moment - the same way you're already doing for everything else.

I... don't know why this is a question. Surely you've written PHP code for a webpage? You know, HTML tags here, PHP tags there, assorted echo statements...

...What?

Oh I see, I can see I have make a mistake so I will need to change the code again. I am just learning what I must do and what I must do as I am doing this for the first time never done this before.

So when I store the message-id, attach-id and filename in a database, how  I can get the images to display in index.php when I click to open my emails?

[Zane]

What you do is maintain a normalized database.  In other words, have a table for anything that you'd consider to be an important chunk of data.  For instance, attachments.  Ideally, you'd have an attachments table in your database.  Within this table, you want to put as much about each attachment as you can, as columns (table fields), with the ultimate goal being to have only one single unique row per attachment.  One attachment cannot be another.

Here's a rundown of what I think you're looking at for a table schema.

• id - you need a primary unique identifier for every attachment
• email - Which email is this an attachment of?  You'd put an id in here that correlates to a specific email in another table called "emails".
• image - Again, images are a good example of another good chunk of data.  So, put an id of an image from another table called "images".  This way you don't restrict images just to attachments.  Images lives matter too.  You could store everything about every image on your site in this table.  You can even give it a label to use for whatever reason you'd need a name for the image that isn't the filename.
• filename - Obviously, this would just be a string containing the filename, and the filename only.  You might decide to change your URL structure one day, or reorganize folders, so just keep the filename.

That's probably the most fundamental minimum schema you'd have.  Then, you have the emails and the images to store in their own separate tables.  Again, bare minimum:

Emails Table

• id - primary unique identifier for the email.
• user_id - Assuming you have a user management system set up, I'm willing to bet there is a unique identifier for every user.  There's another table worth creating!
• destination_address - What is the email address this message was sent to?
• body - What is the message body of this email?
• sent - Is this message sent?  Blink once for yes and zero times for no -- That's binary.
• send_date - When was this message sent? Use a UNIX timestamp so you can output the date precisely as you want to.  Now, and in the future when you make decisions about change and stuff.

For something like the "body" of an email, it's probably one of the few times it'd actually be logical to store HTML (that is encoded).  It's not likely you're going to change the way an email looks that has been sent.  For example, say you're on a mailing list for some site -- Site A.  If, for whatever reason, Site A decides to change their mailing list format that is sent out, you'd still be able to look at older emails from the mailing list and see what they've changed.

Then, when all the proper SQL queries have been executed by a server side language, such as PHP, you can use that same language (PHP) to dynamically generate HTML.  Need a dropdown showing a list of all users?  Create a function that'll generate a <select> tag with <option> tags for each user.  Then, you just slap that sucker in whatever HTML you have set up and pretty it up with CSS.

<div id="attachments">

     <div id="attachment_{$attachment_id}">

          <a href="{$attachment_filename}"><img src="somefolder/images/{$image_filename}" /></a>

     </div>

</div>

Where {$variable} is one of your PHP variables that contains the data you need.

Look up Database Normalization on Google.  Get a feel for how data should be treated.  This is a decent article on it.

[mark107]

16 hours ago, Zane said:

What you do is maintain a normalized database.  In other words, have a table for anything that you'd consider to be an important chunk of data.  For instance, attachments.  Ideally, you'd have an attachments table in your database.  Within this table, you want to put as much about each attachment as you can, as columns (table fields), with the ultimate goal being to have only one single unique row per attachment.  One attachment cannot be another.

Here's a rundown of what I think you're looking at for a table schema.

• id - you need a primary unique identifier for every attachment
• email - Which email is this an attachment of?  You'd put an id in here that correlates to a specific email in another table called "emails".
• image - Again, images are a good example of another good chunk of data.  So, put an id of an image from another table called "images".  This way you don't restrict images just to attachments.  Images lives matter too.  You could store everything about every image on your site in this table.  You can even give it a label to use for whatever reason you'd need a name for the image that isn't the filename.
• filename - Obviously, this would just be a string containing the filename, and the filename only.  You might decide to change your URL structure one day, or reorganize folders, so just keep the filename.

That's probably the most fundamental minimum schema you'd have.  Then, you have the emails and the images to store in their own separate tables.  Again, bare minimum:

Emails Table

• id - primary unique identifier for the email.
• user_id - Assuming you have a user management system set up, I'm willing to bet there is a unique identifier for every user.  There's another table worth creating!
• destination_address - What is the email address this message was sent to?
• body - What is the message body of this email?
• sent - Is this message sent?  Blink once for yes and zero times for no -- That's binary.
• send_date - When was this message sent? Use a UNIX timestamp so you can output the date precisely as you want to.  Now, and in the future when you make decisions about change and stuff.

For something like the "body" of an email, it's probably one of the few times it'd actually be logical to store HTML (that is encoded).  It's not likely you're going to change the way an email looks that has been sent.  For example, say you're on a mailing list for some site -- Site A.  If, for whatever reason, Site A decides to change their mailing list format that is sent out, you'd still be able to look at older emails from the mailing list and see what they've changed.

Then, when all the proper SQL queries have been executed by a server side language, such as PHP, you can use that same language (PHP) to dynamically generate HTML.  Need a dropdown showing a list of all users?  Create a function that'll generate a <select> tag with <option> tags for each user.  Then, you just slap that sucker in whatever HTML you have set up and pretty it up with CSS.

<div id="attachments">

     <div id="attachment_{$attachment_id}">

          <a href="{$attachment_filename}"><img src="somefolder/images/{$image_filename}" /></a>

     </div>

</div>

Where {$variable} is one of your PHP variables that contains the data you need.

Look up Database Normalization on Google.  Get a feel for how data should be treated.  This is a decent article on it.

Thank you for your advice. I have been modified on the code to add the attid, message-id and the filename so am I good to go?

Here is what it look like:

1562453336 attid: 0.1 msessage-id: 455a7a38554a364e577233426974544a4c49397359513d3d filename: noname.gif 1562453336 attid: 0.2 msessage-id: 455a7a38554a364e577233426974544a4c49397359513d3d filename: what-is-bootstrap.png --------------------------Image body

1562023807 attid:0 msessage-id: 655337787a6b7957356e316468667431736d415944673d3d filename: chromegmailnew-vertical-allowed-1366.png 1562023807 attid:1 msessage-id: 655337787a6b7957356e316468667431736d415944673d3d filename: example.png --------------------------test attachment 11

1562014561 attid:0 msessage-id: 706b337a314d6b30563352484d615461764a594461513d3d filename: example.png 1562014561 attid:1 msessage-id: 706b337a314d6b30563352484d615461764a594461513d3d filename: chromegmailnew-vertical-allowed-1366.png --------------------------Test attachments no 9

1562013859 attid:0 msessage-id: 77634b38635a39596a6b56465a314e4d574531776b773d3d filename: chromegmailnew-vertical-allowed-1366.png 1562013859 attid:1 msessage-id: 77634b38635a39596a6b56465a314e4d574531776b773d3d filename: chromegmailnew-vertical-allowed-1366.png 1562013859 attid:2 msessage-id: 77634b38635a39596a6b56465a314e4d574531776b773d3d filename: example.png 1562013859 attid:3 msessage-id: 77634b38635a39596a6b56465a314e4d574531776b773d3d filename: example.png --------------------------Test attachments no 6

 

I think it would be easier if I put them in the attachments column with attid, message-id and filename so I can find the filename I am looking for using attid following by id that I search through in a database. Please let me know if I am doing it correct so I will insert these data to be storing in a database.

However, I have been told that it would not be a good idea to store the images and attachments in a database so I would need to use fwrite to store the data on my server.

Here is the Email Table:

• id - I use INIT to store my name and email address
• uid - I use TEXT to store emails uid
• from_email - I use TEXT to store the name and sender email address
• to_email - I use TEXT to store my name and email address
• subject - I use TEXT to store the subject
• message - I use BLOB to store the messages in HTML
• attachments - I use BLOB to store the attachments in HTML
• header - I use BLOB to store the header as HTML
• readtype - I use TEXT to store the text as read/unread
• received_date - I use datetime to store the date and time when they sent the emails

 

If you find anything wrong with my database or what changes I would need to make so please let me know.

I have storing the email body as HTML in a database so am I good to go?

As for the other file index.php, you can see in my code that I am using the variables to get the id, attid and messageid from the address bar. I guess that if I want to find the images to display, I would have to use `$_GET['id']` to get the id from the address bar to find it through in a database row for that email, then I would have to use `$_GET['attid']` to find the string for the attid in the attachments column to get the strings for the filename so I could link it to where the files are located on my server. Am I correct? 

[ginerjm]

I have to ask - Why are you even DOING this thing with storing html in a db?  One stores DATA in a db.  Then one reads it and sends it to the client or processes it in a script and sends the results of that to the client.  Saving html code in a db is mind-boggling to me.  Never done it.  Probably never will.  As a beginner - why are you making this project so completely complicated by coming up with this idea??  Did you see a recommendation that told you to do it?

[maxxd]

21 minutes ago, ginerjm said:

Saving html code in a db is mind-boggling to me.

Not to be a pedantic ass, but sometimes the data is client-created HTML content. Any CMS runs into this, and a lot of application-style sites I've worked on (including my current job) offer clients the option of customizing display text where it makes sense, and it can include lists and images and whatnot.

[mark107]

2 hours ago, ginerjm said:

I have to ask - Why are you even DOING this thing with storing html in a db?  One stores DATA in a db.  Then one reads it and sends it to the client or processes it in a script and sends the results of that to the client.  Saving html code in a db is mind-boggling to me.  Never done it.  Probably never will.  As a beginner - why are you making this project so completely complicated by coming up with this idea??  Did you see a recommendation that told you to do it?

No one have told me to do it. I have got emails that come with images in the HTML so I'm storing them in a db. I have got no idea how to store the DATA in a db when I'm fetching my emails using imap.

[mark107]

2 hours ago, maxxd said:

Not to be a pedantic ass, but sometimes the data is client-created HTML content. Any CMS runs into this, and a lot of application-style sites I've worked on (including my current job) offer clients the option of customizing display text where it makes sense, and it can include lists and images and whatnot.

Ok, I have come across to this post

https://stackoverflow.com/questions/37338770/php-how-to-save-html-string-into-database

 

It say that I can encode the HTML to store the hash in a database and when I get the hash I can decode them to go it back to where it was. Do you think if it is a good idea to do this to encode the HTML so I can store the hash in a database?

Edited September 28, 2019 by mark107

[Zane]

The more you reply with details the more it makes me understand that you're trying to extract the images from incoming HTML.  Am I correct on this?

39 minutes ago, mark107 said:

I have got emails that come with images in the HTML so I'm storing them in a db. I have got no idea how to store the DATA in a db when I'm fetching my emails using imap.

 

[mark107]

Just now, Zane said:

The more you reply with details the more it makes me understand that you're trying to extract the images from incoming HTML.  Am I correct on this?

Yes.

Can you also answer my questions regards to db that if it is a good idea to encode the HTML to store the hash in a db?

[Zane]

Well, then, if that's the case,  you can use regular expressions to extract the value from the alt tags of the image tags.

/<img.*alt=["|']([^"']+)["|']/gm

https://regex101.com/r/26vszE/1

Though, that regex won't work right if the filename has an apostrophe or double quotes.  Anyway, that's what I'd use to extract all of the image filenames in the alt tags of incoming HTML.  Storing the incoming HTML in a BLOB column shouldn't be an issue.  Store it encoded and decode it when you take it out.  Not hash it.  Hashing is another thing altogether.