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

Defining relations with the Designer

The Ajax-based Designer offers a visually-driven way of managing relations (both internal and foreign key-based), and defining the display column for each table. It can also act as:

  • A menu to access the structure of existing tables and to access the table creation page
  • A PDF schema manager, if we want a PDF schema encompassing all our tables

On the Designer workspace, we can work on the relations for all tables on the same panel. On the other hand, the Relation view shows the relations for only a single table at a time.

We access this feature from the Database view by clicking on the Designer menu tab.

Note

If this menu tab does not appear, it's because we are yet to install the phpMyAdmin configuration storage as described in Chapter 1.

Over viewing the interface

The Designer page contains the main workspace where the tables can be seen. This workspace will dynamically grow and shrink, depending on the position of our tables. The following screenshot demonstrates the Designer interface containing our three tables and the relations between them:

Over viewing the interface

A top menu contains icons whose description is revealed by hovering the mouse over them. The following table gives a summary of the goals for the top menu's icons:

Icon

Description

Show/Hide left menu

To display or hide the left hand menu.

Save position

Saves the current state of the workspace.

Create table

Quits the Designer and enters a dialog to create a table; we should take care of saving the position of tables before clicking on this.

Create relation

Puts the Designer in a relation-creating mode.

Choose column to display

Specifies which column represents a table.

Reload

Refreshes the table's information in case their structure has changed outside of the Designer.

Help

Displays an explanation about selecting the relations.

Angular links/Direct links

Specifies the shape of relation links.

Snap to grid

Influences the behavior of table movements, relative to an imaginary grid.

Small/Big All

Hides or displays the list of columns for every table.

Toggle small/big

Reverses the display mode of columns for every table, as this mode can be chosen for each table with its corner icon V or>.

Import/Export

Displays a dialog to import from an existing PDF schema definition or to export to it.

Move Menu

The top menu can move to right and back again.

A side menu appears when clicking on the Show/Hide left menu icon. Its purpose is to present the complete list of tables, so that you can decide which table appears on the workspace, and to enable access to the Structure page of a specific table. In this example, we choose to remove the book-copy table from the workspace as shown in the following screenshot:

Over viewing the interface

If we want to remove it permanently, we click on the Save position top icon. This icon also saves the current position of our tables on the workspace.

Tables can be moved on the workspace by dragging their title bars, and the list of columns for a table can be made visible/invisible with the help of upper-left icon of each table. In this list of columns, small icons show us the data type (numeric, text, and date), and also tell us whether this column is a primary key.

Defining relations

As we have already defined a relation with the Relation view, we will first see how to remove it. The Designer does not permit a change in a relation. However, the Designer allows the relation to be removed and defined.

The question mark icon displays a panel that explains where to click, in order to select a relation for subsequent deletion.

Defining relations

Let us click on the relation line to select it. We get a confirmation panel on which we click on Delete.

Defining relations

We can then proceed to recreate it. To do this, we start by clicking on the Create relation icon:

Defining relations

The cursor then takes the form of a short message saying Select referenced key. In our case, the referenced key is the id column of the author table; so we bring the cursor on this column and click on it. A validation is done, ensuring that we chose a primary or unique key.

Next, having changed the cursor to Select foreign key, we bring it to the author_id column of the book table and click on it again. This confirms the creation of the relation. Currently, the interface does not permit the creation of compound keys (having more than one column).

Defining foreign key relations

The procedure to delete or define a relation between InnoDB or PBXT tables is the same as that for internal relations. The only exception is that at the time of creation, a different confirmation panel appears enabling us to specify the on delete and on update actions.

Defining foreign key relations

Defining the display column

On the workspace, the name column in author table has a special background color. This indicates that this column serves as the display column. We can simply click on the Choose column to display icon, and drag the short message Choose column to display onto another column—for example, the phone column. This changes the display column to this column. If we were to drag the message to an existing display column, we would have removed the definition of this column as the display column for the table.

Exporting for PDF schema

In Chapter 15, we will see how to produce a PDF schema for a subset of our database. We can import the coordinates of tables from such a schema into the Designer's workspace, and conversely export them to the PDF schema. The Import/export coordinates icon is available for that purpose.