MySQL output not formatted when executed through php

Question

I'm trying to get formatted output from mysql as it normally shows when executed from a shell. This is discussed here and here, but it's not working for me.

When I run this in my shell for instance:

mysql -e "select language_id, name, image from `language`;" my_database

I get the expected output:

+-------------+-----------+--------+
| language_id | name      | image  |
+-------------+-----------+--------+
|           1 | English   | gb.png |
|           2 | Français  | fr.png |
+-------------+-----------+--------+

But when I do the same thing from the php cli:

passthru('mysql -e "select language_id, name, image from `language`;" my_database');

It comes out with no formatting:

language_id name    image
1   English gb.png
2   Français    fr.png

I've tried passthru, system, exec, and shell_exec but all return the same unformatted output. Why is the output different when run from php?

Never mind, based on the other answers I see that you're running this from the CLI. — Jay Blanchard
– Jay Blanchard, Commented Mar 18, 2020 at 14:03
Do you just need something that will be consistent between CLI and shell execution? Maybe \G? — user3783243
– user3783243, Commented Mar 18, 2020 at 15:05
@user3783243 - thanks, yeah I noticed \G behaves the same, which only makes me all the more confused about why the standard output is different. But I need the standard ascii table, not html — You Old Fool
– You Old Fool, Commented Mar 18, 2020 at 16:04
Maybe stackoverflow.com/a/35534673/3783243 would work for you? Can't find much documentation on vertical vs. horizontal display processors. The \G invokes the vertical so whatever is handling the horizontal must vary — user3783243
– user3783243, Commented Mar 18, 2020 at 16:31
@user3783243 - maybe? But it still doesn't answer my question. I'm trying to understand the mechanics of this more than anything else -whydoes it behave differently to begin with? — You Old Fool
– You Old Fool, Commented Mar 18, 2020 at 22:12

BlobbyBob · Accepted Answer · 2020-03-25 17:13:08Z

6

+50

When you run strace on the mysql command you can see the lines

ioctl(0, TCGETS, {B38400 opost isig icanon echo ...}) = 0
ioctl(1, TCGETS, {B38400 opost isig icanon echo ...}) = 0

which are system calls for checking terminal properties of stdin and stdout (see tty_ioctl(4)).

This means, that mysql is checking whether it outputs to a terminal/tty or into a pipe and behaving differently depending on that. Since all the commands PHP offers are based on forking the process and piping the output back into PHP, you won't be able to make mysql behave like it was run in a real terminal.

Note: You can reproduce this behavior in your shell by calling

mysql -e "select language_id, name, image from `language`;" my_database | tee

answered Mar 25, 2020 at 17:13

BlobbyBob

4773 silver badges11 bronze badges

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

Comments

Max · Accepted Answer · 2020-03-25 03:18:02Z

2

Change the command to:

passthru('mysql -t -e "select language_id, name, image from `language`;" my_database');

The -t or --table switch forces tabular display of results.

answered Mar 25, 2020 at 3:18

Max

1,0331 gold badge8 silver badges18 bronze badges

2 Comments

You Old Fool Over a year ago

Yes that works. Although this explains how to get the desired output, it still doesn't quite explain why that flag is needed running from within php but not directly from command line. I have a feeling there are some default flags set in the shell environment somewhere but would be really helpful if you can pinpoint the exact reason why it's behaving differently.

Max Over a year ago

Basically, what's happening is mysql detects that its output is not going to stdout -- it's going to php. When that happens it skips pretty-printing. ls and other unix command-line utilities commonly do the same thing.

Rick James · Accepted Answer · 2020-03-25 23:46:28Z

-1

$ mysql -r -u root -e 'select now()'
+---------------------+
| now()               |
+---------------------+
| 2020-03-25 16:44:06 |
+---------------------+

-r can also be spelled --raw.

See also --batch and --silent. The whole list: man mysql.

As for the "why"... PHP->shell->mysql is too contorted. (Granted, it is quick and dirty, but...) When using PHP, you should connect to the database and format the output using PHP.

answered Mar 25, 2020 at 23:46

Rick James

144k15 gold badges144 silver badges255 bronze badges

Comments

CarlosCarucce · Accepted Answer · 2020-03-18 14:05:04Z

-2

What does it looks formatted is the cli application. Php will fetch raw results from your table. you will have to do the formatting.

One way to do this (if you wanna display it in the browser) is:

//assume $row as your fetch result as an array and $results as tour result set

<table>
    <tr>
        <th>language_id</th>
        <th>name</th>
        <th>image</th>
    </tr>
    <?php while ($row = $results->fetch()) { ?>
        <tr>
            <td><?php echo $row['language_id'] ?></td>
            <td><?php echo $row['name'] ?></td>
            <td><?php echo $row['image'] ?></td>
        </tr>
    <?php } ?>
</table>

answered Mar 18, 2020 at 14:05

CarlosCarucce

3,6093 gold badges31 silver badges56 bronze badges

7 Comments

You Old Fool Over a year ago

This is not a web application and I'm not asking how to format the results - I'm asking why mysql is not returning the same formatted output from the php cli as it does when I run the command directly in a shell.

CarlosCarucce Over a year ago

As explained here, you will have to deal with the formatting. Maybe replace some characters or understanding that the character encoding from your viewport can diverge from your CLI application would help you out. Good luck anyways...

You Old Fool Over a year ago

Carlos, did you look at the answers I linked in my question? This has already been discussed, and again, I'm not asking how to format the output. Please re-read my question.

CarlosCarucce Over a year ago

Yes. And you got this answered there aswell: stackoverflow.com/a/4597190/3435728

You Old Fool Over a year ago

If the accepted answer is correct then why isn't it working for me? That is the question I'm asking.

|

Collectives™ on Stack Overflow

MySQL output not formatted when executed through php

4 Answers 4

Comments

2 Comments

Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related