phpMyAdmin

phpMyAdmin is a web-based graphical user interface (GUI) tool that simplifies the management of MySQL databases. It’s commonly used for tasks like creating, modifying, or deleting databases, tables, and records. 

More About phpMyAdmin

Functionality: Includes features for executing SQL statements, managing users and permissions, and exporting/importing data.

User Interface: Offers an intuitive web-based interface for database management.

Integration: Commonly integrated with web hosting control panels for ease of access.

Popularity: Widely used due to its ease of use and comprehensive features.

Hosting providers often include phpMyAdmin as part of their cPanel hosting control panel, making it accessible to users for database administration tasks.

Using phpMyAdmin in cPanel

1. Log in to cPanel: Access your cPanel account using your hosting provider’s login credentials.
2. Locate phpMyAdmin: In cPanel, you’ll find phpMyAdmin under the “Databases” section. Click on the phpMyAdmin icon or link.
3. Select a Database: On the phpMyAdmin dashboard, you’ll see a list of databases on the left sidebar. Click on the database you want to manage.
4. Database Operations:
   • Create a Database: To create a new database, click the “New” button, provide a name for the database, and choose a collation (character set).
   • Tables: You can manage tables within a database, including creating new tables, modifying their structure, and deleting tables.
   • SQL Queries: phpMyAdmin allows you to run SQL queries directly. This is useful for executing custom commands or importing SQL files.
5. Import and Export Data:
   • Import: To import a database, click the “Import” tab, choose a file from your local device, and click “Go.” This is handy for restoring backups or importing data.
   • Export: To export a database, select the database, click the “Export” tab, choose the export format (e.g., SQL, CSV), and click “Go.” This is useful for creating backups.
6. Search and Edit Records:
   • You can search for specific records within tables and edit them using the phpMyAdmin interface.
7. User Privileges:
   • phpMyAdmin allows you to manage user privileges and assign specific permissions to users for different databases or tables.

Performing Various Database Operations in phpMyAdmin

Here are step-by-step instructions for performing various database operations in phpMyAdmin:

Manage Tables:

1. After creating a database or selecting an existing one, you’ll see the database’s tables listed.
2. To create a new table, click on the database name and then the “Structure” tab.
3. Click the “Create” button.
4. Define the table’s structure by specifying the number of columns, their names, data types, and other attributes.
5. Click the “Save” button to create the table.
6. To modify an existing table, click on the table name within the database and then select the “Structure” tab. From there, you can add, edit, or delete columns.
7. To delete a table, select the table in the database, go to the “Operations” tab, and click the “Drop the table (DROP)” option.

Run SQL Queries:

1. In phpMyAdmin, select the database you want to work with.
2. Click on the “SQL” tab.
3. Enter your SQL query in the text box.
4. Click the “Go” button to execute the query. You’ll see the results displayed below the query box.

Import Data:

1. Select the database where you want to import data.
2. Click on the “Import” tab.
3. Click the “Choose File” button to browse your local device for the SQL file you want to import.
4. Select the file and click the “Go” button. phpMyAdmin will execute the import process, and you’ll see a success message upon completion.

Export Data:

1. Choose the database you want to export.
2. Click on the “Export” tab.
3. You can choose between the “Quick” or “Custom” export methods. “Quick” is usually sufficient for most purposes.
4. Select the export format, such as SQL, CSV, or others.
5. Click the “Go” button, and phpMyAdmin will generate the export file, which you can download to your local device.

Search and Edit Records:

1. In phpMyAdmin, select the database and table where you want to search or edit records.
2. Click on the “Browse” tab.
3. You’ll see a list of records in the table. You can use the search feature to find specific records based on criteria you define.
4. To edit a record, click the “Edit” link next to the record you want to modify. Make the necessary changes and click the “Go” button to save them.

These steps should help you perform various database operations in phpMyAdmin within your cPanel hosting environment.

Most Common SQL Commands in phpMyAdmin

In phpMyAdmin, you can use various SQL commands to manage and manipulate databases and tables. Here are some of the most common SQL commands used in phpMyAdmin:

1. SELECT: Used to retrieve data from one or more tables. It allows you to specify which columns to retrieve and apply filters to the data.
   SELECT * FROM employees WHERE department = 'Sales';

2. INSERT INTO: Adds new records into a table.Example:
   INSERT INTO products (product_name, price) VALUES ('Widget', 19.99);

3. UPDATE: Modifies existing records in a table.Example:
   UPDATE customers SET email = '[email protected]' WHERE customer_id = 123;

4. DELETE: Removes records from a table.Example:
   DELETE FROM orders WHERE order_id = 456;

5. CREATE DATABASE: Creates a new database.Example:
   CREATE DATABASE mydb;

6. CREATE TABLE: Defines the structure of a new table, specifying column names, data types, and constraints.Example:

   CREATE TABLE employees (
   employee_id INT PRIMARY KEY,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   hire_date DATE
   );

7. ALTER TABLE: Modifies an existing table, such as adding or deleting columns, changing data types, or adding constraints.Example:
   ALTER TABLE customers ADD COLUMN phone_number VARCHAR(15);

8. DROP TABLE: Deletes an existing table along with all its data.Example:
   DROP TABLE products;

9. CREATE USER and GRANT: Used to create database users and assign privileges to them.Example:

   CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
   GRANT SELECT, INSERT ON mydb.* TO 'newuser'@'localhost';

10. SHOW DATABASES: Lists all available databases.Example:
    SHOW DATABASES;

11. SHOW TABLES: Lists all tables in the current database.Example:
    SHOW TABLES;

12. DESCRIBE or DESC: Provides information about the structure of a table, including column names, data types, and indexes.Example:
    DESC employees;


These are some of the fundamental SQL commands that you can use within phpMyAdmin to manage your MySQL or MariaDB databases. Depending on your specific requirements, you may need to use more advanced SQL commands and syntax.

How to List All Table Sizes From All Databases Using SQL

To list all table sizes from all databases using SQL commands in phpMyAdmin, you can use the following query. Please note that this may require sufficient privileges, and you should be cautious when running such queries, especially on a production server:

SELECT table_schema AS "Database",
table_name AS "Table",
round(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.tables
ORDER BY (data_length + index_length) DESC;

Here’s how to execute this query in phpMyAdmin:

1. Log in to phpMyAdmin.
2. In the left sidebar, select the database where you want to run the query. You can choose any database because the query will fetch information from all databases.
3. Click on the “SQL” tab at the top.
4. Paste the SQL query mentioned above into the SQL query box.
5. Click the “Go” button to execute the query.

phpMyAdmin will display a result set showing the database name, table name, and the size of each table in megabytes (MB). The tables will be listed in descending order of size, with the largest tables at the top.

Conclusion

phpMyAdmin is an invaluable tool for anyone working with MySQL databases through cPanel. It streamlines database management tasks, offering a user-friendly interface and features for efficient data management, making it an essential component of website and application development and maintenance.