Techy AllTechy All
Learning Hub

How to Create MySQL Database in phpMyAdmin

phpMyAdmin is a web-based tool for managing MySQL databases. This tutorial shows you how to create databases, create tables, define columns, and insert data using both the graphical interface and SQL queries.

How to Create MySQL Database in phpMyAdmin

phpMyAdmin is a web-based database management tool that comes pre-installed with XAMPP. It provides a graphical interface to create, modify, and manage MySQL databases without writing SQL commands. This tutorial will guide you through creating your first database called "users", creating a table called "admin", defining columns, and inserting data using both the graphical interface and SQL queries.

After installing XAMPP, the next step is to create a database for your web applications. To understand this tutorial properly, it is helpful to have XAMPP installed and running with Apache and MySQL services started.

What you will learn in this tutorial: 
✓ Accessing phpMyAdmin from XAMPP Control Panel
✓ Creating a new database with proper collation
✓ Creating tables and defining columns
✓ Setting Primary Key and Auto Increment
✓ Inserting data using the graphical interface
✓ Creating tables and inserting data using SQL queries
✓ Understanding MySQL data types

What Is phpMyAdmin

phpMyAdmin is a free and open-source web-based tool for managing MySQL and MariaDB databases. It is included with XAMPP and accessible at http://localhost/phpmyadmin. It provides an intuitive graphical interface for database operations.

  • Graphical Interface: Create, modify, and delete databases without SQL
  • Table Management: Create tables, add columns, set indexes and keys
  • Data Management: Insert, edit, delete, and search data visually
  • SQL Execution: Run custom SQL queries directly
  • Import/Export: Backup and restore databases using SQL files

Step 1: Start Apache and MySQL

Before accessing phpMyAdmin, ensure both Apache and MySQL services are running in XAMPP Control Panel. 

1. Open XAMPP Control Panel
2. Click "Start" next to Apache (should turn green)
3. Click "Start" next to MySQL (should turn green)

Step 2: Access phpMyAdmin

Method 1: Open browser and go to http://localhost/phpmyadmin
Method 2: In XAMPP Control Panel, click "Admin" button next to MySQL

Step 3: Create a Database

1. In phpMyAdmin, click "New" in the left sidebar
2. Enter database name: users
3. Choose collation: utf8mb4_general_ci
4. Click "Create" button
5. Your "users" database appears in the left sidebar

What is Collation?

Collation determines how string comparison works for sorting and searching. `utf8mb4_general_ci` is recommended as it supports all Unicode characters including emojis, and `ci` means case-insensitive.

Step 4: Create a Table (GUI Method)

1. Click on "users" database in the left sidebar
2. In "Create table" section:
   - Table name: Enter "admin"
   - Number of columns: Enter 3
3. Click "Go" button

Step 5: Define Table Columns

Column Type Length Attributes
id INT (leave empty) Primary Key, A_I (Auto Increment)
name VARCHAR 250 (no special attributes)
age INT 15 (no special attributes) 
After defining all three columns, click "Save" button at the bottom.

Step 6: Insert Data (GUI Method)

1. Click on "admin" table in the left sidebar
2. Click "Insert" tab at the top
3. Enter values:
   - id: (leave empty - auto increment will fill it)
   - name: ADAM
   - age: 29
4. Click "Go" button
5. Your data has been successfully inserted

Method 2: Using SQL Queries (Faster)

Create Database with SQL

CREATE DATABASE users
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

Create Admin Table with SQL

CREATE TABLE admin (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(250) NOT NULL,
    age INT(15) NOT NULL,
    PRIMARY KEY (id)
);

Insert Data with SQL

INSERT INTO admin (name, age) VALUES ('ADAM', 29);

View Data with SQL

SELECT * FROM admin;

How to Run SQL Queries in phpMyAdmin

1. Click on your database name in the left sidebar
2. Click "SQL" tab at the top
3. Paste your SQL query in the text area
4. Click "Go" button

Understanding MySQL Data Types

Data Type Description Use Case
INT Whole numbers IDs, age, counts, quantities
VARCHAR(n) Variable text up to n characters Names, emails, titles, addresses
TEXT Long text Blog posts, descriptions, comments
DATE Date only (YYYY-MM-DD) Birth dates, event dates
DATETIME Date and time Created_at, updated_at timestamps
DECIMAL(m,d) Exact decimal numbers Prices, salaries, financial data

Primary Key and Auto Increment Explained

Concept Description
Primary Key Uniquely identifies each row. No duplicates allowed. Cannot be NULL.
Auto Increment (A_I) Automatically assigns a unique number to each new row. Starts at 1 and increases by 1.

Common phpMyAdmin Operations

Operation How to Perform
Browse table data Click table name → Browse tab
Edit a record Browse tab → Click pencil icon on a row
Delete a record Browse tab → Click trash icon on a row
Delete a table Click table name → Drop (at bottom)
Export database Click database name → Export tab → SQL → Go
Import database Click database name → Import tab → Choose file → Go

Troubleshooting Common Issues

Problem Solution
Cannot access phpMyAdmin Ensure Apache and MySQL are running in XAMPP Control Panel
Database not showing Refresh the page or click the database name in left sidebar
Error creating table Check if table name already exists or column definitions are valid
Cannot insert data Ensure data types match (e.g., do not put text in INT column)

Frequently Asked Questions

  1. What is the default username and password for phpMyAdmin?
    Default username is "root" with no password (leave password field empty).
  2. Can I create multiple databases?
    Yes, you can create as many databases as you want. Each database can have multiple tables.
  3. What is the difference between MySQL and phpMyAdmin?
    MySQL is the database server. phpMyAdmin is a tool to manage MySQL databases visually.
  4. How do I delete a database?
    Click on the database name, then click the "Drop" button at the bottom. This action cannot be undone.
  5. How do I back up my database?
    Click on the database name, then click the "Export" tab, choose SQL format, and click "Go".
  6. What should I learn next after creating databases?
    After creating your database, learn how to connect PHP to MySQL and SQL basics.

You have successfully created a database named "users" and a table named "admin" with columns for id, name, and age. You inserted sample data (ADAM, 29) using both the graphical interface and SQL queries. phpMyAdmin makes database management accessible to beginners while remaining powerful enough for advanced users.

Now that your database is ready, the next step is connecting it to PHP to build dynamic web applications. Practice creating different tables and experimenting with various data types to build your confidence.

Recently Added Tutorials