Changing Table Structure in phpMyAdmin

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

phpMyAdminAdding a Field

Suppose we need a new field to store a book’s language, and by default, the books on which we keep data are written in English. We decide that the field will be called language, and will be a code composed of two characters (en by default).

In the Structure sub-page of the Table view for the books table, we can find the Add new field dialog. Here, we specify how many new fields we want and where the new fields go.

The positions of the new fields in the table only matter from a developer’s point of view; we usually group the fields logically to find them more easily in the list of fields. The exact position of the fields will not play a role in the intended results (output from the queries), because these results can be adjusted regardless of the table structure. Usually, the most important fields (including the keys) are located at the beginning of the table, but this is a matter of personal preference.

In this case, we choose to put the new field At End of Table, which is the first choice in the menu, and click the Go button:

phpMyAdmin

We then see the familiar panel for the new fields, repeated for the number of fields asked for. We fill it, and this time we put a default value, en. We then click on Save.

phpMyAdmin

This panel appeared in horizontal mode, the default for $cfg['DefaultPropDisplay'].

Vertical Mode

If we set $cfg['DefaultPropDisplay'] to ‘vertical’, the panel to add new fields (along with the panel to edit a field’s structure) will be presented in vertical order. The advantages of working in vertical mode become obvious especially when there are more choices for each field, as explained in Chapter 16 on MIME-Based Transformations.

Let’s see how the panel appears if we are in vertical mode and ask for three new fields:

phpMyAdmin

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

Buy this book now.

{mospagebreak title=Editing Field Attributes}

On the Structure sub-page, we can make further changes to our table. For this example, we have set $cfg['PropertiesIconic'] to ‘both’ to see both the icons along with their text explanation:

phpMyAdmin

This panel does not allow every possible change to fields. It specifically allows:

  • Changing one field structure, using the Change link on a specific field
  • Removing a field: Drop
  • Adding a field to an existing Primary key
  • Setting a non-unique Index or a Unique index on a field
  • Setting a Fulltext index (offered only if the field type allows it)

These are quick links that may be useful in specific situations. Keep in mind that they do not replace the full index management panel, or the full field structure panel. Both are explained in this chapter.

We can also use the checkboxes to choose fields, and with the appropriate With selected icons, Edit the fields or do a multiple field deletion with Drop. The Check All / Uncheck All option permits us to easily check or uncheck all boxes.

TEXT Fields

We will now explore how to use the TEXT field type and the relevant configuration values to adjust for the best possible phpMyAdmin behavior.

First we add a TEXT field called description:

phpMyAdmin

There are three parameters that control the layout of the text area that will be displayed in Insert or Edit mode for the TEXT fields.

First, the number of columns and rows for each field is defined by:

$cfg['TextareaCols'] = 40;
$cfg['TextareaRows'] = 7;

This gives (by default) the following space to work on a TEXT field:

phpMyAdmin

The settings do not impose a limit other than visual, and a vertical scroll bar is created by the browser, should it be necessary.


Although MEDIUMTEXT, TEXT, and LONGTEXT columns can accommodate more than 32K of data, current browsers cannot always edit them with the mechanism offered by HTML: a text area. In fact, experimentation has convinced the phpMyAdmin development team to have the product display a warning message if the contents are larger than 32K, telling users that it might not be editable.

For LONGTEXT fields, setting $cfg['LongtextDoubleTextarea'] to TRUE doubles the available editing space.

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

Buy this book now.

{mospagebreak title=BLOB (Binary Large Object)}

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.

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

Buy this book now.

{mospagebreak title=ENUM and SET}

Both these field types are intended to represent a list of possible values; the difference is that the user can choose only one value from a defined list of values with ENUM, and more than one value with SET. With SET, the multiple values all go into one cell; multiple values do not imply the creation of more than one row of data.

We add a field named genre and define it as an ENUM. For now, we choose to put short codes in the value list, and one of them (F) as the default value:

phpMyAdmin

In the value list, we have to enclose each value within single quotes, unlike in the default value field. In our design, we know that these values stand for Fantasy, Child, and Novel, but for now we want to see the interface’s behavior with short codes. In the Insert panel, we now see a radio box interface:

phpMyAdmin

If we decide to have more self-describing codes, we can go back to Structure mode and change the definition for the genre field. In the following example, we do not see the complete value list because the field is not large enough, but what we entered was ‘Fantasy’, ‘Child’, ‘Novel’. We also have to change the default value to one of the possible values, to avoid getting an error message while trying to saving this file structure modification.

phpMyAdmin

With the modified value list, the Insert panel now looks as follows:

phpMyAdmin

Observe that the previous radio buttons have changed to a select list because of the longer size of the possible values.

If we want more than one possible value selected, we have to change the field type to SET. The same value list may be used, but now, using our browser’s multiple value selector (usually control-click) we can select more that one value:

phpMyAdmin


For the previous example, we would store only the genre codes in the books table in a normalized data structure, and would rely on another table to store the description for each code. In fact, we would not be using a SET or ENUM in this case.

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

Buy this book now.

{mospagebreak title=Index Management}

phpMyAdmin has a number of index management options, which we will cover in this section.

Single-Field Indexes

We have already seen how the Structure panel offers a quick way to create an index on a single field, thanks to some quick links like Primary, Index, and Unique. Under the field list, there is a section of the interface used to manage indexes:

phpMyAdmin

This section has links to edit or delete every index. Here, the Field part lists only one field per index, and we can see that the whole field participates in the index.

We will now add an index on the title. However, we want to restrict the length of this index to reduce the space used by the on-disk index structure. The Create an index on 1 column option is appropriate, so we click Go. In the next screen, we specify the index details as shown in the following screen:

phpMyAdmin

Here is how to fill this panel:

  • Index name: A name we invent
  • Index type: We can choose INDEX or UNIQUE
  • Field: We select the field that is used as the index, which is the title field
  • Size: We enter 30 instead of 100 (the complete length of the field) to save space

After saving this panel, we can confirm from the following screenshot that the index is created and does not cover the whole length of the title field:

phpMyAdmin

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

Buy this book now.

{mospagebreak title=Multi-Field Indexes and Index Editing}

In the next example, we will assume that in a future application we will need to find the books written by a specific author in a specific language. It makes sense to expand our author_id index, adding the language field to it.

We click the Edit link (small pencil) on the line containing the author_id index and choose to add one column to this index as shown in the following screenshot:

phpMyAdmin

We select the language field on the next panel. This time we do not have to put a size since the whole field will be used in the index:

phpMyAdmin

For better documentation, we should also change the key name (author_language would be appropriate). We save this index modification and we are back to:

phpMyAdmin

FULLTEXT Indexes

This special type of index allows for full-text searches. It is supported on tables of type MyISAM for VARCHAR and TEXT fields. We can use the Fulltext quick link in the fields list or go to the index management panel and choose Fulltext in the drop-down menu:

phpMyAdmin

We want a FULLTEXT index on the description field so that we are able to locate a book from words present in its description.

After the index creation, the index list looks like:

phpMyAdmin

Seeing 1 as the field length here might be surprising. In fact, MySQL does not support the idea of an index length for FULLTEXT indexes: the index is always on the whole field, but this 1 is the value reported by MySQL.

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

Buy this book now.

{mospagebreak title=Table Optimization: EXPLAIN a Query}

In this section, we want to get some information about the index that MySQL uses for a specific query, and the performance impact of not having defined an index.

Let’s assume we want to use the following query:

SELECT *
FROM `books`
WHERE author_id = 2 AND language = ‘es’

We want to know which books written by author 2 are in the es language, our code for Spanish.

To enter this query, we use the SQL link from the database or the table menu, or the SQL query window. We enter this query in the query box and click Go. Whether the query finds any results is not important right now.

phpMyAdmin

Let’s look at the header: SQL-query: [Edit] [Explain SQL] [Create PHP Code]

We will now use the [Explain SQL] link to get information about which index (if any) has been used for this query:

phpMyAdmin

We can see that the EXPLAIN command has been passed to MySQL, telling us that the possible_keys used is author_language. Thus, we know that this index will be used for this type of query. If this index did not exist, the result would have been quite different:

phpMyAdmin

Here, the possible_keys (NULL) and the type (ALL) mean that no index would be used, and that all rows would need to be examined to find the desired data. Depending on the total number of rows, this could have a serious impact on the performance. We can ascertain the exact impact by examining the query timing that phpMyAdmin displays on each results page and comparing with or without the index:

phpMyAdmin

However, the difference in time can be minimal if we only have limited test data compared to a real table in production.

Summary

In this chapter we covered:

  • How to add fields, including special field types like TEXT, BLOB, ENUM, and SET
  • How to upload binary data into a BLOB field
  • How to manage indexes (multi-field and full-text)
  • How to get feedback from MySQL about which indexes are used in a specific query

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

Buy this book now.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan