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

Question

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.

Answer 1

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");

Answer 2

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!