Data Access Notes » 2006

MYSQL - export and email automation

January 11, 2006 | In MySQL, WebTechnology | No Comments

Situation

You want to export data from a MYSQL table and email it in .CSV format on a regular schedule. This was on Darwin on Mac OS X server, but works on any similarly configured *Nix of course.

Solution

Build Query

Here I am exporting a CSV formatted file suitable for import into Excel.
SELECT * INTO OUTFILE '/tmp/weeklyupdates.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' from profiletable WHERE (entereddt > DATE_SUB( curdate( ) , INTERVAL 7 DAY ) );

Save this sql command file as updateseloutfile.sql

Bash script to run the query:

#!/bin/bash rm /tmp/updates.csv DBS=`mysql -uUsername -pPassword mysqldbnamehere < /scripts/updateseloutfile.sql`

save the script as updatescript.sh and test everything by executing script:

./updatescript.sh

Email Results

Add to the shell script a line to email the results:

(cat /scripts/updatesplaintextemailmessage.txt; uuencode /tmp/updates.csv /tmp/updates.csv) | mail -b samplebcc@antioch--college.edu -s "Weekly Web Updates" toaddress@antioch--college.edu

This line also adds some email text stored in updatesplaintextemailmessage.txt. It encodes the updates.csv file and sends it as an attachment to toaddress@, bccï¿½ing samplebcc@.

So you end up with 3 small text files:

updateseloutfile.sql - the sql file that exports data
updatesplaintextemailmessage.txt - the plain text message for email
updatescript.sh - the shell script that does the work

Schedule Export

Now set up the cron scheduler to run the shell script weekly:

check crontab listing: crontab -l
edit crontab: crontab -e
and add line to run your script weekly: 00 03 * * 6 /scripts/updatescript.sh

Thatï¿½s It! You now have weekly export of MYSQL data being emailed in .CSV format to designated email address. If this is of help to anyone else, Iï¿½d appreciate a quick note.

Learn more about crontab by typing: man crontab -s5 The regular man crontab does not have any good examples.

References

CMS systems

January 11, 2006 | In WebTechnology | 4 Comments

What is a content management system (cms)?
From Wikipedia, the free encyclopedia.
In computing, a content management system (CMS) is a system used to organize and facilitate collaborative creation of documents and other content. A CMS is frequently a web application used for managing websites and web content, though in many cases, content management systems require special client software for editing and constructing articles. The market for content management systems remains fragmented, with many open-source and proprietary solutions available.

There is a saying in German: “You can always spot the shoemaker’s son because he’s wearing the worst shoes.” thanks to Lenya wiki contributor Gregor Rothfus for this.

My May 2004 foray into Content Management Systems has learned about these 2 great products:

Joomla

Joomla is a split of the core Mambo Developers from Mambo; occurring Fall 2005.

Joomla! is a Content Management System (CMS) created by the same award-winning team that brought the Mambo CMS to its current state of stardom.

Joomla has a really solid looking development team as well.
http://www.joomla.org/

An important aspect of understanding Joomla is that it is not a fork in the software. Joomla has all the core developers working in the same structure as they did before. They are only using a different name. As confusing as it sounds, Mambo is effectively the fork from Mambo.

from http://www.devshed.com/c/a/BrainDump/Joomla-is-the-New-Mambo/6/

A great CMS feature comparison database is at The CMS Matrix.
And OpenSourceCMS has many CMS systems installed and ready to try out.

Personal Hover Craft

January 6, 2006 | In Other Tech | No Comments

Your own personal hovercraft! And for only $17,000.

Utilizing the same gravity-defying principles found in larger hovercraft, the hovering scooter is powered by a four-stroke internal combustion engine that operates for approximately one hour on a full tank of unleaded gasoline.