Displaying MySQL tables as ASCII in blog posts

Question

Hi, I would like to quickly display MySQL recordsets in blog posts, using a similar ASCII layout to that used by mysql.com in their help pages. I'm using wordpress v2.7. Any ideas? I should also add that I'm using wp-syntax for syntax highlighting, so it's really just generating the ASCII that I'm interesting in.

Answer 1

If you mean things like

+----+------+
| id | name |
+----+------+
|  1 | Bob  |
|  2 | Mary |
|  3 | Jane |
|  4 | Lisa |
+----+------+

then just running the query from the MySQL commandline should suffice, as the results are formatted when running queries in interactive mode on the commandline. You can then copy and paste them into your blog post, surrounding them with <pre> or similar if necessary.

Answer 2

The ASCII you speak of is the way the MySQL command-line client lays out its results.

mysql> select task_nextrun,task_name from pref_task;
+--------------+-----------------+
| task_nextrun | task_name       |
+--------------+-----------------+
|   1235999760 | datacache_clean |
|   1236002760 | process_stats   |
+--------------+-----------------+
2 rows in set (0.00 sec)

mysql>

You would just need to send your commands to the MySQL command line client.

Answer 3

If you want to do it without calling the command-line client, here's a way in PHP. Note that this is pretty crude code and that I haven't tested it, I'm mostly just trying to explain the process. It will also left-align everything, I believe the mysql client right-aligns numbers, emulating that would require a bit more work, but nothing difficult.

Assuming that you've fetched the records into an associative-only array named $resultset, using something like mysqli_result's fetch_all() function:

// determine maximum value lengths for each column
foreach ($resultset as $result)
{
    foreach ($result as $col => $val)
    {
        if (strlen($val) > $max_length[$col])
        {
            $max_length[$col] = strlen($val);
        }
    }
}

// construct border lines
foreach ($max_length as $col_length)
{
    $border_line .= '+'.str_repeat('-', $col_length+2);
}
$border_line .= "+";

// print header
print $border_line."<br />\n";
foreach ($max_length as $col_name => $col_length)
{
    print '| '.str_pad($col_name, $col_length, ' ').' |';
}
print "<br />\n";
print $border_line."<br />\n";

// print data
foreach ($resultset as $result)
{
    foreach ($result as $col => $val)
    {
        print '| '.str_pad($val, $max_length[$col], ' ').' |';
    }
    print "<br />\n";
}
print $border_line."<br />\n";