PHP MySQL Create Database

A database is a container that holds tables and data. Before you can create tables or store data, you must first create the database itself. In PHP, you do this by sending a CREATE DATABASE SQL statement to the MySQL server.

The SQL Statement

The standard SQL command to create a database is very simple:

CREATE DATABASE myDB;

1. MySQLi (Object-Oriented)

To create a database using the object-oriented approach, you connect to the server (without specifying a database name) and execute the query using the query() method.

<?php
    $servername = "localhost";
    $username = "username";
    $password = "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 myDB";
    if ($conn->query($sql) === TRUE) {
        echo "Database created successfully";
    } else {
        echo "Error creating database: " . $conn->error;
    }

    $conn->close();
?>

2. MySQLi (Procedural)

In the procedural style, you use the mysqli_query() function to send the command to the server.

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

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

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

    // Create database
    $sql = "CREATE DATABASE myDB";
    if (mysqli_query($conn, $sql)) {
        echo "Database created successfully";
    } else {
        echo "Error creating database: " . mysqli_error($conn);
    }

    mysqli_close($conn);
?>

3. PDO

With PDO, you use the exec() method to run the SQL command. PDO is great because it uses exceptions to handle errors automatically.

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

    try {
        $conn = new PDO("mysql:host=$servername", $username, $password);
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        
        $sql = "CREATE DATABASE myDBPDO";
        // use exec() because no results are returned
        $conn->exec($sql);
        echo "Database created successfully<br>";
    } catch(PDOException $e) {
        echo $sql . "<br>" . $e->getMessage();
    }

    $conn = null;
?>

Common Issues

• Database already exists: If you try to create a database that already exists, MySQL will return an error. You can use CREATE DATABASE IF NOT EXISTS myDB to prevent this.
• Permissions: The database user must have the "CREATE" privilege on the server.
• Server Connection: Ensure your server name, username, and password are correct before trying to create a database.

Summary

• The CREATE DATABASE SQL statement is used to create a new database.
• You connect to the MySQL server first, then execute the query.
• MySQLi uses query(), while PDO uses exec() for this action.
• Always check for errors to ensure the database was actually created.