Creating a MySQL Table with PHP

A table is where MySQL actually stores your rows of data. Before you can insert users, products, or posts, the table that holds them has to exist, with a defined set of columns and the data type of each column. This page shows how to create a MySQL table from PHP using both the mysqli and PDO extensions, explains the CREATE TABLE syntax piece by piece, and covers the errors you are most likely to hit.

If you have not yet created the database that the table will live in, do that first — see Creating a MySQL Database. For a deeper look at the connection itself, see Connecting to MySQL with PHP.

How creating a table works

From PHP, creating a table is always three steps:

1. Connect to the MySQL server and select a database. A table must belong to a database, so the connection has to know which one to use.
2. Build the SQL CREATE TABLE statement as a string. This is plain SQL — PHP just delivers it to the server.
3. Execute the statement with mysqli_query() (mysqli) or $pdo->exec() (PDO) and check whether it succeeded.

The most common beginner mistake is connecting without naming a database. CREATE TABLE then fails with "No database selected" because the server has nowhere to put the table. The examples below always pass the database name so this cannot happen.

Connecting and selecting a database

mysqli_connect() takes the server name, username, and password — and, importantly, an optional fourth argument: the database name. Pass it so the new table has a home.

<?php

$server   = "localhost";
$username = "username";
$password = "password";
$database = "my_app"; // the database the table will be created in

$conn = mysqli_connect($server, $username, $password, $database);

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

?>

Creating a table with mysqli

Once the connection has a database selected, run the CREATE TABLE statement with mysqli_query(). It returns true on success and false on failure, so always check the result and print mysqli_error() when it fails — that message tells you exactly what went wrong.

The following creates a users table with three columns: id, name, and email.

<?php

$sql = "CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    email VARCHAR(50) NOT NULL UNIQUE
)";

if (mysqli_query($conn, $sql)) {
    echo "Table created successfully";
} else {
    echo "Error creating table: " . mysqli_error($conn);
}

?>

What each part of the statement means

• id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY — an integer that can never be negative (UNSIGNED), automatically increases by one for every new row (AUTO_INCREMENT), and uniquely identifies each row (PRIMARY KEY). Almost every table has a column like this.
• name VARCHAR(30) NOT NULL — a variable-length string up to 30 characters. NOT NULL means a value is required; an insert without a name will be rejected.
• email VARCHAR(50) NOT NULL UNIQUE — a required string with a UNIQUE constraint, so MySQL refuses to store two rows with the same email.

Common column data types

Creating a table with PDO

PDO is the more modern, portable API and is preferred for new projects. The SQL is identical — only the PHP wrapper changes. With PDO in exception mode you wrap the call in a try/catch instead of checking a return value.

<?php

$dsn = "mysql:host=localhost;dbname=my_app";

try {
    $pdo = new PDO($dsn, "username", "password");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "CREATE TABLE users (
        id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(30) NOT NULL,
        email VARCHAR(50) NOT NULL UNIQUE
    )";

    $pdo->exec($sql);
    echo "Table created successfully";
} catch (PDOException $e) {
    echo "Error creating table: " . $e->getMessage();
}

?>

Avoiding the "table already exists" error

If you run a CREATE TABLE statement twice, the second run fails with "Table 'users' already exists". Add IF NOT EXISTS to make the statement safe to re-run — MySQL simply skips creation when the table is already there.

<?php

$sql = "CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    email VARCHAR(50) NOT NULL UNIQUE
)";

mysqli_query($conn, $sql);

?>

Common errors and fixes

• "No database selected" — you connected without a database. Pass it as the fourth argument to mysqli_connect() or use mysqli_select_db().
• "Table 'users' already exists" — use CREATE TABLE IF NOT EXISTS, or drop the old table first.
• "You have an error in your SQL syntax" — usually a stray comma after the last column, or a reserved word like order used as a column name without backticks. Wrap such names in backticks: `order`.
• "Access denied for user" — the MySQL account lacks CREATE privileges on that database.

Next steps

Now that the table exists, you can start working with the data inside it:

• Insert data into a MySQL table
• Select data from a MySQL table
• Use prepared statements to insert user input safely
• The mysqli extension overview

Summary

Creating a table from PHP comes down to connecting with a database selected, writing a CREATE TABLE statement that defines each column and its type, and executing it with mysqli_query() or PDO::exec(). Always check for errors, add IF NOT EXISTS for scripts you may re-run, and choose a sensible data type for every column so MySQL stores your data efficiently and safely.

Practice