Creating 'Static' Output Files from SQL Statements

You need the mysql.exe program. It is part of the MySQL suite: Documentation. – Download.

As an example, we use the SQL statement SELECT lastname, COUNT(*) AS Total FROM tng_people GROUP BY lastname ORDER BY lastname; which produces a list a all lastnames with frequency of occurence. Change the SQL statement according to your needs.

To get the result file delivered via E-Mail, you can use nail or fastmail under Unix/Linux (depending from what is part of your distribution). Under Windows, there are several solutions, for example Blat, CSSMTP or Bmail.

To get a certain TNG report every day (e.g. a birthday list), create a batch file and use "Planned Tasks" (Windows). Under Unix/Linux, put your shell script into /etc/cron.daily (not with all distributions) or use /etc/crontab.

Creating "delimited ascii" files from SQL statements

Go to the command line and run mysql -h YOURMYSQLHOST -u YOURMYSQLUSER -p -B "SELECT lastname, COUNT(*) AS Total FROM tng_people GROUP BY lastname ORDER BY lastname;" YOURDATABASE >lastnames.txt

((Replace the words written in capital letters against the appropriate values.))

You can import the "delimited ascii" file into your favorite word processor or spreadsheet program.

Creating HTML files from SQL statements

Run mysql -h YOURMYSQLHOST -u YOURMYSQLUSER -p –html "SELECT lastname, COUNT(*) AS Total FROM tng_people GROUP BY lastname ORDER BY lastname;" YOURDATABASE >lastnames_htmlraw.txt

The result file is a "raw" HTML table, not yet a complete (valid) HTML file. To complete the file, do the following steps:

Create a file named html_header.txt with content like the following:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
        "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>
A sample listing
</title>
<meta http-equiv="Content-Type" content="text/html;charset=iso-8859-1" />
</head>
<body bgcolor="#FFFFFF">
<h1>My listing</h1>

Create a second file named html_footer.txt with content like the following:
```
</body></html>
```

Put the pieces together with

type html_header.txt >lastnames_listing.html
type lastnames_htmlraw.txt >>lastnames_listing.html
type html_footer.txt >>lastnames_listing.html

Use type if you have MS-Windows. Use cat if you are running Linux/Unix.

Create a batch file/shell script including all steps.

One possible usage is to get HTML files for search machines (Google and so on) - if you decided to exclude your TNG pages from being indexed by search machines (a I did).

Creating XML files from SQL statements

Run mysql -h YOURMYSQLHOST -u YOURMYSQLUSER -p -B "SELECT lastname, COUNT(*) AS Total FROM tng_people GROUP BY lastname ORDER BY lastname;" YOURDATABASE >lastnames.xml

(I don't own a program which does something useful with those XML files...)

Creating other file formats from SQL statements

There are no more output file formats supported by mysql.exe – you need a conversion program.