Creating Database & Tables using phpMyAdmin

Introduction

Before PHP can store or retrieve data, we need a database and tables.
In this lesson, you’ll learn how to create a database and tables using phpMyAdmin without writing complex SQL.

This method is ideal for beginners and shared hosting users.

Prerequisites

• You should have PHP + MySQL running on your system
• phpMyAdmin should be accessible (local server like XAMPP / WAMP or hosting panel)

What Is phpMyAdmin Used For?

phpMyAdmin allows you to:

• Create databases
• Create tables
• Insert and edit records
• Manage database structure visually

Most hosting providers include phpMyAdmin by default.

Step 1: Open phpMyAdmin

1. Login to your hosting control panel
2. Open phpMyAdmin
3. You’ll see the phpMyAdmin dashboard

Step 2: Create a New Database

1. Click Databases from the top menu
2. Enter a database name (example: php_tutorial)
3. Click Create

Your database is now created successfully.

Step 3: Select the Database

• Click on your newly created database from the left sidebar
• You’ll see an option to create a table

Step 4: Create a Table

1. Enter table name (example: users)
2. Enter number of columns (example: 3)
3. Click Create

Step 5: Define Table Columns

Example table structure:

Column Name	Type	Length	Description
id	INT	11	Primary Key
name	VARCHAR	100	User name
email	VARCHAR	100	User email

Important settings:

• Set id as Primary Key
• Enable AUTO_INCREMENT for id

Click Save.

Step 6: Insert Data into Table (Optional)

1. Click Insert
2. Enter values
3. Click Go

This helps test the table.

Step 7: View and Edit Data

• Click Browse to view records
• Click Edit to update
• Click Delete to remove records

Step 8: Export & Import Database (Optional)

phpMyAdmin allows:

• Export database as SQL file
• Import database from backup

Useful for backups and migration.

Common Data Types Used in Database Tables

While creating database tables in MySQL, choosing the correct data type for each column is very important. Data types define what kind of data a column can store and how much space it will use.

INT

The INT data type is used to store whole numbers (numbers without decimal values).
It is commonly used for IDs, counts, and numeric values such as student IDs, class IDs, or age.

Example use cases:

• Primary Key values
• Foreign Key references
• Total marks, quantity, or counters

VARCHAR

The VARCHAR data type is used to store short text or strings.
You must specify a maximum length, such as VARCHAR(50) or VARCHAR(255).

Example use cases:

• Names (student name, class name)
• Email addresses
• Usernames

TEXT

The TEXT data type is used to store long textual content.
Unlike VARCHAR, it is suitable when you don’t know the exact length of the text or when the content is large.

Example use cases:

• Descriptions
• Comments
• Messages or notes

DATE

The DATE data type is used to store date values in the format YYYY-MM-DD.
It does not store time, only the date.

Example use cases:

• Date of birth
• Admission date
• Record creation date

Primary Key

A Primary Key is a column (or a group of columns) that uniquely identifies each row in a table.

In simple words:
No two rows can have the same primary key value, and it cannot be empty (NULL).

Example:
In a users table, id is usually the primary key:

• User 1 → id = 1
• User 2 → id = 2
• User 3 → id = 3

Why it is important:

• Helps uniquely identify each record
• Makes searching and updating records faster
• Maintains data integrity (no duplicate records)

Foreign Key

A Foreign Key is a column that creates a link between two tables.

In simple words:
It stores the primary key value of another table to create a relationship between them.

Example:

• users table → id (Primary Key)
• orders table → user_id (Foreign Key)

This means:

Each order belongs to a user.

Why it is important:

• Connects related data across tables
• Maintains relationships between tables
• Prevents invalid data (e.g., order without a valid user)

Difference Between Primary Key and Foreign Key

Feature	Primary Key	Foreign Key
Purpose	Uniquely identifies each row in a table	Links one table to another
Uniqueness	Must be unique	Can have duplicate values
NULL allowed?	❌ Not allowed	✅ Allowed (depending on design)
Table relation	Belongs to the same table	Refers to another table’s primary key
Example	users.id	orders.user_id

Common Mistakes to Avoid

• Forgetting primary key
• Not using AUTO_INCREMENT
• Using wrong data types
• Creating too many columns initially

Best Practices

• Use meaningful table names
• Keep column names simple
• Plan table structure before creating
• Avoid storing unnecessary data

Practice Task

• Design two tables, classes and students, and relate them by using class_id in the students table as a foreign key that references the primary key of the classes table.

Summary

• phpMyAdmin makes database creation easy
• Databases store structured data
• Tables organize data in rows and columns
• Proper structure is important for PHP interaction
• Learn about Datatypes used while creating tables
• Learn about Primary Key and Foreign Key

In the next tutorial, we’ll learn about Connecting PHP with MySQL (mysqli).