PHP PDO CREATE TABLE - Creating a Table

Creating a Table (CREATE TABLE)

Once the database is created, we need tables within it. Using the exec() method, we send an SQL command to define columns, their data types (e.g., INT, VARCHAR), and set the primary key (ID).

EXAMPLE

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "pdo_database";

try {
    // NOTE: Unlike creating a database, here we MUST include the "dbname=$database" 
    // parameter in the DSN string. This tells PDO which database to connect to directly.
    // We have also added "charset=utf8mb4" for full support of special characters.
    $dsn = "mysql:host=$server;dbname=$database;charset=utf8mb4";

    $connection = new PDO($dsn, $username, $password);
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // SQL command to create a table with specific columns and data types
    $sql = "CREATE TABLE users (
        id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        first_name VARCHAR(30) NOT NULL,
        last_name VARCHAR(30) NOT NULL,
        email VARCHAR(50),
        registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )";

    // RECAP: Since the CREATE TABLE command does not return data rows, 
    // we use the fast and efficient exec() method to execute it.
    $connection->exec($sql);
    
    echo "Table 'users' was successfully created!";
} catch (PDOException $e) {
    // If the "users" table already exists in the database, an exception will occur, which we catch here:
    echo "Error creating table: " . $e->getMessage();
}

// Closing the connection
$connection = null;
?>

RESULT

The result will be displayed here...

In the example above, we created an users table. Note that in the DSN string, we now specify the database name (dbname=pdo_database), as we want to connect directly to it.

The exec() method is used for commands that do not return data.

The table structure determines how our future records will be stored.

Using AUTO_INCREMENT ensures that the ID of each new row increases automatically.