Create Database

Creating MySQL Databases Programmatically with PHP

PHP applications can execute DDL (Data Definition Language) SQL statements like CREATE DATABASE programmatically using PDO or MySQLi connections.

Database Creation Pipeline

flowchart TD
    A["Establish PDO Connection (Without dbname DSN)"] --> B["Execute 'CREATE DATABASE IF NOT EXISTS dbname'"]
    B --> C["Verify Execution Result"]
    C --> D["Reconnect with dbname Specified in DSN"]

Practical Code Example

<?php
declare(strict_types=1);

$host = '127.0.0.1';
$user = 'root';
$pass = 'secret_password';
$dbname = 'ecommerce_db';

try {
    // 1. Connect to MySQL server without specifying database in DSN
    $pdo = new PDO("mysql:host=$host;charset=utf8mb4", $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ]);

    // 2. Execute SQL CREATE DATABASE Query
    $sql = "CREATE DATABASE IF NOT EXISTS `$dbname` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";
    $pdo->exec($sql);

    echo "Database '$dbname' created successfully (or already exists).
";
} catch (PDOException $e) {
    die("Database Creation Failed: " . $e->getMessage());
}

Best Practices

• Always Include IF NOT EXISTS: Prevents database creation errors if the target database already exists.
• Use exec() for Data Definition Queries: Use $pdo->exec($sql) for DDL statements (CREATE, DROP, ALTER) that do not return result set rows.
• Specify utf8mb4 Charset and Collation: Always declare CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci during database creation.

Self-Check Challenge

Write a SQL query string to create a database shop_db only if it does not already exist! (CREATE DATABASE IF NOT EXISTS shop_db;)