Mastering phpMyAdmin 3.1 for Effective MySQL Management

By Marc Delisle

In Detail

MySQL has gained wide popularity because of its open-source nature, performance, reliability, robustness, and support for various platforms.

phpMyAdmin is a web-based front-end to manage MySQL databases and has been adopted by a number of open-source distributors. It is one of the most widely used open-source applications written in PHP. phpMyAdmin supports a wide range of operations with MySQL. Currently, it can create and drop databases, create/drop/alter tables, delete/edit/add fields, execute any SQL statement, manage keys on fields, manage privileges, export data into various formats and is available in 52 languages. The powerful graphical interface of version 3.1 has made phpMyAdmin an indispensable tool for MySQL and web developers.

This is the first book that covers version 3.1, the latest version of phpMyAdmin. It has been fully updated from the previous version that covered phpMyAdmin Version 2.11. It is a comprehensive guide to unlocking the full potential of phpMyAdmin. It builds a solid understanding of the core capabilities of phpMyAdmin before walking you through every facet right from showing you how to set up and manage the database with clear, step-by-step instructions and screenshots to exploring the less well known and generally under-used features and capabilities of phpMyAdmin in depth. Along the way you'll build a more detailed understanding of SQL and how it works in MySQL.

Whether you are an experienced developer, system administrator, web designer or new to MySQL and phpMyAdmin, this book will show you how to increase your productivity and control when working with your MySQL.

Unlock the full potential of phpMyAdmin

Approach

The book is a step-by-step tutorial for phpMyAdmin explaining and demonstrating the potential of this tool with clear instructions and screenshots. After covering installation and basic features of phpMyAdmin it goes on to show its less well known and generally under-used features and capabilities.

Who this book is for

phpMyAdmin is a must-have tool for everyone who needs to manage their MySQL databases and tables in an effective manner. This book is a must-read for every serious phpMyAdmin user who would like to use this outstanding application to its full power.

• Language: English
• Publisher: Packt Publishing
• Release date: Mar 13, 2009
• ISBN: 9781847197870

Marc Delisle

Marc Delisle was awarded "MySQL Community Member of the year 2009" because of his involvement with phpMyAdmin. He started to contribute to the project in December 1998, when he made the multi-language version. He is still involved with phpMyAdmin as a developer and project administrator. Marc is a system administrator at Cegep de Sherbrooke, Québec, Canada. He has been teaching networking, security, and web application development. In one of his classes, he was pleased to meet a phpMyAdmin user from Argentina. Marc lives in Sherbrooke with his wife and they enjoy spending time with their four children.

Book preview

Table of Contents

Mastering phpMyAdmin 3.1 for Effective MySQL Management

Credits

About the author

About the reviewer

Preface

What this book covers

What you need for this book

Who is this book for

Conventions

Reader feedback

Customer support

Errata

Piracy

Questions

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

extension

PersistentConnections

connect_type, socket and port

compress configuration

Upgrading phpMyAdmin

Summary

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

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

Tree display of database names

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

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

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

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

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

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

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

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

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

Infrastructure and config.inc.php

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

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

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

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

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

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

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

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

A. History of phpMyAdmin

Early events

Project re-launch

Distributors

Evolution

GoPHP5 and the 3.x branch

Awards

Summary

B. Troubleshooting and Support

System requirements

Base configuration

Solving common errors

Error messages

Cannot load MySQL extension

#2003 - Can't connect to MySQL server

Socket problem (Linux/UNIX)

Named pipe problem (Windows)

MySQL said: Access denied

When using http authentication

When using http, cookie, or config authentication

Access denied ... using password: NO

Access denied ... using password: YES

Warning: cannot add header information

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

Other problems

Blank page or weird characters

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

Seeking support

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 versio]ns

Summary

Index

Mastering phpMyAdmin 3.1 for Effective MySQL Management

Marc Delisle

---

Mastering phpMyAdmin 3.1 for Effective MySQL Management

Copyright © 2009 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, Packt Publishing, nor its dealers or distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

First published: March 2009

Production Reference: 1050309

Published by Packt Publishing Ltd. 32 Lincoln Road Olton Birmingham, B27 6PA, UK.

ISBN 978-1-847197-86-3

www.packtpub.com

Cover Image by Marc Delisle (< [e-mail]>)

Credits

Author

Marc Delisle

Reviewer

Kai Oswald Seidler

Acquisition Editor

Shilpa Dube

Development Editor

Shilpa Dube

Technical Editor

Gaurav Datar

Copy Editor

Sumathi Sridhar

Indexer

Rekha Nair

Production Editorial Manager

Abhijeet Deobhakta

Editorial Team Leader

Akshara Aware

Project Team Leader

Lata Basantani

Project Coordinator

Rajashree Hamine

Proofreaders

Mark Reardon

Joel T. Johnson

Production Coordinator

Rajni R. Thorat

Cover Work

Rajni R. Thorat

About the author

Marc Delisle, owing to his involvement with phpMyAdmin, is a member of the MySQL Developers Guild that regroups community developers. He began contributing to this popular MySQL web interface in December 1998, when he made its first multi-language version. As a developer and project administrator, he has been actively involved with this software project since 2001.

Since 1980 Marc has worked at Cegep de Sherbrooke, Québec, Canada, as an application programmer and network manager. He has also been teaching networking, security, and PHP/MySQL application development. In one of his classes, he was pleased to meet a phpMyAdmin user from Argentina. Marc lives in Sherbrooke with his wife, and they enjoy spending time with their four children.

This book is an update to Marc's first book on phpMyAdmin, published by Packt Publishing in 2004, followed by Creating your MySQL Database: Practical Design Tips and Techniques, also by Packt Publishing.

I am truly grateful to Louay Fatoohi, who approached me for this book project, and to the Packt team whose sound comments were greatly appreciated during production. My thanks also go to Garvin Hicking, Alexander Marcus Turek, and Kai 'Oswald' Seidler—the reviewers for the successive editions of this book. Their sharp eyes helped in making this book clearer and more complete.

Finally, I wish to thank all contributors to phpMyAdmin's source code, translations, and documentation. The time they gave to this project still inspires me and continues to push me forward.

About the reviewer

Kai Oswald Seidler was born in Hamburg in 1970. He graduated from the Technical University of Berlin with a Diplom-Informatiker degree (the equivalent of a Master of Science degree) in Computer Science. In the '90s, he created and managed Germany's biggest IRCnet server—irc.fu-berlin.de, and co-managed one of the world's largest anonymous FTP servers—ftp.cs.tu-berlin.de.

He set up his first professional public web server in 1993. From 1993 to 1998, he was a member of Projektgruppe Kulturraum Internet—a research project on net culture and network organization. In 2002, he co-founded Apache Friends and created the multi-platform Apache web server bundle XAMPP. Around 2005, XAMPP became the most popular Apache stack worldwide.

In 2006, his third book, Das XAMPP-Handbuch, was published by Addison Wesley.

To Carole, André, Corinne, Annie, and Guillaume, with all my love.

Preface

Providing a powerful graphical interface for managing MySQL, phpMyAdmin is one of the most popular open source applications. While most developers use routine features of phpMyAdmin every day, few are aware of the power and potential its advanced features have. This book builds a solid understanding of the core capabilities of phpMyAdmin, and then walks you through every facet of this legendary tool.

Used by millions of developers, MySQL is the most popular open source database. It supports numerous large, dynamic websites and applications. MySQL has acquired this wide popularity by virtue of its open source nature, performance, reliability, robustness, and support for multiple platforms. However, this popularity has also been helped by the existence of phpMyAdmin—the industry standard administration tool that makes database management easy for both, the experienced developer and the novice one.

Bringing a web interface to MySQL has made phpMyAdmin an indispensable tool for MySQL and web developers, be they professionals or in the classroom. Every user can benefit from phpMyAdmin, by unlocking the full potential of this powerful application.

What this book covers

Chapter 1 gives us the reasons why we should use phpMyAdmin as a means of developing web applications. It then covers the downloading and installation procedures for phpMyAdmin.

Chapter 2 provides an overview of various authentication types used in MySQL. It then covers the security issues related to phpMyAdmin installation.

Chapter 3 gives us an overview of the phpMyAdmin interface. It includes the login panel, the left and the right panel including the Light and the Full mode, and the Query window.

Chapter 4 is all about database creation. It teaches us how to create a table, how to insert data manually, and how to sort the data.

Chapter 5 covers the aspects of data editing in phpMyAdmin. It teaches us handling NULL values, multi-row editing. Finally, it covers an important aspect of any database tool—data deletion.

Chapter 6 explores the subject of changing the structure of tables. Its primary focus is on editing field attributes and index management.

Chapter 7 deals with backups and exports. It lists various ways to trigger an export, available export formats, the options associated with export formats, and the various places where the export files may be sent.

Chapter 8 tells us how to bring back exported data created for backup and transfer purposes. It provides an overview of the various options available in phpMyAdmin to import data, and different mechanisms involved in importing SQL and CSV files. Finally, it covers the limitations that may be faced while importing files, and the ways to overcome them.

Chapter 9 presents the mechanisms that are useful for searching data effectively.

Chapter 10 covers ways to perform some operations that influence and can be performed on entire tables or databases as a whole. Finally, it deals with table maintenance operations for table repair and optimization.

Chapter 11 is where we start covering advanced features of phpMyAdmin. The chapter explains how to define inter-table relations. It also explains how to install the linked-tables infrastructure—a prerequisite for the advanced features.

Chapter 12 helps us enter our own SQL commands. The chapter also gives us an overview of Query window—the window used to edit an SQL query. Finally, it also helps us to obtain the history of typed commands.

Chapter 13 covers the multi-table query generator, which allows us to produce multi-table queries without actually typing them.

Chapter 14 covers Bookmarks—one of the features of the linked-tables infrastructure. It covers how to record bookmarks and how to manipulate them. Finally, it covers passing parameters to bookmarks, and executing bookmarks directly from the pma_bookmark table.

Chapter 15 gives an overview of how to produce documentation, which explains the structure of the databases, using the tools offered by phpMyAdmin.

Chapter 16 explains how to apply transformations to the data in order to customize its format at view time.

Chapter 17 covers phpMyAdmin's support for the MySQL features that are new in versions 5.0 and 5.1.

Chapter 18 is about the administration of a MySQL server, focusing on the management of user accounts and privileges. The chapter discusses how a system administrator can use phpMyAdmin's server management features for day-to-day user account maintenance, server verification, and server protection.

Appendix A provides a history of the phpMyAdmin project, from its roots back in 1998 through the project re-launch in 2001, and its subsequent evolution.

Appendix B explains how to troubleshoot phpMyAdmin by examining some of its error messages, and proposing appropriate solutions. It also explains how to interact with the development team for support, bug reports, and contributions.

What you need for this book

You need to have access to a server or workstation that has the following installed:

MySQL version 5.0 or newer

PHP version 5.0 or newer

Web server software with PHP support (this book explains phpMyAdmin's installation on Apache and IIS)

Who is this book for

phpMyAdmin is a must have tool for everyone who needs to manage their MySQL databases and tables in an effective manner. This book is a must read for every serious phpMyAdmin user who would like to use this outstanding application to its fullest potential.

Conventions

In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.

Code words in text are shown as follows: If we are using the config authentication type, no login panel is displayed.

A block of code will be set as follows:

$cfg['MySQLManualBase'] = 'http://www.mysql.com/doc/en';

$cfg['MySQLManualType'] = 'searchable';

When we wish to draw your attention to a particular part of a code block, the relevant lines or items will be shown in bold:

$cfg['Servers'][$i]['AllowDeny']['order'] = 'allow,deny';

Any command-line input or output is written as follows:

tar -xzvf phpMyAdmin-3.1.1-all-languages.tar.gz

New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in our text like this: The Home link from the left panel is used to display this page.

Note

Warnings or important notes appear in a box like this.

Tip

Tips and tricks appear like this.

Reader feedback

Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.

To send us general feedback, simply drop an email to <[e-mail]>, and mention the book title in the subject of your message.

If there is a book that you need and would like to see us publish, please send us a note in the SUGGEST A TITLE form on www.packtpub.com or email .

If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.

Customer support

Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.

Errata

Although we have taken every care to ensure the accuracy of our contents, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in text or code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration, and help us to improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the let us know link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata added to any list of existing errata. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.

Piracy

Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works in any form on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.

Please contact us at <[e-mail]> with a link to the suspected pirated material.

We appreciate your help in protecting our authors, and our ability to bring you valuable content.

Questions

You can contact us at <[e-mail]> if you are having a problem with any aspect of the book, and