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

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
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER, name TEXT, email TEXT)");
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
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)");
$db->exec("INSERT INTO users (name) VALUES ('Alice')");
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
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    role TEXT DEFAULT 'member'
)");
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";
}
?>

Common Mistakes

Forgetting to define a primary key on a new table, making it much harder to reliably update or delete specific rows later.
Choosing an overly generic column type (like TEXT for everything) instead of an appropriate type (INT, VARCHAR, DATE) that matches the actual data and enables proper validation and indexing.
Running CREATE TABLE without IF NOT EXISTS in a script that might run more than once, causing an error on the second run.

Chapter Summary

CREATE TABLE tableName (column definitions...) defines a new table's structure, run through mysqli_query() like any other SQL statement.
A primary key column (often an auto-incrementing id) uniquely identifies each row and is essential for reliable updates and deletes.
IF NOT EXISTS makes table creation safe to re-run without erroring if the table already exists.

Browser Support

CREATE TABLE is standard SQL, and running it through mysqli_query() works identically across all MySQL and MariaDB versions PHP supports.

🔒

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