BLOB (Binary Large Object) - PHP

This chapter explores editing table definitions and using special column types. When developing Web applications (or any application), requirements often change because of new or modified needs. Developers must accommodate these changes through judicious table-structure editing. This is chapter six of Mastering phpMyAdmin for Effective MySQL Management by Marc Delisle (Packt Publishing, April 2004, ISBN 1904811035).

TABLE OF CONTENTS:

By: Marc Delisle

Poor Best

Rating:

/ 21

September 28, 2004

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

BLOB fields are usually used to hold some binary data (image, sound), even though the MySQL documentation implies that even TEXT fields could be used for the same purpose. However, phpMyAdmin's intention is to work with BLOB fields to hold all binary data.

We will see in Chapter 16, MIME-Based Transformations that there are special mechanisms available to go further with BLOB fields, including being able to view some images directly from within phpMyAdmin.

First we add a BLOB field, cover_photo, to our books table:

phpMyAdmin

If we now Browse the table, we can see the field length information [BLOB – 0 Bytes] for each BLOB field:

phpMyAdmin

This is because the $cfg['ShowBlob'] configuration directive is set to FALSE by default, thus blocking the display of BLOB contents in Browse and Edit modes (and showing a Binary – do not edit warning). This behavior is intentional—usually we cannot do anything with binary data represented in plain text.

Binary Contents Upload

If we now edit one row, we see the warning and a Browse button. Even though editing is not allowed, we can easily upload a text or binary file into this BLOB column.

Let's choose an image file using the Browse button—for example, the pma_logo.png file in the phpMyAdmin/images directory:

phpMyAdmin

We have now uploaded an image inside this field, for a specific row:

phpMyAdmin

If $cfg['ShowBlob'] is set to TRUE, we see the following in the BLOB field:

phpMyAdmin

The $cfg['ProtectBinary'] parameter controls what can be done while editing binary fields (BLOBs and any other field with the binary attribute).

The default value 'blob' permits us to protect against editing of BLOB fields, allowing us to edit other fields marked as binary by MySQL. A value of 'all' would protect against editing even BLOB fields. A value of FALSE would protect nothing, thus allowing us to edit all fields. If we try the last choice, we see the following in the Edit panel for this row:

phpMyAdmin

Chances are this is not our favorite image editor! In fact, data corruption may result even if we save this row without touching the BLOB field. But the setting to remove ProtectBinary exists because some users put text in their BLOBs and must be allowed to modify them.

MySQL BLOB data types are actually similar to their corresponding TEXT data types, with the only difference of being treated as case-sensitive for sorting and comparison purposes. This is why phpMyAdmin can be configured to allow editing of BLOB fields.

This chapter is from Mastering phpMyAdmin for Effective MySQL Management by Marc Delisle (Packt Publishing, April 2004, ISBN 1904811035). Check it out at your favorite bookstore today.