Create DB

If you are working with PHP and MySQL, one of the first tasks you may need is to create a DB (Database) to store and manage your data.

In this tutorial, I will explain everything you need to know about creating a MySQL database, covering it with examples in PDO and MySQLi.

Create a MySQL Database (DB) Using MySQLi in PHP

A database is something like a container where your application data will be stored. You will see tables, rows, and other functions. Every one of them has its task like in tables we can store records or fetch results.

So when you decide to create a database you will follow the below process:-

In the following sections, you will learn:

Create a Connection to MySQL

The first step is to connect PHP to your MySQL server which requires you to use the mysqli_connect() function or the new mysqli() object for this purpose. Here is an example:

// Database credentials
$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);
}
echo "Connected successfully";

Create a Database

Once the connection is established, you can create a database using an SQL query. Here is how:

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

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

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

Verify Database Creation

To ensure that the database was created, log into your MySQL server and run:

SHOW DATABASES;

You should see school listed in the output.

We can handle errors using connect_error in MySQLi. This helps us to know what the error is. Here is an example:

// Database credentials
$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);
}

// SQL to create a database
$sql = "CREATE DATABASE school";
if ($conn->query($sql) === TRUE) {
    echo "Database created successfully";
} else {
    // Custom error message
    echo "Failed to create database. Error: " . $conn->error;
}

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

In this example, you will know the reason if something is wrong during the database creation.

You can also achieve the same task with PDO. Let's move on to the following section to see how can we do that.

Create a Database Using PDO in PHP and MySQL

You can use PDO to create a database, and it is a great way to keep things secure. Whether you are starting from scratch or adding a database to an existing project.

Before diving into code, let us quickly understand what PDO is.

PDO is a PHP extension that helps you interact with databases. It is a rockstar because:

Connect to MySQL Using PDO

The first step you must to do, is to create a connection with the MySQL server. Here is an example:

try {
    // Connection details
    $dsn = "mysql:host=localhost";
    $username = "root";
    $password = "";

    // Create a new PDO instance
    $pdo = new PDO($dsn, $username, $password);

    //This is a setting for error mode to exceptions
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    echo "Connected to MySQL successfully!";
} catch (PDOException $e) {
    // This handle connection errors
    echo "Connection failed: " . $e->getMessage();
}

The $dsn is like the GPS of your database - it tells PDO what type of database you're connecting to and where that database lives. When you call new PDO, that's actually when the connection gets initiated, so to speak. Now it gets really cool: this sets PDO::ERRMODE_EXCEPTION so that you get clear error messages if something goes wrong.

Use PDO to Create the Database

You can use an SQL CREATE DATABASE command to set up your database Once connected. Here is an example:

try {
    // Connection details
    $dsn = "mysql:host=localhost";
    $username = "root";
    $password = "";

    // Create a new PDO instance
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

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

    // Execute the SQL statement
    $pdo->exec($sql);

    echo "Database created successfully!";
} catch (PDOException $e) {
    // Handle errors during database creation
    echo "Error creating database: " . $e->getMessage();
}

But, if you want more control, you can specify the charset and collation to make your database compatible with your needs:

$sql = "CREATE DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci";

So when you do that, you ensure your database supports emojis, multilingual text, and more.

Anyway, let's move on to the following part, to see how we can create a dynamic database.

Dynamic Database Creation

Here is an example show you how to create a database based on user input:

try {
    $dsn = "mysql:host=localhost";
    $username = "root";
    $password = "";

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

    // Get the database name dynamically (e.g., from a form)
    $dbName = "user_database";

    // Validate the database name
    if (!preg_match('/^[a-zA-Z0-9_]+$/', $dbName)) {
        throw new Exception("Invalid database name.");
    }

    $sql = "CREATE DATABASE $dbName";
    $pdo->exec($sql);

    echo "Database '$dbName' created successfully!";
} catch (PDOException $e) {
    echo "Database error: " . $e->getMessage();
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}

Note that in this step, you will have to validate the user input to avoid SQL injection and/or vulnerability to your database. Here is a simple example using regex /^[a-zA-Z0-9_]+$/ and help you validate that the given name is alphanumeric.

Wrapping Up

You'll have the capability to connect to your database, run SQL commands, and handle errors with either MySQLi or PDO. You will go from zero to a fully-implemented database that completes your application in a few steps.

Anyway, here is a quick recap:

 $pdo = new PDO($dsn, $username, $password);
 $pdo->exec("CREATE DATABASE my_database");

That's all, thank you for reading. Happy Coding.