Mastering phpMyAdmin 3.1 for Effective MySQL Management Table of Contents

Table of Contents

• Preface

• Chapter 1: Introduction and Installation
• Introducing phpMyAdmin
• Web applications
• PHP and MySQL:The leading open source duo
• What is phpMyAdmin?
• phpMyAdmin features
• Installing phpMyAdmin
• Required information
• System requirements
• Downloading the files
• Installation procedure
• Installation on a remote server using a Windows client
• Installation on a local Linux server
• Installation on local Windows servers (Apache, IIS)
• Configuring phpMyAdmin
• The config.inc.php file
• Permissions on config.inc.php
• Configuration principles
• Web-based setup script
• Manual creation of config.inc.php
• Tips for editing config.inc.php on a Windows Client
• Description of some configuration parameters
• PmaAbsoluteUri
• Server-specific sections
• Upgrading phpMyAdmin
• Summary

• Chapter 2: Authentication and Security
• MySQL authentication
• Root user without password
• Single-user authentication using config
• Testing the connection
• Multi-user authentication
• Authentication types offered
• The control user
• Logging out
• HTTP authentication
• Cookie authentication
• Signon authentication
• Multi-server configuration
• Servers defined in the configuration file
• Arbitrary server
• Security
• Directory-level protection
• Error handling
• IP-based access control
• Rules
• Order of interpretation for rules
• Simplified rule for root access
• Restricting the list of databases
• Protecting in-transit data
• Swekey hardware authentication
• Configuration
• Usage
• Security note
• Summary

• Chapter 3: Interface Overview
• Panels and windows
• Login panels
• Left and right panels
• Homepage
• Views
• Query window
• Starting page
• General customization
• Window titles configuration
• Icon configuration
• Natural sort order for database and table names
• Site-specific header and footer
• MySQL documentation links
• Themes
• Theme configuration
• Theme selection
• The color picker
• Sliders
• Character sets, collations and language
• Collations
• Unicode and UTF-8
• Language selection
• Effective character sets and collations
• Left panel (navigation)
• Logo configuration
• Database and table list
• Light mode
• Full mode
• Table short statistics
• Table quick-access icon
• Nested display of tables within a database
• Server-list choice
• Handling many databases or tables
• Limits on the interface
• Improving fetch speed
• Right panel (main)
• Homepage
• Database view
• Table view
• Server view
• Icons for homepage and menu tabs
• Query window
• Summary

• Chapter 4: First Steps
• Database creation
• No privileges?
• First database creation is authorized
• Creating our first table
• Choosing the fields
• Table creation
• Choosing keys
• Manual data insertion
• Data entry panel tuning for CHAR and VARCHAR
• Browse mode
• SQL query links
• Navigation bar
• Query results operations
• Sorting results
• Headwords
• Color-marking rows
• Limiting the length of each column
• Display options
• Browsing distinct values
• Browse-mode customization
• Creating an additional table
• Summary

• Chapter 5: Changing Data
• Edit mode
• Moving to next field with the tab key
• Moving with arrows
• Handling NULL values
• Applying a function to a value
• Duplicating rows of data
• Multi-row editing
• Editing the next row
• Deleting data
• Deleting a single row
• Deleting many rows
• Deleting all the rows in a table
• Deleting all rows in many tables
• Deleting tables
• Deleting databases
• Summary

• Chapter 6: Changing Table Structures
• Adding a field
• Vertical mode
• Horizontal mode
• Editing field attributes
• TEXT
• BLOB (Binary Large Object) fields
• Binary content uploads
• ENUM and SET
• DATE, DATETIME, and TIMESTAMP
• Calendar pop up
• TIMESTAMP options
• Bit
• Index management
• Single-field indexes
• Multi-field indexes and index editing
• FULLTEXT Indexes
• Table optimization—explaining a query
• Detection of index problems
• Summary

• Chapter 7: Exporting Structure and Data (Backup)
• Dumps, backups, and exports
• Scope of the export
• Database exports
• The export subpanel
• SQL
• SQL options
• The "Save as file" subpanel
• File name template
• Compression
• Choice of character set
• Kanji support
• CSV
• CSV for MS Excel
• PDF
• Microsoft Excel 2000
• Microsoft Word 2000
• LaTeX
• XML
• Native MS Excel (pre-Excel 2000)
• Open document spreadsheet
• Open document text
• YAML
• CodeGen
• Texy! text
• Table exports
• Split-file exports
• Selective exports
• Exporting partial query results
• Exporting and checkboxes
• Multi-database exports
• Saving the export file on the server
• User-specific save directories
• Memory limits
• Summary

• Chapter 8: Importing Structure and Data
• Limits for the transfer
• Time limits
• Other limits
• Partial imports
• Temporary directory
• Importing SQL files
• Importing CSV files
• Differences between SQL and CSV formats
• Exporting a test file
• CSV
• CSV using LOAD DATA
• Requirements
• Using the LOAD DATA interface
• Web server upload directories
• Summary

• Chapter 9: Searching Data
• Daily usage of phpMyAdmin
• Single-table searches
• Entering the search subpage
• Search criteria by field—query by example
• Searching for empty / non-empty values
• Print view
• Wildcard search
• Case sensitivity
• Combining criteria
• Search options
• Selecting the fields to be displayed
• Ordering the results
• Applying a WHERE clause
• Obtaining distinct results
• Complete database search
• Restricting search to a column
• Summary

• Chapter 10: Table and Database Operations
• Table maintenance
• Changing table attributes
• Table storage engine
• Table comments
• Table order
• Table collation
• Impact of switching connection collation
• Table options
• Renaming, moving, and copying tables
• Appending data to a table
• Multi-table operations
• Repairing an "in use" table
• Database operations
• Renaming a database
• Copying a database
• Summary

• Chapter 11: The Relational System
• Relational MySQL
• InnoDB and PBXT
• Linked-tables infrastructure
• Goal of the infrastructure
• Location of the infrastructure
• Installing linked-tables infrastructure
• Multi-user installation
• Single-user installation
• Defining relations with the relation view
• Internal relations
• Defining the relation
• Defining the display field
• Foreign key relations
• Foreign keys without linked-tables infrastructure
• Defining relations with the Designer
• Interface overview
• Defining relations
• Foreign key relations
• Defining the display field
• Exporting for PDF schema
• Benefits of the defined relations
• Foreign key information
• The drop-down list of foreign keys
• The browseable foreign-table window
• Referential integrity checks
• Automatic updates of metadata
• Column-commenting
• Automatic migration
• Summary

• Chapter 12: Entering SQL Commands
• The SQL query box
• The Database view
• The Table view
• The Fields selector
• Clicking into the query box
• The Query window
• Query window options
• Session based SQL history
• Database based SQL history (permanent)
• Editing queries in the query window
• Multi-statement queries
• Pretty printing (syntax-highlighting)
• The SQL Validator
• System requirements
• Making the Validator available
• Validator results
• Standard-conforming queries
• Non-standard-conforming queries
• Summary

• Chapter 13: The Multi-Table Query Generator
• Choosing tables
• Column criteria
• Field selector: Single-column or all columns
• Sorts
• Showing a column
• Updating the query
• Criteria
• Adjusting the number of criteria rows
• Adjusting the number of criteria columns
• Automatic joins
• Executing the query
• Summary

• Chapter 14: Bookmarks
• Comparing bookmark and query history features
• Bookmark creation
• Creating a bookmark after a successful query
• Storing a bookmark before sending a query
• Public bookmarks
• The default initial query for a table
• Multi-query bookmarks
• Recalling from the bookmarks list
• Bookmark execution
• Bookmark manipulation
• Bookmark parameters
• Creating a parameterized bookmark
• Passing a parameter value to a bookmark
• Executing bookmarks by browsing the pma_bookmark table
• Summary

• Chapter 15: System Documentation
• Structure reports
• Using print view
• The database print view
• The selective database print view
• The table print view
• The data dictionary
• Relational schema in PDF
• Adding a third table to our model
• Editing PDF pages
• Page planning
• Creating a new page
• Editing a page
• Displaying a page
• A note about fonts used
• Using the Designer for PDF layout
• Summary

• Chapter 16: MIME-Based Transformations
• Display behavior in browse mode
• Display options
• Enabling transformations
• The MIME column's settings
• MIME types
• Browser transformations
• Transformation options
• Requirements for image generation
• The GD2 library
• The JPEG and PNG libraries
• Memory limits
• Examples of transformations
• Clickable thumbnail (.jpeg or .png)
• Links to an image
• Date formatting
• Links from text
• text/plain: link
• text/plain: imagelink
• Preserving the original formatting
• Displaying parts of a text
• Download link
• Hexadecimal representation
• SQL pretty printing
• IP address
• External applications
• External application example: In-cell sort
• Summary

• Chapter 17: MySQL 5.0 and 5.1 Support
• Views
• Manually creating a view
• Right panel and views
• Creating a view from results
• Renaming a view
• Performance hint
• Routines—stored procedures and functions
• Creating a stored procedure
• Changing the delimiter
• Entering the procedure
• Testing the procedure
• Manipulation
• Manually creating a function
• Testing the function
• Exporting stored procedures and functions
• Triggers
• Manually creating a trigger
• Testing the trigger
• Information_schema
• Profiling
• Partitioning
• Table creation
• Operations
• Exporting
• Event scheduler
• Activating the scheduler
• Granting EVENT permission
• Creating an event
• Event manipulation
• Exporting
• BLOB streaming
• System requirements
• Configuration
• Implementation limitations in phpMyAdmin
• Creating the PBMS system tables
• Table preparation
• Uploading to BLOB repository
• Streaming the data from repository
• Changing repository data
• Summary

• Chapter 18: MySQL Server Administration
• User and privileges management
• The user overview
• Privileges reload
• Adding a user
• User name
• Host
• Password
• Database creation and rights
• Global privileges
• Resource limits
• Editing a user
• Edit privileges
• Database-specific privileges
• Changing the password
• Changing login information or copying a user
• Removing a user
• Database information
• Enabling statistics
• Sorting statistics
• Checking the database privileges
• Dropping selected databases
• Server information
• Server status verification
• The general status page
• InnoDB status
• Server variables
• Server processes
• Storage engines
• Available character sets and collations
• The binary log
• Summary

• Appendix A: History of phpMyAdmin
• Early events
• Project re-launch
• Distributors
• Evolution
• GoPHP5 and the 3.x branch
• Awards
• Summary

• Appendix B: Troubleshooting and Support
• Base configuration
• Solving common errors
• #2003 - Can't connect to MySQL server
• Named pipe problem (Windows)
• MySQL said: Access denied
• When using http authentication
• MySQL said: Error 127, Table Must Be Repaired
• BLOB column used in key specification without a key length
• IIS: No Input File Specified
• A "404: page not found" error when modifying a row
• Not being able to create a database
• Problems importing large files or uploading large BLOB files
• MySQL root password lost
• Duplicate field names when creating a table
• Authentication window displayed more than once
• Column size changed by phpMyAdmin
• Seeing many databases that are not ours
• Not being able to store a value greater than 127

• FAQs
• Help forums
• Creating a SourceForge account
• Choosing the thread title
• Reading the answers
• Support tracker
• Bug tracker
• Environment description
• Bug description

• Contributing to the project
• The code base
• Translation updates
• Patches

• Future phpMyAdmin versions
• Summary

• Index