Mike B Mike B - 1 year ago 74
MySQL Question

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.

Answer Source

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");
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download