MySQL output not formatted when executed through php

user_1767412 0
Viewed 168

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?

mysql php
4 Answers

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
user_52048720

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.

user_29627560 
$ 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.

user_17668310

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>
user_34357280
Related