Mastering phpMyAdmin 2.11 for Effective MySQL Management: Increase your MySQL productivity and control by discovering the real power of phpMyAdmin 2.11

337 Pages·2008·8.7 MB·English

by  Marc Delisle

Mastering phpMyAdmin 2.11 for
Effective MySQL Management
Increase your MySQL productivity and control by
discovering the real power of phpMyAdmin 2.11
Marc Delisle

BIRMINGHAM - MUMBAI
Mastering phpMyAdmin 2.11 for Effective
MySQL Management
Copyright © 2008 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 authors, 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 2008
Production Reference: 1110308
Published by Packt Publishing Ltd.
32 Lincoln Road
Olton
Birmingham, B27 6PA, UK.
ISBN 978-1-847194-18-3
www.packtpub.com
Cover Image by Bruno Abarca ([email protected])
Credits
Author Project Coordinators
Marc Delisle Aboli Mendhe
Lata Basantani
Reviewers
Alexander Marcus Turek
Indexer
Garvin Hicking
Monica Ajmera
Kai "Oswald" Seidler
Proofreader
Acquisition Editor
Cathy Cumberlidge
Rashmi Phadnis
Production Coordinator
Technical Editor
Aparna Bhagat
Ajay Shanker
Cover Work
Editorial Manager
Aparna Bhagat
Mithil Kulkarni
Project Manager
Abhijeet Deobhakta
About the Author
Marc Delisle is a member of the MySQL Developers Guild—which regroups
community developers—because of his involvement with phpMyAdmin. He started
to contribute to this popular MySQL web interface in December 1998, when he made
the first multi-language version. He has been actively involved with this software
project since May 2001 as a developer and project administrator.
Marc has worked since 1980 at Collège 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 Marc's first one, and was followed by "Creating your MySQL Database:
Practical Design Tips and Techniques", also with 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 the 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.
To Carole, André, Corinne, Annie, and Guillaume, with all my love.
About the Reviewers
Garvin Hicking is a German webdeveloper working for Faktor E GmbH. He
creates web applications using PHP, MySQL, and in his free time enjoys to work on
OpenSource-projects like phpMyAdmin or Serendipity. At times when he's away
from the computer, he likes going to the movies with his girlfriend and friends, or
taking pictures.
Alexander Marcus Turek was born on June 2nd, 1984 in Düsseldorf, the
capital of the German province Northrhine-Westphalia. Currently, he's studying
Information Engineering and Management at the University of Karlsruhe, but his
origin is Mülheim an der Ruhr, the home of his family. He first got in touch with
the Web in 1998, when he won a 28.8k modem at the CeBit Home in Hannover,
Germany. A few months later, he learned HTML and started his first Web project,
a German game patch archive called Rabus' Update Site, which he renamed to
bugfixes.info, when the .info domains became available. In the meantime, he
switched from static HTML to PHP in order to be able to manage the growing
archive more efficiently. He kept on learning PHP when trying to extend the portal.
Because the flatfile-based database system became too slow when searching the
still growing archive, he also switched to MySQL in 2001. This is when he got in
touch with phpMyAdmin and the project. He started with revising its language
files because they were a bit outdated and inconsistent. He had fun doing so, and
continued with grabbing some bug reports and submitting patches for them. Loïc
Chapeaux, one of the two co-maintainers at that time, added him to the developers
list and gave him a CVS account in March 2002, so he could merge his patches by
himself. Since then, he has mainly worked on the compatibility with MySQL 4.0,
reworked the server administration area, developed a simple abstraction layer in
order to support MySQLi, and continued with compatibility fixing—this time for
MySQL 4.1 and 5.0. Unfortunately, his studies and phpMyAdmin became too time
consuming, and he had to stop working on bugfixes.info in 2003.
Kai "Oswald" Seidler was born in Hamburg in 1970. He graduated from
Technical University of Berlin with a Diplom Informatiker degree (Master of Science
equivalent) in Computer Science. In the 90's he created and managed Germany's
biggest IRCnet server irc.fu-berlin.de, and co-managed one of the world's largest
anonymous FTP server ftp.cs.tu-berlin.de.
He professionally set up his first public web server in 1993. From 1993 until 1998 he
was member of Projektgruppe Kulturraum Internet, a research project on net culture
and network organization. 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.
Table of Contents
Preface 1
Chapter 1: Introducing phpMyAdmin 7
PHP and MySQL: The Leading Open-Source Duo 8
What is phpMyAdmin? 8
History 9
Awards 13
phpMyAdmin Features Summary 13
Summary 15
Chapter 2: Installing phpMyAdmin 17
System Requirements 18
GoPHP5 18
Downloading the Files 18
Installation 19
Installation on a Remote Server Using a Windows Client 19
Installation on a Local Linux Server 20
Installation on Local Windows Servers (Apache, IIS) 20
First Connection Configuration 21
Configuration Principles 21
Web-Based Setup Script 22
Manual Creation of config.inc.php 27
Tips for Editing config.inc.php on a Windows Client 27
The config.inc.php File 27
PmaAbsoluteUri 28
Server-Specific Sections 28
extension 29
PersistentConnections 29
connect_type, socket and port 29
compress Configuration 30
Authentication Type: config 30
Table of Contents
Testing the First Connection 31
Multi-Server Configuration 31
Servers Defined in the Configuration File 31
Arbitrary Server 32
Advanced Authentication 33
Authentication Types Offered 33
The Control User 33
Logging Out 34
HTTP Authentication 34
Cookie Authentication 35
Signon 36
Security 37
Directory-Level Protection 37
IP-Based Access Control 38
Rules 38
Order of Interpretation for Rules 39
Simplified Rule for Root Access 40
Restricting the List of Databases 40
Protecting In-Transit Data 41
Upgrading phpMyAdmin 41
Summary 42
Chapter 3: Interface Overview 43
Panels and Windows 43
Login Panels 43
Left and Right Panels 43
Home Page 44
Views 44
Query Window 45
Starting Page 45
Window Titles Configuration 45
General Icon Configuration 45
Natural Sort Order for Database and Table Names 46
Language Selection 46
Themes 48
Theme Configuration 48
Theme Selection 49
Left Panel 49
Logo Configuration 50
Database and Table List 50
Light Mode 51
Full Mode 53
Table Short Statistics 54
[ ii ]
Table of Contents
Quick-Browsing a Table 54
Nested Display of Tables within a Database 54
Server-List Choice 56
Handling Many Databases or Tables 56
Right Panel 57
Home Page 58
Database View 59
Table View 61
Server View 62
Icons for Home Page and Menu Tabs 62
Query Window 63
Site-Specific Header and Footer 64
MySQL Documentation Links 64
Summary 64
Chapter 4: First Steps 65
Database Creation 65
No Privileges? 65
First Database Creation Is Authorized 66
Creating Our First Table 68
Choosing the Fields 68
Table Creation 68
Choosing Keys 70
Manual Data Insertion 72
Data Entry Panel Tuning for CHAR and VARCHAR 74
Browse Mode 75
SQL Query Links 76
Navigation Bar 77
Query Results Operations 79
Sorting Results 80
Color-Marking Rows 81
Limiting the Length of Each Column 81
Browsing Distinct Values 82
Browse-Mode Customization 83
Creating an Additional Table 83
Summary 84
Chapter 5: Changing Data 85
Edit Mode 85
Moving to Next Field with the Tab Key 86
Moving with Arrows 87
Handling NULL Values 87
[ iii ]