Viewing Content Of Blob In phpMyAdmin

Question

Sorry for the Noob Question, but what does the circled button mean, and how can I view the content of a blob?
_{(source: rigel222.com)}

Billiton

Can you view it using the print preview mode? – Inwardly

This seems to be the same question/answer space as #2721802 ? – Estimate

Answer 1

earlier versions of phpmyadmin had a setting called

$cfg['ShowBlob']              = TRUE;

That would allow you to view the contents of blobs in the browser. You should note that this would cause chaos if you were storing binary files in blobs, since you would see endless gobblygok in the browser window. There are some people (like me) who decided that their application needed to use BLOB types to store text (seemed like a good decision at the time, and as I recall there was some thinking on my part that went into the decision). However phpmyadmin decided to discourage this by deprecating this config setting. Understandable since doing this might cause quite a support request. Apparently the thinking was to move people over the TEXT field types.

Happily displaying the contents of blobs has been moved into the user interface rather than the configuration.

The simplest way to see the contents of blobs when you are browsing is to click the link:

+ Options

Happily your screenshot already shows the + Options in the top part of the top image.

Which will display a form that will allow you to display blobs (and binaries). Click that and it will add it to your choice to the session, ensuring that you see the contents from then on.

You can also get the same result using print view:

Print view (with full texts)

Which lives at the bottom of the page.

Sadly both of these techniques are not helpful if you always want to display the blob, since it appears to reset frequently. You can fix this by adding the line

$_GET['display_blob'] = true;

At the beginning of the sql.php file. I think there might be a better way to do this, and I hope someone else might bring it up...

(note: as Rodrigo pointed out you can manually achieve this effect by appending &display_blob=true on the URL)

Your specific question about the "Choose File" button is simple. Most of the uses of blobs are for storing digital files in the database. This button allows you to upload a new file into the database. If you select a file and click "go" it will try to stuff the contents of that file into the blob column for you.

Just to note, simply displaying the contents of the blob is probably not what other users want. When I look at the "blob summary" before I use this option to display the blobs I see blob sizes of 55 bytes max. Your example has bigger values, because it looks like you are storing very small text files, which I assume means paragraphs of text. If the size is bigger then 10's of kilo-bytes it is probably a binary file that will just display gooblegok.

If you want to download binary files intelligently (rather than displaying them as text) I think you need to look into what phpmyadmin calls blobstreaming.

Buffington

Which are "earlier versions" in this context, and which was the current version in April 2010? For phpMyAdmin 2.11.8.1deb5+lenny8 with MySQL client 5.0.51a I can't find a "+ Options" link anywhere :-(. – Caitiff

I think only very very early versions... something that worked with php v4 for instance. – Buffington

Notice the misspelling &display_bob=true it should be &display_blob=true – Aymer

fixed the misspelling. Only took me a year or so. Thanks bjtilley! – Buffington

Answer 2

Put &display_blob=true on the end of your URL.

Incline

Answer 3

For new visitors, another way to view BLOB columns is the QUOTE() function. You can create a view out of it for convenience. (A view behaves like a table but it's really a kind of saved query):

CREATE VIEW log_text AS SELECT BlobID, FileName, CAST(QUOTE(Content) AS CHAR) FROM log;

You'll have to CAST the result as CHAR because QUOTE(binary) is still binary. This may cause some chaos (as @ftrotter puts it) because QUOTE only translates control characters, not supra-ASCII characters.

For less chaos use HEX().

Maharaja

Answer 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.

Oxheart

Whilst I believe your answer to be useful, be careful with boilerplate copy and paste answers, these tend to be flagged (as did this one) as duplicate and possible spam. – Umbria

For the record both answers are originally authored by me. Thanks. – Oxheart

I know they were both authored by you, but they get flagged as identical which means they are at risk of being flagged as spam by the community via the 10k rep user mod tools. Just take care to make them fit the question, perhaps re-jig the wording a bit. It's more for your own good than for anyone else. Thanks. – Umbria

not working. $cfg['Servers'][$i]['extension'] = 'mysqli'; – Huber

Answer 5

New versions of PHPMyAdmin seem to require a slightly different solution

$cfg['ProtectBinary'] = FALSE;

Place this at the end of the file /etc/phpmyadmin/config.inc.php

Or by using this one liner

echo "\$cfg['ProtectBinary'] = FALSE;" | sudo tee -a /etc/phpmyadmin/config.inc.php

This worked for me on PHPMyAdmin version 3.4.10.1deb1

Choreodrama

Answer 6

There is already a handler in phpMyAdmin to show blob data For this just add "&display_blob=true" at the end of the url. Change url like below:

*****phpmyadmin/sql.php?db=database_name&table=table_name

to

*****phpmyadmin/sql.php?db=database_name&table=table_name&display_blob=true

Mighty

Answer 7

The "Choose file" dialog permits you to pick a file on your workstation and upload it inside the blob column for this row.

If your BLOB contains JPEG or PNG images, you can actually view their thumbnails when browsing, with the full image being displayed when you click on the thumbnail. See https://phpmyadmin.readthedocs.org/en/latest/transformations.html.

Buss

Answer 8

Save Link As txt file

You can right click on phpMyAdmin and save link as txt file extension to view the blob text file type

Gamely

Instead of posting links as answer add some text to explanation how this answer help to OP in fixing current issue.Thanks – Umiak

Answer 9

I've added additional this to me to config.inc.php that helped me out so much

# Show BLOB data on table browse pages.  Hack to hardcode all requests.
$_REQUEST['display_blob'] = true;

Bookshelf