PHP MySQL Create Database

Creating databases with PHP

🏗️ Creating a Database

Creating a database in MySQL using PHP is simple with the CREATE DATABASE statement. You connect to MySQL server and execute the SQL command to create a new database for storing your application's data. 


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

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Create database
$sql = "CREATE DATABASE mywebsite";
if ($conn->query($sql) === TRUE) {
    echo "Database created successfully!";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

Output:

Database created successfully!

Database Creation Methods

🎯

MySQLi OOP

Object-oriented approach 

$conn->query($sql);
🔧

MySQLi Procedural

Function-based approach 

mysqli_query($conn, $sql);
💎

PDO

Database-agnostic method 

$conn->exec($sql);
✅

Check Existence

Avoid duplicate errors 

CREATE DATABASE IF NOT EXISTS

🔹 MySQLi Object-Oriented Method

The most popular way to create a database using modern PHP. This method uses objects and is easy to read and maintain. 


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

// Create connection (no database selected yet)
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// SQL to create database
$sql = "CREATE DATABASE school_system";

// Execute query
if ($conn->query($sql) === TRUE) {
    echo "Database 'school_system' created successfully!";
} else {
    echo "Error creating database: " . $conn->error;
}

// Close connection
$conn->close();
?>

Output:

Database 'school_system' created successfully!

🔹 MySQLi Procedural Method

Uses functions instead of objects. Good for beginners who prefer a simpler, step-by-step approach. 


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

// Create connection
$conn = mysqli_connect($servername, $username, $password);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// SQL to create database
$sql = "CREATE DATABASE online_store";

// Execute query
if (mysqli_query($conn, $sql)) {
    echo "Database 'online_store' created successfully!";
} else {
    echo "Error: " . mysqli_error($conn);
}

// Close connection
mysqli_close($conn);
?>

Output:

Database 'online_store' created successfully!

🔹 PDO Method

PDO provides a consistent interface for multiple database types. It uses exception handling for better error management. 


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

try {
    // Create PDO connection
    $conn = new PDO("mysql:host=$servername", $username, $password);
    
    // Set error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // SQL to create database
    $sql = "CREATE DATABASE blog_website";
    
    // Execute query
    $conn->exec($sql);
    echo "Database 'blog_website' created successfully!";
    
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

// Connection closes automatically
$conn = null;
?>

Output:

Database 'blog_website' created successfully!

🔹 Create Database If Not Exists

Prevent errors by checking if the database already exists before creating it. This is useful when running setup scripts multiple times. 


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

$conn = new mysqli($servername, $username, $password);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Create database only if it doesn't exist
$sql = "CREATE DATABASE IF NOT EXISTS user_management";

if ($conn->query($sql) === TRUE) {
    echo "Database ready! (created or already exists)";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

Output:

Database ready! (created or already exists)

🔹 Create Database with Character Set

Specify character encoding to support international characters and emojis. UTF-8 is the most common choice for modern applications. 


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

$conn = new mysqli($servername, $username, $password);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Create database with UTF-8 encoding
$sql = "CREATE DATABASE social_media 
        CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci";

if ($conn->query($sql) === TRUE) {
    echo "Database created with UTF-8 support! 🎉";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

Output:

Database created with UTF-8 support! 🎉

🔹 Complete Setup Script

A practical example that creates a database and handles common scenarios: 


<?php
// Database setup script
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "ecommerce_site";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Create database
$sql = "CREATE DATABASE IF NOT EXISTS $dbname 
        CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci";

if ($conn->query($sql) === TRUE) {
    echo "✓ Database '$dbname' is ready!<br>";
    
    // Select the database
    $conn->select_db($dbname);
    echo "✓ Connected to database!<br>";
    echo "✓ Setup complete!";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

Output:

✓ Database 'ecommerce_site' is ready!
✓ Connected to database!
✓ Setup complete!

🔹 Database Naming Best Practices

Naming Rules:

  • Use lowercase: mywebsite (not MyWebsite)
  • Use underscores: online_store (not online-store)
  • Be descriptive: school_management (not db1)
  • Avoid spaces: Use underscores instead
  • Keep it short: But still meaningful
  • No special characters: Only letters, numbers, and underscores

🧠 Test Your Knowledge

Which SQL statement creates a new database?