MySQL Cookbook

1.24 Producing HTML Output

1.24.1 Problem

You'd like to turn a query result into HTML.

1.24.2 Solution

mysql can do that for you.

1.24.3 Discussion

mysql generates result set output as HTML tables if you use -H (or --html) option. This gives you a quick way to produce sample output for inclusion into a web page that shows what the result of a query looks like.^[8] Here's an example that shows the difference between tabular format and HTML table output (a few line breaks have been added to the HTML output to make it easier to read):

^[8] I'm referring to writing static HTML pages here. If you're writing a script that produces web pages on the fly, there are better ways to generate HTML output from a query. For more information on writing web scripts, see Chapter 16.

% mysql -e "SELECT * FROM limbs WHERE legs=0" cookbook
+------------+------+------+
| thing      | legs | arms |
+------------+------+------+
| squid      |    0 |   10 |
| octopus    |    0 |    8 |
| fish       |    0 |    0 |
| phonograph |    0 |    1 |
+------------+------+------+
% mysql -H -e "SELECT * FROM limbs WHERE legs=0" cookbook
<TABLE BORDER=1>
<TR><TH>thing</TH><TH>legs</TH><TH>arms</TH></TR>
<TR><TD>squid</TD><TD>0</TD><TD>10</TD></TR>
<TR><TD>octopus</TD><TD>0</TD><TD>8</TD></TR>
<TR><TD>fish</TD><TD>0</TD><TD>0</TD></TR>
<TR><TD>phonograph</TD><TD>0</TD><TD>1</TD></TR>
</TABLE>

The first line of the table contains column headings. If you don't want a header row, see Recipe 1.26.

The -H and --html options produce output only for queries that generate a result set. No output is written for queries such as INSERT or UPDATE statements.

-H and --html may be used as of MySQL 3.22.26. (They actually were introduced in an earlier version, but the output was not quite correct.)