How to manage a database with phpMyAdmin

Introduction

PhpMyAdmin is a intuitive graphical interface for managing your MySQL database with no necessary previous knowledge on the PHP commands of the database or the SQL instructions for data manipulation.

It is advisable to continue learning some of the fundamentals of the PHP programming language and MySQL DB. In this guide we will focus on a simple analysis of the main features offered by this powerful interface.

Pre-Requisites

Being a tool widely used in webhosting, the requirements to use the interface are limited to:

• Owning a web server with installed:
  • Php module
  • MySQL
  • PhpMyAdmin

Considering how useful it is, PhpMyAdmin is included in some web hosting packages or, in case of a local server, it is pre-installed with the LAMP or XAMPP package.

For convenience, the tool access and use on a local host server with the LAMP package installed is shown.

Interface

When using LAMP or XAMPP, open the PhpMyAdmin interface simply by running MYSQL and Apache on the local server, and connecting to the page:

http://nomedominio/phpadmin/index.php

The package can also be installed on non-local web servers, by simply entering from the prompt as follows:

$ sudo apt install phpmyadmin

You will be required by the terminal to choose the server where to install the package. Once confirmed, connect to the right page by following the syntax above.

A basic interface that allows you to carry out the main operations of the database makes the environment very simple to understand.

If you are familiar with the SQL language, use a terminal where to directly enter the instructions for creating and managing databases.

Creating a new database

To separate the persistent data of the different projects, use different databases. By doing so, a dedicated db for any of your new applications can be obtained.

Creating a DB is very simple: first, identify the "Database" section and click on the related icon to create a new database.

Once done, enter the required information to name your new DB and set some of its characteristics before proceeding with the actual creation.

Alternatively, when entering instructions from the terminal, use the following instruction:

CREATE DATABASE nomedatabase;

Deleting a database

In case you no longer need a previously created database, use the feature ‘delete’ to remove it from PhpMyAdmin.

To delete a database, click on its name on the interface and select ‘delete’. If you you want to use the SQL terminal, send this statement:

DROP DATABASE nomedatabase;

To be performed, the deletion has to be commissioned by a user with root permissions.

Creating a table

To organize persistent data within your database, store it in tables. Tables allow you to specify fields for the classification of the values ​​inserted.

To create a new table, identify the ‘new’ icon on PhpMyAdmin and create a new table for a specific database.

As shown in the picture, the interface will allow you to add new fields and assign them a name, an associated data type, a range of ​​allowed values, a specification (e.g. primary key, unique value, index) and other information.

Once done, click on "Save" to end the creation of your new table.

From the terminal, enter the following SQL statement:

CREATE TABLE nometabella (
 campo1 datatype,
 campo2 datatype,
 campo3 datatype,
....
);

Read the documentation on w3cschools for further specifications on this simple SQL instruction and add new details to your new tables.

Modifying or Deleting a table

To delete, empty or modify the contents of a table, click on the name of the table and identify the ‘Operations’ icon.

Several options related to your table will then appear, including those for emptying or deleting. When choosing the former, the content but not the structure of a table will be deleted, while, when choosing the latter, the whole table will be deleted.

Use the SQL statements for emptying or deleting, using, respectively:

• TRUNCATE TABLE nometabella;

• DROP TABLE nometabella;

To modify the structure of an existing table, click on the ‘Structure’ icon and interact with the interface for modifying the fields and their specifications.

Conclusions

By the end of this tutorial you will have been able to carry out the main operations related to the management of your database and the tables included. Additional backup operations for your tables will be available in the dedicated guide!