PHP MySQL Create Table

Once a database exists, it needs tables to actually hold structured data -- rows and columns with defined types, like a "users" table with id, name, and email columns. Running a CREATE TABLE statement through mysqli, the same way you'd run any other query, builds this structure from PHP.

In this page:

Defining a Basic Table Structure
Choosing a Primary Key
Column Constraints: NOT NULL, DEFAULT, UNIQUE
Making Table Creation Repeatable
Verifying a Table's Structure

Syntax

php

CREATE TABLE table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    column1 VARCHAR(100) NOT NULL,
    column2 VARCHAR(100) UNIQUE,
    column3 INT DEFAULT 0
);

Defining a Basic Table Structure

A CREATE TABLE statement lists each column's name and data type -- id INT, name VARCHAR(100), email VARCHAR(100) -- describing exactly what shape of data the table will hold, before any rows are ever inserted.

Note:

Choose column types that match the data as precisely as reasonable:
INT for whole numbers
VARCHAR(n) for short text with a sensible length limit
TEXT for longer free-form content.

Warning: A VARCHAR length that is too short for real-world data will silently truncate values or reject inserts, depending on the database's strict-mode settings.

Example: Defining a Basic Table Structure

php

<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER, name TEXT, email TEXT)");
// Print "Table created with 3 columns" to the output
echo "Table created with 3 columns";
?>

Choosing a Primary Key

A primary key uniquely identifies every row in a table, and an auto-incrementing integer id column (INT AUTO_INCREMENT PRIMARY KEY) is the most common choice -- MySQL assigns each new row the next available number automatically, guaranteeing uniqueness without any extra work.

Note: Default to an auto-incrementing integer id for the primary key unless you have a specific reason to use something else, like a natural unique identifier that already exists in your data.

Warning: A table without any primary key makes it much harder to reliably target one specific row for an UPDATE or DELETE, since you would have to match on potentially non-unique column values instead.

Example: Choosing a Primary Key

php

<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)");
$db->exec("INSERT INTO users (name) VALUES ('Alice')");
// Print `"Auto-assigned id: " . $db->lastInsertRowID()` to the output
echo "Auto-assigned id: " . $db->lastInsertRowID();
?>

Column Constraints: NOT NULL, DEFAULT, UNIQUE

Beyond just a type, a column can carry constraints: NOT NULL requires every row to have a value there, DEFAULT sets an automatic value when none is provided, and UNIQUE ensures no two rows share the same value in that column -- like guaranteeing no two users share an email address.

Note: Add NOT NULL to any column that should never be left empty, and UNIQUE to any column (like email or username) that must never be duplicated across rows.

Warning: Adding constraints after a table already contains data that violates them will fail -- constraints are far easier to define correctly upfront than to retrofit later.

Example: Column Constraints: NOT NULL, DEFAULT, UNIQUE

php

<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    role TEXT DEFAULT 'member'
)");
// Print "Constraints applied" to the output
echo "Constraints applied";
?>

Making Table Creation Repeatable

CREATE TABLE IF NOT EXISTS works exactly like its database-level counterpart -- MySQL only creates the table if it does not already exist, silently doing nothing otherwise, which makes a setup script safe to run more than once without erroring.

Note: Combine CREATE DATABASE IF NOT EXISTS and CREATE TABLE IF NOT EXISTS together in a single setup script to make the whole thing idempotent and safely re-runnable.

Warning: IF NOT EXISTS checks only whether a table with that name exists -- it does not verify the existing table actually has the columns and structure your script expects.

Example: Making Table Creation Repeatable

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE IF NOT EXISTS users (id INTEGER)");
$db->exec("CREATE TABLE IF NOT EXISTS users (id INTEGER)"); // safe to run again
echo "No error on second run";
?>

Verifying a Table's Structure

After creating a table, DESCRIBE tableName (or the equivalent SHOW COLUMNS query) returns each column's name, type, and constraints -- a quick way to confirm from PHP that a table was created exactly as intended.

Note: Run a DESCRIBE query during development or in a diagnostic script to double-check a table's actual structure matches what your CREATE TABLE statement intended.

Warning: DESCRIBE shows the table's current live structure -- if it was later altered by a separate ALTER TABLE statement, DESCRIBE reflects the current state, not the original CREATE TABLE.

Example: Verifying a Table's Structure

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER, name TEXT)");
$result = $db->query("PRAGMA table_info(users)"); // DESCRIBE users; in MySQL
while ($col = $result->fetchArray()) {
    echo $col['name'] . "\n";
}
?>

Chapter Quiz — Complete all 21 topics to unlock

0/21 topics done

Complete these topics first:

← Previous Lesson PHP MySQL Create DB Next Lesson → PHP MySQL Insert Data