MySQL Cookbook, 2nd Edition

Producing HTML or XML Output

Problem

You’d like to turn a query result into HTML or XML.

Solution

mysql can do that for you. Use mysql -H or mysql-X.

Discussion

mysql generates an HTML table from each query result set if you use the -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 statement looks like. 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):

%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 Suppressing Column Headings in Query Output.

mysql creates an XML document from the result of a statement if you use the -X (or --xml) option:

%mysql -X -e "SELECT * FROM limbs WHERE legs=0" cookbook <?xml version="1.0"?> <resultset statement="select * from limbs where legs=0 "> <row> <field name="thing">squid</field> ...