SiteGround Tutorials

---

PhpMyAdmin Tutorial

 

PhpMyAdmin Tutorial: Features

The main PhpMyAdmin features are as follows: 

• User-friendly web interface;
• Support for most MySQL functions like browse, drop, create, copy and alter databases, tables, views, fields and indexes, execute MySQL queries, manage stored procedures and functions;
• Import data from CSV and SQL files;
• Export data to various formats: CSV, SQL, XML, PDF, ISO/IEC 26300 - OpenDocument Text and Spreadsheet, Word, Excel, LATEX and others;
• Searching globally in a database or a subset of it;
• And much more. 

The full list of the PhpMyAdmin features can be found in the official web site.

 

PhpMyAdmin Tutorial: Installation

All the accounts on the SiteGround servers come with preinstalled PhpMyAdmin software. The application is accessible through cPanel->PhpMyAdmin.

However, you may need to install PhpMyAdmin manually. In this page of our PhpMyAdmin tutorial we will show the necessarily steps which should be taken in order to install PhpMyAdmin under your account.

Manual PhpMyAdmin Installation

Download the latest stable version of the PhpMyAdmin software.

Extract the archive file on your computer. Then upload the extracted files and folders through an FTP Client.

Place them at the exact location where you would like to access the software. If you want to open it directly through http://www.yourdomainname.com/PhpMyAdmin you should place the content of the extracted directory straight under the public_html/PhpMyAdmin folder of your account. Substitute www.yourdomainname.com with your actual domain name.

Add a database user with full privileges to the databases which you want to manage through your cPanel->MySQL Databases. You can follow our detailed tutorial on Creating MySQL database and user.

Load the PhpMyAdmin page using the corresponding URL (for example http://www.yourdomainname.com/PhpMyAdmin, where you should substitute www.yourdomainname.com with your actual domain name).

Enter the database username and the corresponding password. In this way you will be able to manage all the databases for which the database user has privileges. Click on the Go button to log in.

Now you can proceed with the databases management.

PhpMyAdmin Tutorial: Administration

In the following pages of our PhpMyAdmin Tutorial we will describe the functionality of the software, integrated in cPanel.

Once you enter your PhpMyAdmin application, you will see different areas.

 

In the upper part you will find the server hostname. The databases which you will manage are stored on the same server as the software and the hostname is: localhost.

Under it there is information regarding the MySQL server, the MySQL client and the PhpMyAdmin version.

Next, you will see the MySQL charset and you will be able to define the MySQL connection collation.

In the right column you can change the default language, alter the style, customize the theme color and the font size. Also there you will notice links to PhpMyAdmin resources.

Please note that you can not create a database directly through cPanel->PhpMyAdmin. However, you can easily create a new database from your cPanel->MySQL Databases. Navigate to the Create New Database box. Enter the database name in the New Database text field and click on the Create Database button. The database name will start with the cPanel user name. For example if your cPanel user name is user and you want to have a database named test, the actual database name will be user_test. You can check our MySQL tutorial for more details.

In the left panel you will see the list of the databases for which your cPanel user has privileges.

The main PhpMyAdmin areas are as follows:

Databases

In the Databases tab you will find a list with all the databases which can be managed through the cPanel user.

Once you click on a chosen database, you can start its management.

If you enable the statistics for the databases, you will see a table with the used collations, the number of the tables and the rows, the size of the data and the indexes, the total size and the overhead.

Please note that the enabling of the statistics will increase the load caused by your account on the server. Thus it is not recommended when you have many large databases. 

Status

You can open the Status tab by clicking on the Show MySQL runtime information link. There you will find detailed information regarding the MySQL server since the last restart.

You will see the traffic handled by the MySQL server, the maximum number of simultaneous connections, the total number of connections, the failed and the aborted attempts, the total number of queries sent to the server and more related details.

Variables

The Variables tab will be opened after clicking on the Show MySQL system variables link.

You will see a list with the MySQL server system variables and their values.

Processes

By clicking on the Processes link you will see all the processes running by your cPanel user.

Charsets

The Character Sets and Collations link leads to the Charset area. There you will find all the charsets and collations supported by the MySQL server.

Engines

The Storage Engines link opens a list with all the engines supported by the MySQL server. The default one is MyISAM. Another popular storage engine, used by many databases is InnoDB. More about the MySQL storage engines can be found in the official documentation.

Export

In the Export section you can export your database tables content in different formats (CSV, SQL, PDF, Microsoft Excel, Microsoft Word, XML, and many more). You can select all the database tables or just pick some of them.

You can add custom comments in the header of the exported content. You can decide whether to export just the database structure, the data or both of them. You can export the database tables in a file and compress it or you can visualize the queries directly on the screen.

Import

In the Import section you can import your database tables from a file, saved on your local computer.

You should browse for the file and pick its character set from the drop-down menu.

If the file is too big, the MySQL server timeout can be reached. In such a case you can interrupt the import action. Then you can continue with the data import defining the number of the queries to be skipped from the file beginning. In this way you will skip the imported queries and continue from the point of the interruption.

Additionally you can pick the SQL server mode of the imported file. You can find more details in the Server SQL Modes documentation.

PhpMyAdmin Tutorial: Database Management

The main functionality of the PhpMyAdmin tool is to manage your databases.

Click on the Databases link. Pick the preferred database which you want to manage and click on its name.

 

In the new screen you will see a list with the database tables, the allowed actions with them, the number of the records, the storage engine, the collation, the tables' sizes and the overhead.

The possible actions which you can perform to a chosen table are:

Browse

Only the tables with existing records can be browsed. Once you click on the Browse icon a new window with the records list will be opened.

By clicking on the Pen icon you can edit the chosen record.

You will see the record structure and you can alter the values of the records.

Structure

The next option is named Structure. In the Structure screen you will see the database's table structure.

You will see the fields' names, their types, collations, attributes, additional extra information,  the default values and whether the fields' values can be NULL. You can browse for distinct values by clicking on the corresponding action icon. Also, you can edit a field's structure or delete a field. You can define different indexes: Primary, Unique, Index and Fulltext. More about the indexes can be found in the MySQL Indexes documentation.

In the Indexes area you will find the indexes assigned for the table and the fields for which they are set. You can edit and delete them.

Additionally, in the same screen you can check the Space Usage and the Row Statistics.

Search

Through the Search action you can generate a search query for the chosen table.

 

You can either write the WHERE clause or you can use the "query by example" functionality. You should click on the Go button to execute the query.

For example, if you want to visualize all the records with a field value that starts with a you should select the fields which you want to show. Pick the LIKE operator from the drop-down menu and enter in the corresponding field value a% (% stands for a wildcard string). Click on the Go button to see the result.

Insert

Using the Insert action you can insert records in your database table.

Once you fill in the corresponding values click on the Go button and the new record will be inserted.

Empty

The Empty action allows you to empty your database table, removing the data and keeping the empty table.

Drop

Through the Drop action you can delete the whole table and all the records stored in it.

 

PhpMyAdmin Tutorial: Create Database

In this part of our PhpMyAdmin tutorial we will describe the steps which must be followed in order to create a new database, add a table with records, create a database backup and perform a database restore procedure.

We will start with the database creation.

How to Create a MySQL Database?

Please note that you can not create a database directly through cPanel->PhpMyAdmin due to the lack of user privileges. However, you can easily create a new database from your cPanel->MySQL Databases. Navigate to the Create New Database box. Enter the database name in the New Database text field and click on the Create Database button.

The database name will be preceded by the cPanel username. For example, if your cPanel user name is user and you want to have a database named test, the actual database name will be user_test. You will get a confirmation message.

How to Add MySQL Database Tables?

Navigate to your cPanel->PhpMyAdmin tool and open the newly create database. It is empty and there are no tables.

Enter the table name and the number of fields. Click on the Go button to create the table.

On the next screen you should enter the fields' names and the corresponding properties. The properties are:

Type

Here you should pick the type of the data, which will be stored in the corresponding field. More details about the possible choices can be found in the official MySQL Data Types documentation.

Length/Values

Here you should enter the length of the field.  If the field type is "enum" or "set", enter the values using the following format: 'a','b','c'...

Collation

Pick the data collation for each of the fields.

Attributes

The possible attributes' choices are:

BINARY - the collation for the field will be binary, for example utf8_bin;

UNSIGNED -  the field numeric values will be positive or 0;

UNSIGNED ZEROFILL - the field numeric values will be positive or 0 and leading zeros will be added to a number;

ON UPDATE CURRENT_TIMESTAMP - the value for a data type field has the current timestamp as its default value, and is automatically updated;

Null

Here you define whether the field value can be NULL. More about the NULL value can be found in the corresponding MySQL documentation.

Default

This property allows you to set the default value for the field.

Extra

In the Extra property you can define whether the field value is auto-increment.

The radio buttons that come below define whether there is an Index defined for the particular field and specify the Index type.

Comments

Here you can add comments, which will be included in the database sql code.

At the end you can include Table comments and pick the MySQL Storage Engine and the Collation. Once you are ready, click on the Save button.

If you want to add more fields you should specify their number and click on the Go button instead of Save.

The database table will be created and you will see the corresponding MySQL query.

Now we will proceed with the populating of the table with data.

How to Add Content in a Database Table?

In order to add records in a database table click on the Insert tab.

Enter the data in the corresponding fields and click on the Go button to store it.

At the bottom of the page you will see a drop-down menu labelled Restart insertion with x rows . There you can pick the number of the rows that you can populate with data and insert at once. By default the value is 2.

The Ignore check box will allow you to ignore the data entered below it. It will not be added.

You can see the newly inserted record by clicking on the Browse tab.

You can edit or delete the record by clicking on the corresponding icons.

To insert more records, return to the Insert tab and repeat the procedure.

How to Backup a Database?

Once you are ready, you can create a backup of your database through the Export tab.

Select the tables which you want to be exported.

Leave the radio button selection to the SQL option.  The Structure and the Data check boxes should remain checked.

Select the Save as file check box and then click on the Go button.

In this way you will save the dump SQL file with your database structure and content on your local computer.

If you have a large database with a lot of records, the server timeout value can be reached. In such a case you can export the database in several batches. You can find more details in our MySQL Knowledge Base.

How to Restore a Database Backup?

You can restore your database backup from the Import tab. 

Click on the Browse button to select your database backup file from your local computer.

Pick the charset of the file from the corresponding drop-down menu.

If the file is too big, the MySQL server timeout can be reached. In such a case you can interrupt the import action. Then you can continue with the data import defining the number of the queries to be skipped from the file beginning. In this way you will skip the imported queries and continue from the point of the interruption.

Additionally you can pick the SQL compatibility mode of the imported file. You can find more details in the Server SQL Modes documentation.

PhpMyAdmin Tutorial: Repair and Optimize Database

Sometimes the database tables get crashed. Also, they should be optimized from time to time. In this part of our PhpMyAdmin tutorial we will show how to repair and optimize database tables through PhpMyAdmin.

How to Repair MySQL Database Tables?

Open the database which should be repaired.

Select the tables that need repair and pick the Repair table action from the drop-down menu located below the tables' list.

The tables will be repaired and you will get a confirmation screen.

How to Optimize MySQL Database Tables?

Open the database which you want to optimize.

Select the tables that need optimization.

From the drop-down menu pick the Optimize table option.

After the tables' optimization a confirmation message with a list of the optimized tables will be visualized on the screen.

 

PhpMyAdmin Tutorial: Run MySQL Query

In the following part of our PhpMyAdmin tutorial we will show how to construct and run a MySQL query.

Actually there are two options to run a MySQL query. It depends on your MySQL syntax level of knowledge.

The one for advanced users is through the SQL tab. There you should enter the entire SQL query code and click on the Go button to execute it.

The other option is to construct a MySQL query in the Query tab.

There you can define different search conditions, sort the results and query multiple tables.

You should select the tables used in the query from the Use Tables list.

The fields which will be included in the SELECT MySQL statement should be picked from the Field drop-down menus. The Show check box should be selected.

In the Criteria text field you should enter the criteria according to which the search will be completed.

Through the Sort drop-down menu you can visualize the result sorted in an ascending or a descending order.

The text window located below allows you do add extra search conditions.

Additionally, you can use the Ins and the Del check boxes to add or delete text rows for search conditions. The same can be performed through the Add/Delete Criteria Row drop-down menu. To add or delete columns use the corresponding Ins and Del check boxes or the Add/Delete Field Columns drop-down menu.

In the Modify section you can define the relations between the fields (whether they are connected through the AND or the OR logical operators).

You need to click on the Update Query button to complete the modifications.

To run the query click on the Submit Query button.

The query which we have included in our example is:

SELECT `AT_admins` . * FROM AT_admins WHERE (`AT_admins`.`login` LIKE "a%");

It shows all the records from the AT_admins table for which the login field starts with "a".

You can find more details about the MySQL syntax in the official MySQL Documentation.

PhpMyAdmin Tutorial: PhpMyAdmin Operations

There are different operations which you can perform on the whole database and on a separate table.

Database Operations

Select a database and navigate to the Operations tab.

There you will find all the possible actions which you can apply on a database.

The first option allows you to create a new table under the current database. Enter the table name and the number of the fields. Then click on the Go button.

The second option is called Rename database to. However, the action can not be performed directly through the PhpMyAdmin area due to lack of privileges for the user. If you want to rename the database, you should create a new MySQL database, export the database tables and import them in the new one. Then you should delete the old database through cPanel->MySQL Databases.

The same is the case with the Copy database to option. The difference with the above one is that you should not delete the source database.

The last option allows you to change the database collation. Pick the preferred one from the drop-down menu and confirm the modification by clicking on the Go button.

Database Tables Operations

In order to perform the corresponding operations on a database table, you should select one and click on the Operations tab.

The Alter table order by option allows you to change the listing order for the table's records. It can be ascending or descending. Pick the field on which to base the sorting. Click on the Go button to confirm your choice.

The Move table to option allows you to move the table with a new name under the current database or to move it under a different database.

In the Table options section you can rename the table, insert comments, change the storage engine and the collation. Additionally, you can use the pack_keys option. It takes effect only with MyISAM tables. It packs the keys. When the DEFAULT choice is selected, the storage engine packs only long CHAR, VARCHAR, BINARY and VARBINARY columns. When 1 is selected numbers columns are packed along with the strings. The 0 choice disables the packing of keys.

Also, there are 2 more check-boxes. When the checksum option is enabled the table checksum is calculated. The delay_key_write option makes the index updates faster because they are not flushed to disk until the table is closed.

Using the Copy table to option you can copy the table with a new name under the current database or it can be duplicated in another database.

Under the Table Maintenance section you will find different options which will help you to maintain your database table:

• Check table - checks the table and the views associated with it for errors and problems; 

• Analyze table - analyses and stores the key distribution for the table. Then the MySQL server uses the stored key distribution for JOIN operations and for decisions which index to be used in a table query;

• Repair table - repairs a possibly corrupted table;

• Optimize table - the action should be performed when you delete or modify many records from the table. It will reclaim the used space, defragment the data file removing the overhead and sort the indexes.

• Flush the table - clears and reloads the internal cache related to the table.