The following information explains how to use the basic features in phpMyAdmin:

A. CREATING DATABASES AND TABLES

Creating a Database

1. Login to your Control Panel in the Members section.
2. Click the 'Site Tools' tab.
3. Click the 'MySQL Database' link.
4. Click the [Add New Database] link.
5. Enter a name for your database in the 'Add New Database' text box and click Create.
6. Write down the database code and click on [Return to Database List] link.

Creating a Table

1. Click the phpMyAdmin link of the database you wish to create a table for.
2. In this new window, click the Database link under 'MySQL'.
3. Under 'Database' click the code of the database you wish to create a table for.
4. Under 'Create new table on database:' enter the following information:

Name - Enter the name of the table.
Fields - Enter the number of fields for the table.

5. Click Go.
6. On the next screen, enter the following information for each field of the table:

Field – Enter a name for the field.
Type – Select the type of field. For more information, click on [Documentation] at the bottom of the screen.
Length/Values – Depending on which type of field you have, you may need to enter a variable here. For more information, click on [Documentation] at the bottom of the screen.
Attributes – Select an Attribute. For more information, click on [Documentation] at the bottom of the screen.
Null – Select 'not null' for fields that must have a value entered and 'null' for fields which can be left empty.
Default – Enter a value if you want the field to have a default value.
Extra – Select whether or not you want the field to automatically go in increments. For example, you may want to auto-increment a field so that each of the records will have a different number associated with it.

5. Select whether you want the field to be 'Primary', 'Index', and/or 'Unique'. For more information, click on [Documentation] at the bottom of the screen.
6. Enter a comment for the table if you wish.
7. Click Save.

B. MANAGING DATABASES AND TABLES

Databases and their tables can be edited by clicking on the name of the database on the left-hand side of the PhpMyAdmin program window. This will take you to the database homepage which provides the following links and options:

Table Management Links

On the homepage of the database, all of the tables will be listed with links to 'Browse', 'Select', and 'Insert' records. For more details on using these features see the 'Managing Tables and Fields' section of this document. Links to the following options are also provided.

Properties – Takes you to the home page of the table. For more information about this section, visit the 'Managing Tables and Fields' section of this document.
Drop – Allows you to delete a table entirely. You will be asked to confirm the deletion.
Empty – Makes it possible to empty the table of all information without changing the structure.

Printing Tables

Clicking 'Print view' will list all of the tables in the database and the number of records in a simple printable format.

Running Quick Queries

(Requires MySQL programming knowledge.)
Queries make it possible to add, delete, edit, and select records; perform a database dump; etc.

For example, the query below deletes all records from 'table_name' where 'column_name' has the value of 'test'. For more information, click on 'Documentation'.

delete from table_name where column_name = 'test'

The following are two options for running quick queries:

Option 1
In the “Run SQL query/queries…” field, enter your query and click the “Go” button.

Option 2
Another method of running a query is to upload a text file with the query in it. This is useful for MySQL dumps. To upload a text file, click the “Browse” button to select the file and then click the “Go” button. The information from the file is displayed and the query is carried out.

Searching for Records by Querying the Database

1. Click “Query by Example” to search for records. Specify your criteria using the fields below. When you make a change, click the “Update Query” button so that your options will take affect.

Field – Select the “table.field” you wish to query. Tables and fields are displayed with the name of the table, a period, and then the name of the field.
Sort – Select whether you wish the information to be sorted in “Descending” order, top to bottom or “Ascending” order, bottom to top.
Show – Check this box if you are selecting fields and wish to have them displayed.
Criteria – Enter your search criteria. For example, if you entered “>3” without the double quotes, your query would basically read “find all of the records where the field I selected above is greater than 3”.
And/Or – If you wish to enter another search criteria, select whether you wish the selected records to match both or either criteria then enter the string into the text box. You can select “And” or “Or” for two different search criteria within the same field and also when searching more than one field at a time.
Ins & Del Checkboxes – Check the “Ins” checkbox next to a criteria row or under a field column to add another option. Check the “Del” checkbox next to a criteria row or under a field column to remove an option.
Use Tables – Select any or all tables to query. Selected tables will affect the options for the “Field” dropdown.
Add/Delete Criteria Row – This is similar to the “Ins” and “Del” checkboxes for the criteria rows except you are able to add or remove more than one option at a time.
Add/Delete Field Columns –This is similar to the “Ins” and “Del” checkboxes for the field columns except you are able to add or remove more than one option at a time.

2. Click the “Update Query” button as you make changes and the query will be written out in the “SQL query on database…” field.
3. Click the “Submit Query” button to have the query take effect.

C. BACKING UP AND RESTORING DATABASES (Database Dumps)

A database dump converts a database or table into a text file with sql statements. This text file can be moved or saved and then restored back into database format.

Backing up a Database

1. To backup the structure and data in your database, select your options under “View dump (schema) of database” and then click the “Go” button.

– Only copies the structure of the database like fields, field types, etc.
Structure and data – Copies the structure of the database as well as all of records.
Add ‘drop table’ – This option will add a statement that basically says, “If there is already a database by this name when you turn this file into a database again, delete the old one before the new one is created.
Send – If “Send” is not checked, the dump will be displayed in the browser window. Checking “Send” will enable you to save the file to your hard drive.
Complete inserts – If “Complete inserts” is checked, the queries will use complete insert statements including the column names.

Restoring a Database
There are two easy ways to restore a database.

Option 1

1. Copy the contents of the dump.
2. Paste the dump into the “Run SQL query/queries…” field.
3. Click the “Go” button.

Option 2

1. Under “or Location of the textfile”, click the “Browse” button to find the database dump on your local computer. It will have the extension of “.sql”
2. After you have located the file, click the “Go” button.

Deleting Databases

1. To delete a database, click on “Drop database….” You will be asked to confirm the deletion.

Managing Tables and Fields

Tables and fields can be edited by going to the home page of the table.

1. On the left-hand side of the program window, click on the database in which the table resides.
2. Click on the table you wish to edit. 

Table comments

If you added comments when you created the table, you will see them at the top of the program window when you access the table. To edit comments for the table, enter a comment and click the “Go” button.

 Fields Management Links

When you access a table, you will see a list all of the fields and information about them. You are also provided with links to manage the fields.

Change – Allows you to edit a field’s properties.
Drop – Allows you to delete a field.
Primary – Allows you to make a field primary.
Index – Allows you to make a field an index field.
Unique – Allows you to make a field unique.

Beneath the list of fields is a list of the special fields like the unique and index fields.

Printing Table Fields

Clicking “Print view” will list all of the fields and their properties in a simple printable format.

Browsing – View/Edit/Delete/Insert Data
Click “Browse” to view/edit/delete/insert records in your table.

Navigating

The “Begin”, “Previous”, “Show” and “End” buttons allow you to browse through the records.

Displaying Records

The “rows” and “starting from” fields allow you to choose the number of records displayed and on what number they start. By default, 30 rows will be displayed and “starting from” will be the next 30 records. To select your own, enter a number in each field and click the “Show” button.

Adding Records

To add a record, also called a row, click “Insert new row” at the bottom of the program window.

Selecting Records

Click “Select” to search for records based on certain criteria.

Selecting Fields

All of the fields for the table are listed in the “Select fields (at least one):” field. Only the fields selected will be displayed in the final results.

Displaying Records

Enter a number in the “Display XX records per page” field to limit the number of records displayed. The default is 30.

Adding Search Conditions

Specific conditions for the search can be entered into the “Add search conditions…” field. Examples include: “field_name < 1”, “field_name = (1+2)*3”, etc. Do not include the double quotes. For more information, click on “Documentation”.

Querying by Example

You can also search for specific words or values in fields by entering the search criteria into the appropriate field under “Do a “query by example” (wildcard: “%”)” and then clicking the “Go” button.

Entering data into a field will only bring up results with the exact data in the exact field. To broaden the search, you can add a wildcard symbol before and/or after the word you are searching for.

For example, entering “Smith” will pull up any records with “Smith” in that specific field. But, entering “%Smith%” will pull up other records like “John Smith”, “Smithson”, etc.

Inserting Records

Click “Insert” to add another record to the database.

Adding Fields

To add a field, enter the number of fields you wish to add in the “Add new field” field and then select where you want the new field to go. Click the “Go” button. Fill out the properties for each field and click the “Save” button. For more information, click on “Documentation” or visit the “Creating Tables” section of this document.

Inserting Textfiles

Click “Insert textfiles into table” to upload a text file. At the next screen enter the following information:

Location of the textfile – Click the “Browse” button to select the file you wish to upload.
Replace table data with file – Check the “Replace” checkbox if you wish to have the contents of the file replace the contents in the database.
Fields terminated by – Enter the character(s) that separates the fields. The default is the commonly used “;”.
Fields enclosed by – Enter the character(s) which denotes fields and then check the “OPTIONALLY” checkbox if only the “char” and “varchar” fields of the text file are enclosed by the character.
Fields escaped by– Enter the character(s) that escapes a line. For example, in PHP starting a line with “//” will make the server skip that line.
Lines terminated by – Enter the character(s) which denote a line break.
Column names – If you want only certain columns to be uploaded, enter the names of those columns.

When you are finished selecting your options, click the “Submit” button. For more information, click “Documentation”.

Backing up and Restoring Tables (Database Dumps)

A database dump converts a database or table into a text file with sql statements. This text file can be moved or saved and then restored back into database format.

Backing up a Table

To backup the structure and data in a table, select your options under “View dump (schema) of database” and then click the “Go” button.


Structure only – Only copies the structure of the table like fields, field types, etc.
Structure and data – Copies the structure of the table as well as all of records.
CSV data – If this option is selected, the file will be a “.csv” file which is an alternative to “.sql”.
Add ‘drop table’ – This option will add a statement that basically says, “If there is already a table by this name when you turn this file into a database again, delete the old one before the new one is created.
Send – If “Send” is not checked, the dump will be displayed in the browser window. Checking “Send” will enable you to save the file to your hard drive.
Complete inserts – If “Complete inserts” is checked, the queries will use complete insert statements including the column names.
Terminated by – If “CSV data” is selected, the character in this field will be the character that separates each of the fields.

Restoring a Table

Tables can be restored in the same manner as a database. To restore a table, go to the home page of the database in which it is located and then view the “Restoring a Database” section of this document.

Renaming Tables

To rename a table, enter the new name in the “Rename table to:” field and click the “Go” button.

Copying Tables

To copy a table, enter a name for the new table in the “Copy table to:” field, select whether you want to copy the structure of the database only or the structure and the data, then click the “Go” button.