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

Chapter 4. Creating and Browsing Tables

Having seen the overall layout of phpMyAdmin, we are ready to create a database, create our first table, insert some data into it, and browse it. These first steps are intentionally simple, but they will give you the foundation on which more complex operations will be achieved later. At the end of the chapter, we will have at our disposal the two basic tables on which the remaining exercises are based.

Creating a database

Before creating a table, we must ensure that we have a database for which the MySQL server's administrator has given us the CREATE privilege. The following possibilities exist:

  • The administrator has already created a database for us, and we see its name in the navigation panel; we don't have the right to create an additional database.
  • We have the right to create databases from phpMyAdmin.
  • We are on a shared host, and the host provider has installed a general web interface (for example, cPanel) to create MySQL databases and accounts; in this case, we should visit this web interface now and ensure we have created at least one database and one MySQL account.

The Databases panel in Server view is the place to go to find the database creation dialog. Note that a configuration parameter, $cfg['ShowCreateDb'], controls the display of the Create new database dialog. By default, it is set to true, which shows the dialog.

No privileges

If you do not have the privilege to create a database, the panel displays a No privileges message under the Create new database label. This means that you must work with the databases already created for you, or ask the MySQL server's administrator to give you the necessary CREATE privilege.

Note

If you are the MySQL server's administrator, refer to Chapter 19.

First database creation is authorized

If phpMyAdmin detects that we have the right to create a database, the dialog appears as shown in the following screenshot:

First database creation is authorized

In the input field, a suggested database name appears if the $cfg['SuggestDBName'] parameter is set to TRUE, which is the default setting. The suggested database name is built according to the privileges we possess.

If we are restricted to the use of a prefix, the prefix might be suggested in the input field. (A popular choice for this prefix is the username, which might or might not be followed by an underscore character.) Note that, in this case, the prefix is followed by an ellipsis mark, added by phpMyAdmin. We should remove this ellipsis mark and complete the input field with an appropriate name.

First database creation is authorized

The Collation choice can be left unchanged for now. With this dialog, we could pick a default character set and collation for this database. This setting can be changed later (refer to Chapter 9 for more information on this).

We will assume here that we have the right to create a database named marc_book. We enter marc_book in the input field and click on Create. Once the database has been created, we will see the following screen:

First database creation is authorized

Notice the following:

  • The title of the main panel has changed to reflect the fact that we are now located in this database
  • A confirmation message regarding the creation is displayed
  • The navigation panel has been updated; we see marc_book
  • By default, the SQL query sent to the server by phpMyAdmin to create the database is displayed in color

Note

phpMyAdmin displays the query it generated, because $cfg['ShowSQL'] is set to TRUE. Looking at the generated queries can be a good way of learning SQL.

As the generated queries could be large and take much of the on-screen room, the $cfg['MaxCharactersInDisplayedSQL'] acts as a limit. Its default value of 1000 should be a good balance between seeing too few and seeing too many of the queries, especially when doing large imports.

It is important to examine the phpMyAdmin feedback to ascertain the validity of the operations we make through the interface. This way, we can detect errors like typos in the names, or creation of a table in the wrong database. phpMyAdmin retrieves error messages from the MySQL server and displays them on the interface.