[Impressum/Imprint] [Kontakt/Contact Me]
Home VIEW-based TNG reports Backup and Download Your TNG Data in One Step
 TNG - Tips & Tricks

Creating 'Static' Output Files from SQL Statements

You need the mysql.exe program. It is part of the MySQL suite:  http://dev.mysql.com/doc/refman/5.1/en/mysql.html Documentation. –  http://dev.mysql.com/downloads/ 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  http://www.blat.net/ Blat,  http://www.codestone.co.uk/software/docs/cssmtp/cssmtp.html CSSMTP or  http://www.beyondlogic.org/solutions/cmdlinemail/cmdlinemail.htm 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:

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.


Copyright © http://www.ahnendaten.de/tng/
Letzte Aktualisierung am 11. Juni 2017

Home VIEW-based TNG reports Backup and Download Your TNG Data in One Step