Formatting the results of a MySQL query as if it were run from the console

I'm writing a quick and dirty reporting script that queries a report and emails the results. When using the MySQL console the results are in a nicely formatted table:

mysql> select * from users;
+-----------+-----------开发者_运维知识库-+-------+
| firstname | city       | zip   |
+-----------+------------+-------+
| Maria     | Holland    | 12345 |
| Rene      | Doylestown | 65432 |
| Helen     | Conway     | 98745 |
+-----------+------------+-------+
3 rows in set (0.01 sec)

Is there an easy way to replicate this formatting when fetching the results with PHP? Obviously I could achieve this by writing my own report formatter but I was hoping for something a little more elegant.

You could do this quite easily using the Console_Table PEAR package. Just loop through your MySQL results, and add rows to your table. You can use the Console_Table::setHeaders() method to add the headers for your columns, then the Console_Table::addRow() method to add each row, and finally Console_Table::getTable() to display it.

There is nothing built into PHP to do this. If you don't want to use/write code to draw console tables, just pass -e query to mysql via PHP using passthru(). This will work queries terminated with both ; and \G:

passthru("mysql -e '$query;' database_name");

You have to do it yourself.

do a loop to find the max size for each column. Then output each row padding to that size +2 with a space at the beginning and end. seperate each column with a |.

Use + and - to create your top and bottom.

It's hard to give a concrete example without knowing what you're using to get your results. But assuming you're using mysql_query. Here's an example.

$conn = mysql_connect("localhost", "mysql_user", "mysql_password");
mysql_select_db("mydbname");
$result = mysql_query("SELECT * FROM myTable");
//first get your sizes
$sizes = array();
$row = mysql_fetch_assoc($result);
foreach($row as $key=>$value){
    $sizes[$key] = strlen($key); //initialize to the size of the column name
}
while($row = mysql_fetch_assoc($result)){
    foreach($row as $key=>$value){
        $length = strlen($value);
        if($length > $sizes[$key]) $sizes[$key] = $length; // get largest result size
    }
}
mysql_data_seek($result, 0); //set your pointer back to the beginning.

//top of output
foreach($sizes as $length){
    echo "+".str_pad("",$length+2,"-");
}
echo "+\n";

// column names
$row = mysql_fetch_assoc($result);
foreach($row as $key=>$value){
    echo "| ";
    echo str_pad($key,$sizes[$key]+1);
}
echo "|\n";

//line under column names
foreach($sizes as $length){
    echo "+".str_pad("",$length+2,"-");
}
echo "+\n";

//output data
do {
    foreach($row as $key=>$value){
        echo "| ";
        echo str_pad($value,$sizes[$key]+1);
    }
    echo "|\n";
} while($row = mysql_fetch_assoc($result));

//bottom of output
foreach($sizes as $length){
    echo "+".str_pad("",$length+2,"-");
}
echo "+\n";

That would do it (I hope I didn't miss a semicolon in there :) ).

Hope that helps!

You could use exec or backticks and actually run it from the command line through php. Apparently the mysql command has a -H switch you can use, and it will output HTML formatted. Haven't tried it though, but that might look good too.

echo '<pre>';
echo `mysql -u user -ppass -e "select * from table;" database_name`;

2 lines, no pear packages, how much more elegant can it get for a quick and dirty stats page.

Building on mfonda's answer, you can really easily load the Console_Table pear package with composer now: https://packagist.org/packages/pear/console_table

$ composer require pear/console_table

<?php
//Suppress E_DEPRECATED errors for statically calling a non-static method (this package is pretty old!)
error_reporting(E_ALL & ~E_DEPRECATED);
require __DIR__ . '/vendor/autoload.php';

//echo "<pre>"; #uncomment this line if running script in a browser

//The class isn't namespaced so just call it directly like so:
echo Console_Table::fromArray(
    ['column', 'headings'], 
    [
        ['1st row', 'values'], 
        ['2nd row', 'values'], 
        ['...', '...']
    ]
);

This outputs:

+---------+----------+
| column  | headings |
+---------+----------+
| 1st row | values   |
| 2nd row | values   |
| ...     | ...      |
+---------+----------+

This doesn't make any sense bearing in mind the manner in which you fetch data from MySQL in PHP. (i.e.: You generally fetch onw row of data at a time either as an array (mysql_fetch_array) or an object (mysql_fetch_object).)

As such, you'd need to write your own hack to grab all of the rows and format the output in this manner. (That said, it should be trivial to grab the data and output it as an HTML table - you could get the field names via array_keys if you use mysql_fetch_array, etc. )

I optimized the answer of @ehudokai so it uses less loops (5 vs 9). And for completeness I added the command line, stats and error output, too:

<pre>
<?php
$db = mysqli_connect('localhost', 'my_user', 'my_password', 'my_db');
$start = microtime(true);
$sql = "SELECT * FROM myTable";
$result = mysqli_query($db, $sql);
$exec_time = microtime(true) - $start;
// obtain the maximum string length of all column headings and rows
$colwidths = array();
while ($row = mysqli_fetch_assoc($result)) {
    foreach ($row as $key => $value) {
        // heading
        if (!isset($colwidths[ $key ])) {
            $colwidths[ $key ] = strlen($key) + 2;
        }
        // rows
        $colwidths[ $key ] = max($colwidths[ $key ], strlen($value) + 2);
    }
}
echo 'mysql>' . trim($sql) . PHP_EOL;
// SELECT, SHOW, DESCRIBE, EXPLAIN = resource
// INSERT, UPDATE, DELETE, DROP = true
// Error = false
if (!is_bool($result)) {
    if ($colwidths) {
        mysqli_data_seek($result, 0);
        while ($row = mysqli_fetch_assoc($result)) {
            // create and display horizontal line and column headings
            if (!isset($header)) {
                $header = '| ';
                $line = '+';
                foreach ($row as $key => $value) {
                    $line .= str_repeat('-', $colwidths[ $key ] + 2) . '+';
                    $header .= str_pad($key, $colwidths[ $key ]) . ' | ';
                }
                echo $line . PHP_EOL;
                echo $header . PHP_EOL;
                echo $line . PHP_EOL;
            }
            // display row values
            foreach ($row as $key => $value) {
                echo '| ' . str_pad($value, $colwidths[ $key ] + 1);
            }
            echo '|' . PHP_EOL;
        }
        echo $line . PHP_EOL;
    }
    mysqli_free_result($result);
}
$affectedrows = mysqli_affected_rows($db);
if ($result === false) {
    echo PHP_EOL . 'ERROR ' . mysqli_errno($db) . ': ' . mysqli_error($db);
}
else if ($result === true) {
    echo 'Query OK, ' . $affectedrows . ' rows affected (' . round($exec_time / $iterations * 1000) . ' ms)';
}
else if ($affectedrows) {
    echo $affectedrows . ' rows in set (' . round($exec_time / $iterations * 1000) . ' ms)';
}
else {
    echo 'Empty set (' . round($exec_time / $iterations * 1000) . ' ms)';
}
?>
</pre>

Examples

SELECT

mysql>SELECT
        topic_id,
        MATCH(text) AGAINST('tuning') AS score
    FROM
        topics
    WHERE
        MATCH(text) AGAINST('tuning' IN BOOLEAN MODE)
    ORDER BY
        score DESC
    LIMIT 10
+----------+--------------------+
| topic_id | score              |
+----------+--------------------+
| 153257   | 5.161948204040527  |
| 17925    | 4.781417369842529  |
| 66459    | 4.648380279541016  |
| 373176   | 4.570812702178955  |
| 117173   | 4.55166482925415   |
| 167016   | 4.462575912475586  |
| 183286   | 4.4519267082214355 |
| 366132   | 4.348565101623535  |
| 95502    | 4.293642520904541  |
| 29615    | 4.178250789642334  |
+----------+--------------------+
10 rows in set (141 ms)

Error:

mysql>SELECT * WHERE 1=1

ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE 1=1' at line 1

UPDATE

mysql>UPDATE topics_search SET topic_id = topic_id WHERE topic_id = 2
Query OK, 0 rows affected (0 ms)

it sounds like you just need to use any one of the exec methods or backticks. I'm not sure about the '\G' thingy... but, I published a php function called query2Table() a few months ago @ http://www.logicwizards.net/php-query2table -- based on a function I've been recycling for years. I have a bunch that I've accumulated over the years: query2xml, query2excel, query2json, etc. I think I still have the old perl & asp versions somewhere, too.

Basically, in my solution, you can just pass it the query string and it dynamically spit's out an html table using column names fetched from the results as the table's header row. It also grows to fill the width of it's inherited container object.

  query2table("select * from table;");

I have a more up to date version query2AjaxTable() which wraps everything up nicely in a class and adds jQuery sorting & animations -- but it's not ready for publishing yet.

If my silly little function doesn't help you, in your dilemma, maybe someone else will find it useful...