Print results in MySQL format with PHP

Question

Is there a way to print the results of a MySQL query in the same format as the terminal?

Something like:

+--------+------------------------------+------+------+
| id     | name                         | year | rank |
+--------+------------------------------+------+------+
|  10920 | Aliens                       | 1986 |  8.2 |
|  17173 | Animal House                 | 1978 |  7.5 |
|  18979 | Apollo 13                    | 1995 |  7.5 |
|  30959 | Batman Begins                | 2005 | NULL |
|  46169 | Braveheart                   | 1995 |  8.3 |
| 109093 | Fargo                        | 1996 |  8.2 |

I am querying the database from php.

Any help would be appreciated. Thanks!

I should specify, is there an 'easy' way to do this without manually formatting the output. — Craig Kochis
– Craig Kochis, Commented May 24, 2011 at 3:34
You can use something like printf to format them with specific column widths. stackoverflow.com/questions/6053095/textfile-structure-tables/… This solution can be made more flexible by checking against the max length of name and using it for the column width, but that's a little too involved for me to tackle at the moment — onteria_
– onteria_, Commented May 24, 2011 at 3:38
pass the values through a loop, use a html table, guess it depends on your definition of easy. — user557846
– user557846, Commented May 24, 2011 at 3:39
I am aware of printing the results to a table or styling it using css. I was just wondering if there was a quick way to dump the output of query with the same formatting as the terminal. The main reason is that I want to be able to compare the results of queries (seeing if they match). I figured this would be easier without any markup involved (was just going to throw the output into a <pre> tag). I'm doing this as a lesson for a class I teach. — Craig Kochis
– Craig Kochis, Commented May 24, 2011 at 3:50

deceze · Accepted Answer · 2011-05-24 03:45:36Z

4

Look into sprintf/printf:

echo '+--------+------------------------------+------+------+';
echo '| id     | name                         | year | rank |';
echo '+--------+------------------------------+------+------+';

foreach (...) {
    printf('| %6s | %-28s | %4s | %4s |', $row['id'], $row['name'], $row['year'], $row['rank']);
}

You'll run into problems for anything that's longer than the predefined column size though. You can make dynamic column widths that scale to the right size by first going through your values and figuring out what the longest value is. In that case, for the horizontal lines, use str_repeat. I'll leave that as exercise for you.

edited May 24, 2011 at 3:45

answered May 24, 2011 at 3:39

deceze♦

525k89 gold badges807 silver badges954 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

tlunter · Accepted Answer · 2011-05-24 03:38:40Z

1

Why not print using HTML? That'd be easier than ASCII

answered May 24, 2011 at 3:38

tlunter

7241 gold badge5 silver badges14 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:00:02Z

0

Technically...

$sql = "SELECT id, name, year, rank FROM Table"
echo '<pre>';
system("mysql mysql --user=$user_name --password=$your_password $db_name -q $sql")
echo '</pre>';

But please don't because it a big security issue and just considered bad.

Again, this answer depends on how specific you are defining "How MySQL does it" and "easy".

Highly suggest using something like what was mentioned in a very similar question: Print table data mysql php

or what @deceze mentioned, although using sprintf is not very flexible, it will get the job done.

edited May 23, 2017 at 12:00

CommunityBot

11 silver badge

answered May 24, 2011 at 3:56

colinross

2,08513 silver badges10 bronze badges

Collectives™ on Stack Overflow

Print results in MySQL format with PHP

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related