phpMyAdmin - MySQL Database Manager

About the MySQL Database Manager The MySQL Database Manger is a powerful tool that allows you to create, manage and administer MySQL databases on your website. Built upon the popular open source utility, phpMyAdmin, DB Manager includes an intuitive GUI that lets both novice users and seasoned database administrators work quickly and efficiently. This guide is a comprehensive resource covering all features of DB Manager. After reading it, you will know how to:

• create databases (either from scratch or by importing data from another source) • delete databases • design and populate tables • set up user accounts so that others can access your data • export databases

You will also gain a basic understanding of SQL, or Standard Query Language. SQL is a command language consisting of keywords like SELECT, INSERT and DROP and the syntax governing how they may be used (SQL statements are also known as queries ). You can type queries directly or use the GUI to build them visually; regardless of the method you choose, DB Manager displays the queries generated as feedback.

The Database Manager GUI consists of two main parts: the main window , which you use to create databases, users and ODBC connections, and the phpMyAdmin window, the utility you use to manage a selected database.

Creating databases

Database Manager is installed with a default database that uses the same name as your domain name (with underbars replacing periods). For example, if your domain is mydomain.com , your default database will be mydomain_com . As you create more databases, the default name (with a trailing underbar) is used as a prefix for them, for example, mydomain_com_contacts . Once you have created a database, you can build the tables associated with it in phpMyAdmin.

Changing the Language of a Database You may change the language in which you view your database. This does not affect the information in the database. Only the method of viewing the data is changed.

Building Tables Tables are the basic building blocks of databases. They consist of columns (or fields ) and rows . Each row is a single data record; for example, if you had a table for storing customer names, each row might contain a customerID field, a first_name field and a last_name field. You use phpMyAdmin to create, alter and delete records from tables.

Creating table structure Before you can insert data into table, you must create its structure. The structure of a table is determined by its columns, with each column in the table set up to handle a particular kind of data. Each column has specific attribute settings.

• Field the name of the column
• Type the data type of the column
• Length/values the appropriate length/values setting for the selected data type
• Attributes any optional attribute you wish to apply (for example, UNSIGNED to only allow zero and positive numbers.)
• Null a keyword which determines if the field for the record can be left blank, set to either NULL (can be empty) or NOT NULL (cannot be empty)
• Default a default value for the column (if one is not provided when the record is inserted into the table)
• Extra use AUTO_INCREMENT to automatically increment the value in an ID field.

To create a table:

1 In DBManager Home, select the database into which you want to add the table.
2 Click the Manage Database button.
3 Enter a name for the table, and specify the number of fields (columns) it will contain.
4 Click Go.
5 Enter column information.
6 Click Save.

phpMyAdmin refreshes and shows the table structure as well as the SQL query used to create it. You are now ready to insert rows (records) into your table.

Inserting rows The rows of a table store its actual information (data). Adding rows into a table is similar to adding rows into a spreadsheet.

Setting column indexes Databases typically include several tables and use special flags—called indexes or keys —to reference these tables, ensure uniqueness of data or optimize text search queries. The four types of column indexes (along with their associated phpMyAdmin Action buttons) are:

• Primary The identifying column of a table. Each table may only contain one primary key.
• Index Applied to a key to improve the performance of queries involving it. You can set as many indexes as you wish.
• Unique A key set on a column to ensure that no two rows in the table use the same value for the field.
• Full Text A key you set on a text field to allow it to be searched.

Altering table structure You can alter the structure of a table you have created. You can add, delete (drop), or rename a column, or change its attributes.

Deleting records To remove actual data from a table (for example, to remove selected customers from a customer table) you delete the relevant records.

Browsing Records You can control how tables are displayed when you browse them in phpMyAdmin. Specifically, you can:

• toggle full text or partial text views of records containing long text entries.
• apply a different table layout.
• set a result limit (to control how many records appear per screen).
• change how rows are sorted.

Toggling full text display If the table you are browsing includes a text type field (e.g., LONGTEXT), you can choose to expand the field (to show full text) or collapse the field (to show partial text and converse screen real estate).

Sorting table rows You can sort a table by column (in ascending or descending order) for any column that is indexed; an auto_increment column (such as a customerID field) will always be indexed.

Searching You can use phpMyAdmin to search for a particular record or records in a table. You can set specific search parameters for each column in the table, set which field the results will be sorted on and how they will be displayed. For integer fields (columns that store numeric values) you can search for equal (=), not equal (!=), greater (>), lesser (<), greater-or-equal (=>) or lesser-or-equal (=<). For text fields (for example, the VARCHAR data type) you can search for text that matches (=) or does not match. Both integer and text fields also support the LIKE keyword; to use this option, enclose your search string between percentage signs (%), for example,%456% or %Rob%.

Performing Table Operations A table operation is an action you perform on a table—moving or copying it to another database, reordering its columns, or renaming it. You can also use table operations to perform maintenance on a table, for example, restoring a table that you suspect has been corrupted.

Moving tables When you move a table, you remove it from one database and add it to another as-is. You can not overwrite an existing table in another database with the table you are moving.

Copying tables When you copy a table, you have a number of considerations to make. You can choose to:
• copy just the structure (columns), just the data (records) or both (structure and data).
• include a “drop table” statement in the SQL query phpMyAdmin sends to the database; this will overwrite an existing table that has the same name as the copied table.
• check the auto_increment option to append the records of the copied table to the existing table; the starting auto_increment value (typically an ID field) will be one increment above the highest value in the existing table.
• switch to the copied table once the action has been performed.

Performing table maintenance Table maintenance operations are advanced functions. You should not use them without first reading and understanding the MySQL documentation. Checking is used to determine if a table contains errors; it runs a 'myisamchk -m' on 'MyISAM' and 'InnoDB' files from within MySQL. For `MyISAM' tables the key statistics are updated. You can get many rows of information for each checked table. The last row will be of `Msg_type status' and should normally be `OK'. If you don't get `OK', or `Not checked' you should run a repair of the table. Analyzing is used to analyze and store the key distribution for the table. During the analysis, the table is locked with a read lock. This works on `MyISAM' and `BDB' tables and is equivalent to running `myisamchk -a' on the table. MySQL uses the stored key distribution to decide in which order tables should be joined when one does a join on something other than a constant. Repairing is used to fix a table that you suspect may have been corrupted. Executing this operation returns a message describing whether a repair was warranted, and what exactly the problem was. Do not attempt to repair a table without first checking it. Optimizing is used to reclaim unused space and defragment the data file. OPTIMIZE TABLE' works only on `MyISAM' and `BDB' tables. For `BDB' tables, `OPTIMIZE TABLE' is mapped to `ANALYZE TABLE'. Flushing is used to clear some of the internal caches MySQL uses. The flush commands include actions against: HOSTS, DES_KEY_FILE, LOGS, PRIVILEGES, QUERY CACHE, and TABLES. You can run these commands through the mysqladmin utility using `flush-hosts', `flush-logs', `reload', or `flush-tables' commands.

Managing Users Database Manager supports a sophisticated privilege system that grants and restricts privileges to database users based on their roles . You create user accounts (and associated ODBC connections) to allow users to connect remotely to your databases and perform actions on them, typically through a web page which includes a connection script written in PHP.

Setting up an ODBC connection ODBC (Open DataBase Connectivity) is an open standard which allows virtually any system or application to access a database. You can set up an ODBC for users to allow them to connect to your database from a remote location.

Exporting Database Manager allows you to export a database (or selected tables) to a file. Some file formats allow you to view the records in another application, such as Microsoft Excel. The SQL format is used for backup/restoration purposes; it creates an SQL file (a series of queries) that can be imported back into MySQL to restore a database.

About export formats Database Manager can export databases (or tables) in the following formats:
• LaTex A document preparation format for high-quality typesetting.
• CSV ( Comma Separated Values ) A text file in which columns are delimited by commas (or semicolons) and records by line breaks.
• CSV for MS Excel A CSV file optimized for use in Microsoft Excel.
• XML ( Extensible Markup Language ) A flexible text format derived from SGML (Standard Generalized Markup Language).
• SQL ( Standard Query Language ) The command language used to administer databases.

Exporting databases When you export a database, you set options related to the export format you chose. Once you have set these options, you can select the tables you want to include and export the file. When you export SQL, you can choose to export the structure (columns), data (records) or both. The structure options are:

• inclusion of a “drop table” statement; if this option is checked, restoring the database will drop (delete) any existing database with the same name.
• addition of an “auto_increment” value.
• enclosing tables and field names with back quotes.
• adding creation, update and check information to the comments section. And the data options are:
• setting export type (INSERT, UPDATE or REPLACE)
• use complete inserts.
• use extended inserts.
• use delayed inserts.