Creating a User Database and Table Using SQL

A login system needs a users table that can identify accounts, store password hashes and enforce roles. This example uses fictional accounts and prepares the database for PDO-based PHP pages.

Never store plain-text passwords. The password field below stores the output from PHP’s password_hash() function.

Create the database

Open http://localhost/phpmyadmin/, select SQL, and run:

CREATE DATABASE project_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE project_db;

Create the users table

CREATE TABLE users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role VARCHAR(20) NOT NULL DEFAULT 'user',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT chk_user_role CHECK (role IN ('user', 'admin'))
);

Some older MariaDB versions accept but do not enforce CHECK constraints. PHP must still validate the role, and ordinary registration must never accept an administrator role from the user.

Create a fictional administrator

Generate a hash with PHP’s password_hash() or the classroom password-hasher tool. Insert the generated hash—not the test password:

INSERT INTO users (username, password, role)
VALUES ('admin_demo', '$2y$10$REPLACE_WITH_A_REAL_TEST_HASH', 'admin');

Use only a fictional test password. Never enter a personal, school or reused password into a classroom tool.

Verify the table

DESCRIBE users;
SELECT user_id, username, role, created_at FROM users;

Do not select or display password hashes unless diagnosing a specific local problem.

Check

• Username is unique.
• Password field is long enough for modern hashes.
• New accounts default to user.
• Administrator status cannot be selected during public registration.
• All accounts and data are fictional.