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>
    <field name="legs">0</field>
    <field name="arms">10</field>
  </row>

  <row>
    <field name="thing">octopus</field>
    <field name="legs">0</field>
    <field name="arms">8</field>
  </row>

  <row>
    <field name="thing">fish</field>
    <field name="legs">0</field>
    <field name="arms">0</field>
  </row>

  <row>
    <field name="thing">phonograph</field>
    <field name="legs">0</field>
    <field name="arms">1</field>
  </row>
</resultset>

You can also write your own XML generator that directly processes query results into XML. See Exporting Query Results as XML.

The -H, --html -X, and --xml options produce output only for statements that generate a result set. There is no output for statements such as INSERT or UPDATE.

See Also

For information on writing your own programs that generate HTML from query results, see Chapter 17.