Basics of phpMyAdmin

As we know that, any simple website or application needs interaction with data or data management, so it uses databases phpMyAdmin has become one of the popular, free, open-source software platform for administration of MySQL and MariaDB data over the web world. This tool is primarily written in PHP for web hosting and related services.

This GUI tool manages a lot of data related operations such as creation, modification, deletion of data, change structure of table which are in the Databases. It can manage multiple databases at a time. It also performs other actions like browsing data, executing SQL queries along with managing indexes, users, and permissions. This also provides functionality like taking backups, doing search operations, import and export of data in different formats like SQL, CSV, XML, Excel sheets.

phpMyAdmin is the most trusted and user-friendly database managers and mostly used for web-based applications or programs. In the following article, we will be learning about the importance of the phpMyAdmin tool in the web world.

phpMyAdmin | Pre-requisites: To install phpMyAdmin software, you need a server running platform like Windows or Linux supports operating systems.

• Web Browser: You need a web browser interface to run the tool.
• PHP scripting language: You need a server side language.
• Apache Web server: You need a web server to store phpMyAdmin files.
• MySQL or MariaDB Database: You need a database to manage application data.

Note: XAMPP package installation is the easiest way to get phpMyAdmin tool.

phpMyAdmin | Features: Let us now look into some of the features of phpMyAdmin tool.

• Manages all user level permissions.
• Executes SQL queries, batch queries, triggers, events, stored procedures, functions to display relevant data results.
• It performs all data related operations like create, read, edit, delete, browse, change structure, schema and search.
• It also provide ways to import, export and load text file data.
• It export data in various formats like CSV, XML, PDF, Word document, Latex, Spreadsheet, Excel file and many more.
• It supports complex queries and bookmarking of SQL queries.
• It can also optimize, repair, change tables and views.
• It also supports InnoDB, foreign keys and MySQLi.
• It provides user friendly graphical interface to access website related data.
• It can provide PDF graphics layout of database.
• It is very flexible with different operating system.
• It can handle complex queries using Query by example.
• It provides multiple-server administration.
• It can control several servers at the same time.

phpMyAdmin | Advantages: Let us now look into some of the advantages of phpMyAdmin tool in brief.

• Its very simple to set up the tool.
• The GUI is very convenient and easy to understand for developers and users.
• As its a web-based tool, it can be accessed it from any computer system.
• Its a intuitive web based interface which runs on any server.
• It is advantageous over console as many tasks like cut, copy and run queries becomes very easy due to its GUI.
• It provides automatic MySQL backup facility.
• It don’t require a network connectivity as its installed on the computer already having MySQL server.
• It provides maximum level of data security and efficiency for its client.
• It is supported by multi-language community.

phpMyAdmin | Configuration:

• Download the latest version of phpMyAdmin.
  
• Once the files are extracted during the download process, they are stored in location C:\xampp\phpMyAdmin
• The configuration settings can be changed in config.inc.php file which is present in the above location. Other system settings can also be changed in the file.
• Administrators have full rights for creating new users, remove users and modify privileges for old users.
• For a specific database, you can define the permissions of any user and manage accounts of user groups.

Note: The configuration settings are used to customize the web interface.

phpMyAdmin | Logging in: Whenever a user logs in to phpMyAdmin server with the username and password, these values are passed directly to MySQL server. For logging in, you need the valid credentials of MySQL users.

• Database server
• Database username
• Database password

Once all the credentials are entered correctly, you can see the phpMyAdmin home page in your browser. When you are in phpMyAdmin page, the center section is the default General Settings and Appearance Settings and in the right side, you can see Database server and Web server information.

You can adjust language, themes, font size under the Appearance Settings section. You can also change Database server information as needed.

All the list of databases can be seen on the left-hand side of the screen. You can select the database, you want to work with out of the list. Once it is expanded, all the tables in the selected database can be viewed.

phpMyAdmin tool allows you to check the MySQL server status by accessing the Status tab. For checking status, remember NOT to select any specific database.

Here, you can find information regarding all the traffic your server has Received and Sent while the MySQL server been running. It also shows the maximum concurrent connections, and Failed attempts. If you are using a shared hosting plan, chances are your numbers won’t make much sense. All these information helps in determining the amount of bandwidth consumed as you are not the only one to use the server.

phpMyAdmin | Access of Database information: You can see information_schema database in the left side of the screen. It is the database which stores all the information of all other databases like name of databaseS, name of tables, column data type, access privileges and so on. It is a built-in database created with the purpose of giving information about the whole database system. In information_schema tables are automatically populated by the MySQL DB server.

phpMyAdmin | Create database: A user can create a database in phpMyAdmin server. Just click on the Databases tab, you have a Create database input option. Give a relevant name for the database in the entry box and click the Create button. The phpMyAdmin will create tables which will be added under the newly created database.

You can add as many numbers of tables as required in one particular selected database by using the section Create table and then the select number of columns needed for any table.

The phpMyAdmin will give an option to upload an SQL file to your server. Click on the Import tab of the top screen and it will allow you to choose a file from any location. This helps in creating database or tables as written in the imported file.

Note: In the same way, you can also export any table or database as a file by using Export tab.

phpMyAdmin | Execute a SQL query: Let us look into the steps to execute a SQL query on a selected database.

• In the left side of the screen, select the database to work with. The Structure of the database can be seen after the selection.
• Click SQL tab from top bar to open editor in which you can write the queries.
• Write the query into the SQL editor.
• You can also write multiple queries, with semicolon (;) separating different queries.
• Click Go button to execute the query. If the query is executed successfully, you will get a success message.

Another way to run a SQL query in the database is by using Query tab. Using this you can give many search conditions, query more than one table and also sort the results. You can even easily pick all the columns needed for MySQL query by using Column drop down menus and Show checkbox.

For sorting the result set in a query in an ascending or descending order, you can use the Sort dropdown. You can also enter the criteria for the search query. The Ins and Del check boxes or Add/Delete criteria rows options are provided to add or delete text rows for any search conditions. The logical operators And or the Or can also be used to define relations between the fields in the Modify section. After entering all the relevant conditions, you need to click on Update Query button for doing the SQL operation. Next step is to select all the tables from Use Tables section used in the query and then click on Submit Query.

phpMyAdmin | Operations: In the phpMyAdmin tool, different operations can be performed on the database or on a separate table. Select the database for which you want to perform any operation and click the Operations tab. It is allowed to create a new table under the selected database by using Create table section. You can also rename the selected database by using Rename database to section. Similarly you also have Remove database to delete database and Copy database to sections which can be used as required for any SQL operation.

phpMyAdmin | Generate a search query: We can also generate a search query using phpMyAdmin GUI, rather than writing the search query manually for a selected table. This can be done by clicking the Search tab in the top menu of the screen.

phpMyAdmin | Insert query: We can also insert query using phpMyAdmin GUI, rather than manually writing it. Select the table for which insert query will be executed. Then click the Insert tab in the top menu of the screen. Once the relevant values are entered, the new record will be inserted in the table. The newly inserted record can be viewed using the Browse tab under the selected table name.