PHP MySQL Create Table

Creating tables to store data

📊 Creating Tables

Tables are the foundation of database storage, organizing data into rows and columns. Use CREATE TABLE statement to define column names, data types, and constraints for structured data storage in your MySQL database. 


<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "mywebsite";

$conn = new mysqli($servername, $username, $password, $database);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// SQL to create table
$sql = "CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
    echo "Table 'users' created successfully!";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

Output:

Table 'users' created successfully!

Table Components

📝

Columns

Define data fields 

name VARCHAR(100)
🔑

Primary Key

Unique identifier 

id INT PRIMARY KEY
🔢

Auto Increment

Automatic numbering 

AUTO_INCREMENT
⚠️

Constraints

Data validation rules 

NOT NULL, UNIQUE

🔹 Basic Table Creation

Create a simple table with essential columns. This example shows a basic users table with common fields you'll use in most applications. 


<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "school_system";

$conn = new mysqli($servername, $username, $password, $database);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Create students table
$sql = "CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    age INT,
    grade VARCHAR(10)
)";

if ($conn->query($sql) === TRUE) {
    echo "Table 'students' created successfully!";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

Output:

Table 'students' created successfully!

🔹 Table with Multiple Data Types

Different columns need different data types. This example demonstrates various MySQL data types for storing different kinds of information. 


<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "online_store";

$conn = new mysqli($servername, $username, $password, $database);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Create products table with various data types
$sql = "CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    is_available BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
    echo "Table 'products' created with multiple data types!";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

Output:

Table 'products' created with multiple data types!

🔹 Table with Constraints

Constraints ensure data integrity by enforcing rules. They prevent invalid data from being inserted into your tables. 


<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "blog_website";

$conn = new mysqli($servername, $username, $password, $database);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Create table with constraints
$sql = "CREATE TABLE authors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    bio TEXT,
    website VARCHAR(200),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
    echo "Table 'authors' created with constraints!";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

Output:

Table 'authors' created with constraints!

🔹 Create Table If Not Exists

Prevent errors when running setup scripts multiple times by checking if the table already exists before creating it. 


<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "social_media";

$conn = new mysqli($servername, $username, $password, $database);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Create table only if it doesn't exist
$sql = "CREATE TABLE IF NOT EXISTS posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    likes INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
    echo "Table 'posts' is ready!";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

Output:

Table 'posts' is ready!

🔹 Table with Foreign Key

Foreign keys create relationships between tables, linking data across multiple tables for better organization and data integrity. 


<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "ecommerce_site";

$conn = new mysqli($servername, $username, $password, $database);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// First create users table
$sql1 = "CREATE TABLE IF NOT EXISTS customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
)";

// Then create orders table with foreign key
$sql2 = "CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    total DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
)";

if ($conn->query($sql1) === TRUE && $conn->query($sql2) === TRUE) {
    echo "Tables created with relationship!";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

Output:

Tables created with relationship!

🔹 Common Data Types

Text Types:

  • VARCHAR(n): Variable text up to n characters (max 255)
  • TEXT: Long text content (up to 65,535 characters)
  • CHAR(n): Fixed-length text (always n characters)

Number Types:

  • INT: Whole numbers (-2 billion to 2 billion)
  • DECIMAL(m,d): Exact decimal numbers (m digits, d after decimal)
  • FLOAT: Approximate decimal numbers

Date/Time Types:

  • DATE: Date only (YYYY-MM-DD)
  • DATETIME: Date and time (YYYY-MM-DD HH:MM:SS)
  • TIMESTAMP: Auto-updating date/time

Other Types:

  • BOOLEAN: True/False values (stored as 0 or 1)
  • ENUM: List of allowed values

🔹 Complete Setup Example

A practical script that creates multiple related tables for a complete application: 


<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "user_management";

$conn = new mysqli($servername, $username, $password, $database);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Create users table
$sql1 = "CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

// Create profiles table
$sql2 = "CREATE TABLE IF NOT EXISTS profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    full_name VARCHAR(100),
    bio TEXT,
    avatar VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)";

if ($conn->query($sql1) === TRUE && $conn->query($sql2) === TRUE) {
    echo "✓ All tables created successfully!<br>";
    echo "✓ Database structure is ready!";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

Output:

✓ All tables created successfully!
✓ Database structure is ready!

🧠 Test Your Knowledge

Which constraint ensures each value in a column is unique?