Table of Contents for
Mastering phpMyAdmin 3.4 for Effective MySQL Management

Version ebook / Retour

Cover image for bash Cookbook, 2nd Edition Mastering phpMyAdmin 3.4 for Effective MySQL Management by Marc Delisle Published by Packt Publishing, 2012
  1. Cover
  2. Mastering phpMyAdmin 3.4 for Effective MySQL Management
  3. Mastering phpMyAdmin 3.4 for Effective MySQL Management
  4. Credits
  5. About the Author
  6. About the Reviewers
  7. www.PacktPub.com
  8. Preface
  9. What you need for this book
  10. Who this book is for
  11. Conventions
  12. Reader feedback
  13. Customer support
  14. 1. Getting Started with phpMyAdmin
  15. What is phpMyAdmin?
  16. Installing phpMyAdmin
  17. Configuring phpMyAdmin
  18. Installing phpMyAdmin configuration storage
  19. Upgrading phpMyAdmin
  20. Summary
  21. 2. Configuring Authentication and Security
  22. Securing phpMyAdmin
  23. Summary
  24. 3. Over Viewing the Interface
  25. Customizing general settings
  26. Character sets and collations
  27. Navigation panel
  28. Main panel
  29. User preferences
  30. Query window
  31. Summary
  32. 4. Creating and Browsing Tables
  33. Creating our first table
  34. Inserting data manually
  35. Browse mode
  36. Profiling queries
  37. Creating an additional table
  38. Summary
  39. 5. Changing Data and Structure
  40. Changing table structure
  41. Summary
  42. 6. Exporting Structure and Data (Backup)
  43. Exporting a database
  44. Exporting a table
  45. Exporting selectively
  46. Exporting multiple databases
  47. Saving the export file on the server
  48. Memory limits
  49. Summary
  50. 7. Importing Structure and Data
  51. Importing SQL files
  52. Importing CSV files
  53. Importing other formats
  54. Reading files from a web server upload directory
  55. Displaying an upload progress bar
  56. Summary
  57. 8. Searching Data
  58. Performing a complete database search
  59. Stopping an errant query
  60. Summary
  61. 9. Performing Table and Database Operations
  62. Changing table attributes
  63. Emptying or deleting a table
  64. Renaming, moving, and copying tables
  65. Performing other table operations
  66. Multi-table operations
  67. Database operations
  68. Summary
  69. 10. Benefiting from the Relational System
  70. Defining relations with the relation view
  71. Defining relations with the Designer
  72. Benefiting from the defined relations
  73. Column commenting
  74. Summary
  75. 11. Entering SQL Statements
  76. The Query window
  77. Multi-statement queries
  78. Pretty printing (syntax highlighting)
  79. The SQL Validator
  80. Summary
  81. 12. Generating Multi-table Queries
  82. Exploring column criteria
  83. Generating automatic joins (internal relations)
  84. Executing the query
  85. The visual builder
  86. Summary
  87. 13. Synchronizing Data and Supporting Replication
  88. Supporting MySQL replication
  89. Summary
  90. 14. Using Query Bookmarks
  91. Creating bookmarks
  92. Recalling bookmarks from the bookmarks list
  93. Passing a parameter to a bookmark
  94. Summary
  95. 15. Documenting the System
  96. Generating relational schemas
  97. Summary
  98. 16. Transforming Data using MIME
  99. Enabling transformations
  100. Examples of transformations
  101. Summary
  102. 17. Supporting Features Added in MySQL 5
  103. Supporting routines—stored procedures and functions
  104. Executing code with triggers
  105. Using information_schema
  106. Partitioning
  107. Exploring the event scheduler
  108. Summary
  109. 18. Tracking Changes
  110. Prerequisites
  111. Principles
  112. Initiating tracking for one table
  113. Testing the tracking mechanism
  114. Determining tracking status
  115. Structure snapshot
  116. Exporting a version
  117. Creating a new version
  118. Deleting tracking information
  119. Summary
  120. 19. Administrating the MySQL Server
  121. Database information
  122. Server information
  123. Summary
  124. A. Troubleshooting and Support
  125. Seeking support
  126. Contributing to the project

Enabling transformations

We define transformation as a mechanism by which all the cells related to a column are transformed at browse time, using the metadata defined for this column. Only the cells visible on the current results page are transformed.

The use of this feature is controlled by the $cfg['BrowseMIME'] directive in config.inc.php. The default value of this directive is TRUE, meaning that transformations are enabled. However, the phpMyAdmin configuration storage must be in place (refer to Chapter 1) as the metadata necessary for the transformation is not available in the official MySQL table structure. It's an addition made especially for phpMyAdmin.

Note

The transformation logic itself is coded in PHP scripts, stored in libraries/transformations, and is called using a plugin architecture. In the documentation section on phpMyAdmin's home site (currently at http://www.phpmyadmin.net/home_page/docs.php), there is a link pointing to additional information for developers who would like to learn the internal structure of the plugins in order to code their own transformation.

Configuring settings for MIME columns

If we go to the Table view of the Structure page for the book table and click on the Change link for the cover_photo column, we see three additional attributes (provided the transformations feature is enabled):

  • MIME type
  • Browser transformation
  • Transformation options

This is shown in the following screenshot:

Configuring settings for MIME columns

For a specific column, it's possible to indicate only one type of transformation. Here, the column is a BLOB. Hence, it can hold any kind of data. In order for phpMyAdmin to interpret and act correctly on the data, the transformation system must be informed of the data format and the intended results. Accordingly, we have to ensure that we upload data that always follows the same file format.

We will first learn the purpose of these attributes and then try some possibilities in the Examples of transformation section, later in this chapter.

Selecting the MIME type

The MIME specification (http://en.wikipedia.org/wiki/MIME) has been chosen as a metadata attribute to categorize the kind of data that a column holds. Multipurpose Internet Mail Extensions (MIME) , originally designed to extend mail, are now used to describe content types for other protocols as well. In the context of phpMyAdmin, the current possible values are:

  • image/jpeg
  • image/png
  • text/plain
  • application/octetstream

The text/plain type can be chosen for a column containing any kind of text (for example, XHTML or XML text). In the Examples of transformations section, you will see which MIME type you are required to choose to achieve a specific effect.

Browser transformations

This is where we set the exact transformation to be done. More than one transformation may be supported per MIME type. For example, for the image/jpeg MIME type, we have two transformations available: image/jpeg: inline for a clickable thumbnail of the image, and image/jpeg: link to display just a link.

The following screenshot shows the list of the available transformations:

Browser transformations

A more complete transformation explanation and a list of the possible options are available on clicking the question mark icon next to Transformation options, and then clicking on the transformation descriptions link that appears.

Assigning values to transformation options

In the Examples of transformations section, we will see that some transformations accept options. For example, we can indicate the width and height in pixels for a transformation that generates an image. A comma is used to separate the values in the options list, and some options may need to be enclosed within quotes.

Some options have a default value, and we must be careful to respect the documented order for options. For example, if there are two options, and we only want to specify a value for the second option, we can use empty quotes as a placeholder for the first option, to let the system use its default value.

Requirements for image generation

Normal thumbnail generation requires that some components exist on the web server, and that a parameter in config.inc.php be correctly configured.

Configuring GD2 library availability verification

phpMyAdmin uses some internal functions to create the thumbnails. These functions need the GD2 library to be present on our PHP server.

phpMyAdmin can detect the presence of the correct GD2 library, but this detection takes some time. It also takes place not once per session, but almost every time an action is taken in phpMyAdmin.

Setting the $cfg['GD2Available'] parameter in config.inc.php to its default value'auto' indicates that the detection of the library's presence and version is needed.

If we know that the GD2 library is available, setting $cfg['GD2Available'] to yes will make execution quicker. If the GD2 library is not available, you are recommended to set this parameter to no.

To find out which GD2 library we have on the server, we can go to phpMyAdmin's home page and click on Show PHP information. If this link is not present, we need to set the $cfg['ShowPhpInfo'] parameter to true. We then look for a section titled gd and verify which version is identified. In the following screenshot, all is fine as we can see that the GD version is 2.X with JPEG and PNG support:

Configuring GD2 library availability verification

Asserting support of JPEG and PNG libraries

The PHP component in our web server needs to have support for the JPEG and PNG images if we want to generate thumbnails for these types of images. For more details, please refer to http://php.net/manual/en/ref.image.php.

Evaluating the impact of memory limits

On some servers, the default value in php.ini for memory_limit is 8M, meaning 8 MiB. This is too low for correct image manipulation, because the GD functions used to produce the final images need working memory. For example, in one test, a value of 11M in memory_limit was needed to generate the thumbnail from a 300 KiB JPEG image. Also, if multiple rows are viewed at once, more working memory will be needed.