What is PhpMyAdmin?

PhpMyAdmin is one of the most popular applications for MySQL databases management. It is a free tool written in PHP. Through this software you can create, alter, drop, delete, import and export MySQL database tables. You can run MySQL queries, optimize, repair and check tables, change collation and execute other database management commands.

All the SiteGround clients can manage their MySQL databases through the preinstalled PhpMyAdmin software which is integrated in cPanel. Check our PhpMyAdmin hosting for more details.

In this tutorial we will show how to use PhpMyAdmin and manage your MySQL databases through it.

 

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.