How to Manage Databases with PhpMyAdmin

Overview

A direct access to phpMyAdmin allows you to manage, import, export and modify your databases as such.

Indeed, phpMyAdmin is a free software written in PHP designed to manage MySQL administration on the web. It supports a wide range of operations, most of which – database management, tables, columns, relationships, indexes, users, permissions, etc. – Most of them – database management, tables, columns, relationships, indexes, users, permissions, etc. – can be done through the user interface, while you still have the possibility to execute any SQL command directly.

In the following, we will limit ourselves to information related to the use for beginners.

Note :The Glossary contains explanations on multiple topics and can be consulted to clarify certain terms.

Prerequisite

Access to https://mg.n0c.com/en/.

PHPMyAdmin

As soon as the database in SQL has been created (how), you can design it in PHPMyAdmin. You can also import it if it already exists on your machine.

Note

More experienced users can refer to the PHPMyAdmin documentation by clicking on this link: https://docs.phpmyadmin.net/en/latest/.

Access through N0C

1. In the sidebar menu, select Databases -> PhpMyAdmin. This opens the phpMyAdmin software:

2. The Navigation Panel (left side) is primarily used to choose a database or a table, or to come back to the phpMyAdmin Homepage.
3. The purpose of the Main Panel, also called Central Page (right side), is to interact with the system.

How to Create a Table

1. Click on the name of the database:

2. Enter the Name and the Number of columns of the table.
3. Click on the Go button.

4. Enter following information for each field of the table :
   • a. Name: defines the name of the table;
   • b. Type: the type of data that will be recorded in the field. Most usual are INT (whole number), VARCHAR (short text), TEXT (long text) and DATE (date, month, year);
   • c. Length/Values: maximum size of the field, useful for VARCHAR in particular, to limit the number of authorized characters;
   • d. Index: activates the indexation of the field. This word means that the field will be adapted to queries. The PRIMARY index is used on fields of id type;
   • e. A_I (AUTO_INCREMENT): allows the field to increment alone at each entry. It is often used for id type fields.   

5. Click on Save, so the table becomes visible in the left Navigation panel.

6. Continue to create as many tables as necessary by repeating the previous steps.

How to Modify the Fields in a Table

Once created, a table is not fixed: you have the option to rename, delete and add fields,  etc.

1. After having chosen the table to modify in the left Navigation panel, click on the Structure tab.
2. Click on relevant action, Change changes the field of table, while Drop removes a field.
3. More gives access to icons allowing additional actions.

How to Insert Values in a Table

1. Select a table in the left Navigation panel then click on the Insert tab.
2. Fill in the Value fields.

How to Write a Query to be Executed in a Table

1. Select a table in the left Navigation panel then click on the SQL tab.
2. Write the query in the designated field.
3. Then click on the Go button; the result of the query is then displayed.

How to Browse the Records in a Table

1. Select a table in the left Navigation panel then click on the Browse tab.
2. Entries are displayed.

How to Search Records in a Table

Before beginning, it is important to note that the operator LIKE means « exactly the same», while LIKE %…% means « includes ».

To search a value chosen in a table selected in the left Navigation panel.

1. Click on the Search tab.
2. Click on the Table Search tab.
3. Using the Operator and Value fields, define search criteria.
4. Click on the Go button.
5. You can edit, copy, delete or export the entries.
6. Other actions on the results of the query are possible.

To make a search by value on two different columns:

1. Click on the Zoom search tab.
2. Enter the requested information.
3. Click on the Go button.

To search and replace a record:

1. Click on the Find and replace tab.
2. Enter the information in the Find field and the new data in Replace with field; then,  specify the Column.
3. Click on the Go button.
4. Click on Replace to save the modified string.

Note

The % character (joker) replaces a string (it can be null).

How to Export a Table

1. Select the desired table.
2. Click on the Export tab.
3. Define the format of the data you wish to export (SQL, CSV, PDF or other format).
4. Click on the Go button.

How to Import Data from a Similar Table

Sometimes, it is more practical to use a file containing SQL queries in order to import many queries instead of writing each query individually.

1. Select the desired table.
2. Click on the Import tab.
3. Define the import settings.
4. Click on the Go button.

How to Perform Operations on a Table

More advanced users can modify tables with the Alter table functions. 

You may need to:

• Rename table to: change the table name.
• Move table to (database.table):  Move the table to another database.
• Copy table to (database.table): copy the table in the same database or another one (beware: in this case, it must have another name).
• Optimize table: speed up queries.
• Empty the table (TRUNCATE): empty all the data in the table. All records disappear and only the structure of the table stays (that is, the fields). Beware: this operation cannot be undone.
• Delete the table (DROP):  removes one or more tables (structure and data). You must have the DROP privilege for each table. Once again, be careful before deleting a table, as nothing can be recovered afterwards, unless a save has been done previously with the export tool.

1. Select the desired table.
2. Click on the Operations tab.

How to Manage the Triggers on a Table

1. Select the desired table.
2. Click on the Triggers tab.

3. Click on Add trigger.
4. Define the procedure for each trigger and click on the Go button.

How to Empty a Saturated Table

It often happens that a user saturates a table while making a blog. It is then mandatory to empty the table.

Note

Before doing so, check the content of the table to avoid losing important elements of your website, such as articles.

1. Select the table causing problems.
2. Check Check all.
3. Click on Delete to empty the table.

How to Export a Database

The PHPMyAdmin function has two purposes:

• exporting databases on the Internet;
• make backup copies of databases in several export file formats (dump).

If the SQL format is chosen, the file that you will obtain upon exporting with phpMyAdmin specifies to MySQL how to recreate your databases with SQL queries.

Note

It is strongly recommended to make a backup of your database before exporting anything in PHPmyAdmin.

How to Export all Your Databases

1. Go to the Homepage.
2. Click on the Export tab.
3. Define the Export method by clicking on the Quick – display only the minimal options radio button.
4. Define the export file Format (SQL, CSV, PDF or any other format).
5. Click on Go button.

How to Export Selected Databases

1. Go to the Homepage.
2. Click on the Export tab.
3. Define the Export method by clicking on the Custom – display all possible options radio button.
4. Define the export file Format (SQL, CSV, PDF or any other format). 
5. Select your Databases to export.
6. The options depend on the chosen format (SQL only is used for our explanations in this article). In Output, choose Rename exported databases/tables/columns if needed.
7. Enter relevant information.
8. Click the Save & close button.
9. Continue to fill in the Output options (you can choose either Save output to a file or View output as text).
10. Define the Format-specific options.
11. If you choose to export the structure only, click on Structure.
12. Define the Object creation options.
13. Click on the Go button.
14. If you choose to export the data only, click Data.
15. Define Data creation options and click on the Go button.
16. If you choose to export structure and data, click Structure and data.
17. Define Object creation options and Data creation options, and click Go.

How to Import a Database With phpMyAdmin

It is important to know good practices for importing databases.

You must have an empty database in your N0C account in order to import one. In addition, you must ensure that if your file is compressed, its name ends with .[format].[compression].

Example:. sql.zip. If the .sql you attempt to import is too large for phpMyAdmin, you have to import it with SSH (in this case, please see section How to import a database in command line).

1. Refer to the section Create an SQL database and follow the creation steps. 
2. Click on the name of the database that will receive import information. The page will refresh to display the related information.
3. Click on the Import tab.
4. Click Choose file. Your browser will then allow you to select the database file on your computer.
5. Define the Character set of the file.
6. Check Partial import options:
   • allowing the interruption of an import in case the script detects it is close to the PHP timeout limit might be a good way to import large files; however it can break transactions;
   • the field Skip this number of queries (for SQL) starting from the first one allows you to ignore some queries at the beginning.
7. Check Enable foreign key checks under Other options in order to insert tables that have foreign keys.
8. Choose the Format (SQL by default).
9. Choose Format specific options. They allow you to choose a compatibility mode. This is very useful in particular to import various types of SQL files from many databases (ANSI, ORACLE, etc.).
10. Click on the Go button.

As soon as the file import is finished, the data and the structure it contains will be available for use in the database.

How to Import a Database in Command Line

Importing a database via SSH can be done in Windows with puTTY software. It can also be done directly with the terminal in some environments. We will cover the use of Windows in this article.

For our example, let’s suppose the user having the username « fbkfdayw » desires to import a database called « atomes.sql » in an empty database called « BD_Atomes ».

Note

This procedure is for advanced users. Always save your work beforehand.

Recover your Password

The first step is to recover your username and your password from your hosting.

Prepare hosting

1. Refer to the section Create an SQL database and follow the steps to create your empty « BD_Atomes » database where the import will be done.

2. Create the exportat file (dump) on your machine — « atomes.sql » in our example (how).
3. Send the export file  to the N0C server. There are 2 possibilities, the first one is recommended:
   • connect to your N0C hosting in FTP (how);
   • connect to N0C and perform an Upload on File Manager (how).

Connection in SSH

You are now ready to remotely connect from your machine.

1. Load PuTTY from its official website. Click on the installation program (here, putty-64bit-0.74-installer.msi).

2. Then, run PuTTY. Make sure that the Connection Type is set to SSH in the PuTTY Configuration window.
3. Enter your server IP address or Host Name (for example, node2-ca.n0c.com). If the Host Name is not convenient, enter localhost instead.
4. Specify the port. This varies depending on the hosting type you have: unless otherwise specified, use 5022 for the Port fields.
5. To save the information kept in memory – so it is not necessary to redo this at each connection – give a name to the session in the field named Saved Sessions and click on the Save button.
6. Launch the SSH session by clicking on the Open button. On the first time, PuTTY should ask for a confirmation.
7. As the server displays login as:, identify yourself by entering  your username and press the Enter key on your keyboard.
8. Enter your password (since you have copied it at step 3 when you recovered your password, you can right click your mouse).

Import in Command Line

You are now ready to import your dump file in the empty database.

1. Launch the import command:

mysql -u user -ppass database < file_dump

Where :

• user: the user you wish to connect with.
• pass: the password of this user.
• database: the name of the database where data will be imported.
• file_dump: name of the dump file containing SQL instructions to import.

In our example, we will write :

mysql -u fbkfdayw -ppass fbkfdayw_BD_Atomes < atomes.sql

2. You can then access phpMyAdmin and confirm that your database has been imported.

Some Useful Putty Commands

Settings

The Settings tab allows you to configure PhpMyAdmin as you desire. Preferences are saved in current sessions only.

A permanent storage requires the installation of phpMyAdmin configuration storage.

You can customize the information displayed on various pages:

Server Variables and Parameters

The Variables tab contains all MySQL variables and their values.

The Edit action allows you to change the value of any variable. Take note that you cannot necessarily change the values of all variables, this being permitted only for the root user on the server.

Character Sets and Collations

The Charsets tab gives access to the character sets and list of each collation supported by the MySQL server.

Storage Engines

The Engines tab opens a page wth all storage engines that are supported by the MySQL server.

InnoDB is selected by default. Users can refer to the documentation by clicking here to get more information about storage engines.

Plugins

The Plugins tab opens a page showing all installed MySQL plugins, with relevant information such as description and version.

References

https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver15.