PHP PDO CREATE DATABASE - Creating a Database

Creating a Database (CREATE DATABASE)

To execute SQL commands that do not return a result set (such as CREATE DATABASE), we use the exec() method in PDO. This method executes the command and returns the number of rows affected.

EXAMPLE

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

try {
    // NOTE: Since we are creating the database, we MUST NOT include the "dbname=..." parameter in the DSN string, 
    // as the database does not physically exist on the server yet. We connect only to the general MySQL server.
    // We have also added "charset=utf8mb4" for secure support of international characters and emojis.
    $dsn = "mysql:host=$server;charset=utf8mb4";

    // Establishing a universal PDO connection to the server
    $connection = new PDO($dsn, $username, $password);
    
    // Enable strict exceptions. If anything fails, PHP will immediately jump to the "catch" block.
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // SQL command to create a database with built-in support for modern character sets
    $sql = "CREATE DATABASE pdo_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";
    
    // WHY USE THE exec() METHOD?
    // In PDO, we have two main methods for direct commands: query() and exec().
    // For SQL commands that DO NOT return data rows (such as CREATE, DROP, INSERT, UPDATE, DELETE),
    // we always use exec(). This method is faster and simply returns the number of affected rows.
    $connection->exec($sql);
    
    echo "Database 'pdo_database' was successfully created!";
} catch (PDOException $e) {
    // If the pdo_database already exists, the server will reject the command, and the code will safely finish here:
    echo "Error creating database: " . $e->getMessage();
}

// Closing the connection by removing the PDO object from memory
$connection = null;
?>

RESULT

The result will be displayed here...

When creating a database, we first connect to the server (without specifying the database name in the DSN string), and then we execute the command. We wrap the entire process in a try...catch block to catch any access errors.

The exec() method is fast and efficient for structural SQL commands.

Using try...catch prevents the script from crashing upon an error.

After successful execution, we can immediately begin creating tables.