Today’s kludge is a response to the following need: a web form is used to populate a MySQL database table on a Linux server with information. Periodically, we would like to send out the contents of this table in a form that Excel users can easily adapt for their purposes.
Here’s one possible way to satisfy the need. It’s the result of combining various tips from around the web. It’s not elegant, but it works. We’re going to string together some sql, a bit of bash and the mutt mail user agent to accomplish our goal.
First, we want to use MySQL to write out the data to a CSV file. Here’s how to do that:
USE your_database; SELECT "firstname","lastname","email","school","schoolyear","affiliation","atty", "barnumber","vegetarian","crdate","tstamp","uid","source","ipaddress" UNION SELECT * from your_table INTO OUTFILE '/tmp/formdata.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'rn'
Save this in a file that has the name formdata.sql or something equally memorable.
One of the gotchas here is that the “SELECT .. INTO OUTFILE” part of this requires the MySQL FILE privilege, which is generally granted system-wide and not per-database. That is, it’s really only meant to be run by database administrators, and not mere mortals. Ordinary database accounts do not usually have this privilege. You may need to be the SQL “root” or similarly high-privilege user in order to have the ability write the data file. In our case, we are going to create a .my.cnf file in our home directory that gives us the ability to use mysql without supplying a user name and password on the command line. It looks like this:
[client] host=localhost user=root password=s0m3r3@11yh@rDPW
Note that “root” here refers to the MySQL user named “root”, not to the system user. If you go this route, make sure that the user that owns this file is the only one that can read the .my.cnf (
chmod 600 .my.cnf).
You may be wondering about the first line of the SQL script: SELECT “firstname” … UNION … . This is a clever hack to output the field names as the first line of the CSV file. You will need to enter the headers manually and enclose them with quotes and commas. The UNION just means that this output will be combined with the results of the normal select statement.
Now you need a small shell script to glue some things together:
#!/bin/bash rm -f /tmp/formdata.csv mysql < /home/sqluser/formdata.sql mutt -s "Updates" -a /tmp/formdata.csv firstname.lastname@example.org < /home/sqluser/message.txt
As you can see from the script, we’re telling the script to force remove
/tmp/formdata.csv. Then, we invoke mysql with the formdata.sql sql command file. Finally, we send out an email, using the mutt command-line mail tool to attach the csv file. Mutt is very useful for these sorts of things: we set the subject with “-s”, the attachment with “-a” and pipe in a message body from /home/sqluser/message.txt. This last is done to avoid tripping spam filters that check for the absence of a message body, although it could also provide useful information for your recipient, of course.