News-Issues Data Base

I started emailing myself newspaper articles of interest to me in 2020 and flagging the subject line with an "I:".  I then from time to time moved these flagged emails from my inbox to a folder called "Issues". 

From there the Issues folder can be parsed to remove all the email crap (technical term) leaving a // delimited csv file containing Subject//Date//Link.  This parsed file can then be copied to my web server where it is inserted into a sqlite database.

Select I:  on subject in email program

Move all I: to Issues folder

Copy the folder to Issues directory on the mail server

cd Issues
cp ~deid/mail/Issues .

Mail files

A litle bit about mail file formats.
mbox or maildir.  I am currently using sendmail and mbox.

All incoming mail goes into a single file in (for me) /var/mail/deid.
If I create more folders in addition to the inbox, each folder is a file in /home/deid/mail.  So in my case all the messages with subject of I: ... are moved into /home/deid/mail/Issues when I collect them.  I now have a single file containing about 2 years of collected messages. 

-rw------- 1 deid deid 17M Sep 23 16:02 Issues

Parse out the Subject, Date, Link

./ Issues > parse
Parse Source Code

Copy the parsed file from my mail server to to my web server.  sshfs - makes it easy to move data from one server to another.

cp fibble/Issues/parsed island/Issues/

Now we need a sqlite DB  - (1 time)

php makedb.php

Make DB Source Code

Make the table in the DB that will contain the issues information: Subject, Date, Link

php maketable.php issuesdb

Make Table Source Code

Populate the table with the csv file

php populatetable.php issuesdb parsed

Populate the table with data from the parsed file Source Code

Using a web browser

Query Page

Results Page

Query Page Source

Results Page Source

Results source code