PHP MySQL Create Database – Step-by-Step Guide with Best Practices

Creating a MySQL database using PHP is essential for web development. In this guide, we will cover the complete process of creating a database using PHP and MySQL, including best practices for security and efficiency.

Setting Up Your Environment

Before starting, ensure you have the following installed:

PHP (7.x or later)
MySQL (or MariaDB)
Apache or Nginx (optional for testing)
phpMyAdmin (optional GUI for MySQL)

Connect to MySQL Using PHP

To interact with MySQL, use the mysqli or PDO extension. Here’s how to connect:

Using `mysqli`

<?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);
}
echo "Connected successfully";
?>

Using PDO

<?php
$dsn = "mysql:host=localhost";
$username = "root";
$password = "";

try {
    $conn = new PDO($dsn, $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Create a MySQL Database

Once connected, you can create a database using SQL.

Example Using `mysqli`

<?php
$servername = "localhost";
$username = "root";
$password = "";
$conn = new mysqli($servername, $username, $password);

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

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

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

$conn->close();
?>

Example Using `PDO`

<?php
$dsn = "mysql:host=localhost";
$username = "root";
$password = "";

try {
    $conn = new PDO($dsn, $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $sql = "CREATE DATABASE mydatabase";
    $conn->exec($sql);
    
    echo "Database created successfully";
} catch (PDOException $e) {
    echo "Error creating database: " . $e->getMessage();
}
?>

Verify the Database

You can check if the database was created successfully using phpMyAdmin or via the command line:

Best Practices for Creating a Database in PHP

Use Environment Variables – Never hardcode credentials. Store them in .env files.
Use PDO Over mysqli – PDO supports multiple databases and provides better error handling.
Enable Error Reporting – Use PDO::ERRMODE_EXCEPTION to catch errors efficiently.
Close Connections – Always close database connections after executing queries.
Validate User Input – Avoid SQL injections by using prepared statements.