5

I have a table with a varchar column. Its collation is set to utf8_bin. My software using this table and column works perfectly. But when I look at the content in phpmyadmin, I only see some hex values or [Blob xB]. Can I make phpmyadmin show the content correctly?

Besides, when I set the collation to utf8_general_ci or utf8_unicode_ci, the phpmyadmin shows the content correctly.

Thx Marc

[edit]Hah, I found out, there is a small "+Options" link above every table in phpmyadmin. It opens several options including "Show BLOB contents" - which makes the [blob] to readable text when enabled and "Show binary contents as HEX" which shows the hex codes as text when disabled.

No idea why there are two options though and why sometimes there is a [Blob] and sometimes hex values.

Well. Now I'm still wondering: Setting these options get lost when I go to another table. I have to set them every time I go there. Is there a way to save those options? [/edit]

marc40000
  • 3,167
  • 9
  • 41
  • 63
  • 1
    This seems to be the same question/answer space as http://stackoverflow.com/questions/2188264/viewing-content-of-blob-in-phpmyadmin? – John Fiala Mar 10 '11 at 06:35

5 Answers5

7
  • DO NOT EDIT THIS FILE, EDIT config.inc.php INSTEAD !!!

So, to do this in line with PMA author's correct documentation, edit config.inc.php. Add the line: $cfg['DisplayBinaryAsHex'] = false;

to your config.inc.php file.

;-)

sciron
  • 79
  • 1
  • 1
4

I think the best solution is to change this line:

$cfg['Servers'][$i]['extension'] = 'mysql';

to this:

$cfg['Servers'][$i]['extension'] = 'mysqli';

If you have the mysqli extension available, use it. It is more secure, a bit more optimized, and it handles the BLOB type of utf-8 better by default. Your [BLOB] entries should start showing up as their values without having to add in any other special configuration options.

cwd
  • 53,018
  • 53
  • 161
  • 198
  • 1
    I can confirm that this makes a difference. After trying a bunch of other changes, switching to mysqli the utf8_bin columns started showing as regular text again. – Costa Aug 14 '13 at 20:53
2

As a matter of fact, you can. But you would need to have access to the phpMyAdmin php files. If you have, go to /libraries/config.default.php

Then look for $cfg['DisplayBinaryAsHex'] = true; and change the value to "false". (It should be around line 888)

Cheers,

Robin

Robin
  • 37
  • 1
  • Ok, ignoring the "NO!! DO NOT EDIT!!" comment in acsii art big font at the beginning of that file, it actually works and doesn't seem to mess up things :) I was searching for another otpion in that file to make the "Show BLOB contents" default to true as well, but I can't find it. Do you also know how to enable that? Thx. – marc40000 Jun 23 '10 at 13:14
2

Append the following lines to your config.inc.php:

$_REQUEST['display_blob'] = true;

to have the BLOBs always expanded in the results table, and

$cfg['ProtectBinary'] = false;

to be able to edit them.

You may also want to have a look at the phpMyAdmin wiki.

UPDATE

That was enough in 3.2.4. In 3.2.5 in order to see text and not hex when editing a row you also need to do as Robin suggested, add the following entry to the config:

$cfg['DisplayBinaryAsHex'] = false;
Johnny Baloney
  • 3,409
  • 1
  • 33
  • 37
1

cwd's suggestion also fixes another problem.

I had been having trouble with "&" being displayed as "&" in phpMyAdmin - switching to MySQLi makes that problem go away (so "&" is displayed as "&").

j0k
  • 22,600
  • 28
  • 79
  • 90
trapper_hag
  • 780
  • 9
  • 14