Skip to main content

Course Progress

Loading...

Using phpMyAdmin

Duration: 60 minutes
Module 3: Session 1

Learning Objectives

  • Navigate the phpMyAdmin interface efficiently
  • Create and manage databases using the GUI
  • Design tables visually with phpMyAdmin
  • Import and export databases
  • Execute SQL queries through phpMyAdmin
  • Manage users and privileges

What is phpMyAdmin?

phpMyAdmin is a free, open-source web application that provides a user-friendly interface for managing MySQL databases. Think of it as your visual control panel for MySQL! 🎮

💡
Fun Fact
phpMyAdmin is written in PHP and has been around since 1998! It's translated into 72 languages and is one of the most popular MySQL administration tools.
graph TB Browser[Web Browser] --> PMA[phpMyAdmin] PMA --> MySQL[MySQL Server] MySQL --> DB1[(Database 1)] MySQL --> DB2[(Database 2)] MySQL --> DB3[(WordPress DB)] PMA --> F1[Create/Drop Databases] PMA --> F2[Design Tables] PMA --> F3[Run SQL Queries] PMA --> F4[Import/Export] PMA --> F5[User Management] style PMA fill:#f89c0e,color:white style MySQL fill:#00758f,color:white

Accessing phpMyAdmin

Quick Access Methods

  • XAMPP:Click "Admin" button next to MySQL in Control Panel
  • MAMP:Click "phpMyAdmin" tab in MAMP window or use WebStart page
  • Direct URL:Bookmark the phpMyAdmin URL for quick access

phpMyAdmin Interface Tour

Key Interface Elements

  • 📊Left Sidebar:Database and table navigation
  • 📑Top Tabs:Different views and operations
  • 📝Main Area:Content and data display
  • 🔧Action Buttons:Quick operations on tables

Creating a Database

Visual Database Creation

✅
Collation Tip
Always useutf8mb4_unicode_cifor WordPress databases. It supports emojis and all international characters!

Creating Tables with phpMyAdmin

Visual Table Designer

phpMyAdmin provides a visual interface for creating tables without writing SQL:

  1. Select your database from the left sidebar
  2. Click "Create table" or the "Structure" tab
  3. Enter table name and number of columns
  4. Define each column:
    • Name:Column name (e.g., user_id)
    • Type:Data type (INT, VARCHAR, TEXT, etc.)
    • Length:Maximum size
    • Default:Default value
    • Null:Allow NULL values?
    • Index:PRIMARY, UNIQUE, INDEX
    • A_I:Auto Increment
  5. Click "Save" to create the table
-- phpMyAdmin generates this SQL for you:
CREATE TABLE `users` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL,
    `email` VARCHAR(100) NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `username` (`username`),
    UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Running SQL Queries

graph LR A[SQL Tab] --> B[Write Query] B --> C[Execute] C --> D{Result} D -->|Success| E[View Results] D -->|Error| F[Error Message] style A fill:#f89c0e,color:white style C fill:#10b981,color:white

SQL Tab Features

  • 🎨Syntax Highlighting:Color-coded SQL for easy reading
  • 📝Auto-completion:Suggests table and column names
  • 📚Query History:Access previous queries
  • 💾Save Queries:Bookmark frequently used queries
  • 📊Explain Query:See how MySQL executes your query
-- Example queries to try:
-- Show all users
SELECT * FROM users;

-- Find users registered today
SELECT * FROM users 
WHERE DATE(created_at) = CURDATE();

-- Count posts per user
SELECT u.username, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id
ORDER BY post_count DESC;

Import and Export

⚠️
Import Size Limits
Default upload limit is 2MB. For larger files:
  1. Increaseupload_max_filesizein php.ini
  2. Increasepost_max_sizein php.ini
  3. Or use command line:mysql -u root -p database_name < backup.sql

User Management

Creating Database Users

Never use root for applications! Create specific users with limited privileges:

  1. Click "User accounts" tab
  2. Click "Add user account"
  3. Set username and password
  4. Choose host (localhost for local development)
  5. Grant specific privileges
-- phpMyAdmin creates users like this:
CREATE USER 'wordpress_user'@'localhost' 
IDENTIFIED BY 'strong_password';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER 
ON wordpress_db.* 
TO 'wordpress_user'@'localhost';

FLUSH PRIVILEGES;

Practice Exercise

💻
Hands-On Practice
Complete these tasks in phpMyAdmin:
  1. Access phpMyAdmin through XAMPP/MAMP
  2. Create a database calledpractice_db
  3. Create a table calledproductswith these columns:
    • id (INT, AUTO_INCREMENT, PRIMARY KEY)
    • name (VARCHAR 100)
    • price (DECIMAL 10,2)
    • stock (INT)
    • created_at (TIMESTAMP, DEFAULT CURRENT_TIMESTAMP)
  4. Insert 3 sample products using the Insert tab
  5. Run a SELECT query to view all products
  6. Export the database as SQL

Quick Tasks

Before moving to the next lesson:

  • Explore all tabs in phpMyAdmin
  • Try the visual query builder (Query tab)
  • Check the Variables tab to see MySQL settings
  • Look at the Status tab for server information

Additional Resources