Creating a Database and Table

Before PHP can save anything, the database and its tables must exist. You can create them in phpMyAdmin (click around) or by running SQL.

Create the database

1

CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

utf8mb4 is the character set to use. It stores every character, including accents, Hindi letters and emoji. The older utf8 in MySQL is not full UTF-8, so avoid it.

Create a table

1
2
3
4
5
6
7
8
9
10

USE shop;

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NULL,
    role VARCHAR(20) NOT NULL DEFAULT 'user',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Read each column definition: name, type, then rules.

Common data types

A table for products

1
2
3
4
5
6
7

CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    category VARCHAR(50) NOT NULL DEFAULT 'general'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Relations: a foreign key

A post belongs to a user, so posts.user_id points to users.id.

1
2
3
4
5
6
7
8

CREATE TABLE posts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    title VARCHAR(200) NOT NULL,
    body TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The foreign key stops you from creating a post for a user that does not exist. ON DELETE CASCADE deletes a user’s posts when the user is deleted.

Add some data

1
2
3

INSERT INTO users (name, email) VALUES
    ('Ana', 'ana@example.com'),
    ('Ben', 'ben@example.com');

Use phpMyAdmin

1. Open phpMyAdmin (for example http://localhost/phpmyadmin).
2. Click New, type a database name and choose utf8mb4_unicode_ci.
3. Create a table, add columns, choose types, tick A_I (AUTO_INCREMENT) for the id and make it the primary key.
4. Use the SQL tab to paste the commands above.

Design tips

• Every table gets an id primary key.
• Use singular or plural names consistently (users, products).
• Store one fact in one place. Do not copy a user’s name into every post.
• Index columns that you search often (email, category). Primary and unique keys are already indexed.
• Never store plain passwords. The column holds a hash (see Password Hashing).