Creating a MySQL Database with PHP

A database is the named container that holds your tables, and every table holds your application's data in rows and columns. Before you can store users, orders, or blog posts, that container has to exist on the MySQL server. This chapter walks through the full sequence in PHP: connect to the server, run a CREATE DATABASE statement, then add a table inside it.

You should already have PHP and a MySQL (or MariaDB) server installed and running. If your connection step fails, see PHP Connect to MySQL and PHP MySQLi for setup details.

Two ways to talk to MySQL

PHP ships with two modern extensions for MySQL: MySQLi and PDO. Both are safe and supported; this chapter uses MySQLi because it is MySQL-specific and reads simply, but the same CREATE DATABASE SQL works through PDO too. Use PDO when you may switch database engines later, and prefer prepared statements whenever user input touches a query.

The old mysql_* functions were removed in PHP 7 — do not use them.

Step 1 — Connect to the MySQL server

To create a database you connect to the server, not to a specific database (it doesn't exist yet). You pass the host, username, and password, but no database name:

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

// Create connection (no database name — we're about to create one)
$conn = new mysqli($servername, $username, $password);

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

new mysqli(...) returns a connection object. If anything goes wrong, $conn->connect_error holds the message, and die() stops the script with it. The username/password above are placeholders — replace them with your own credentials.

The three steps below are shown as separate snippets for clarity. In a real project you would keep the connection code in one file and pull it in with require or include.

Step 2 — Create the database

With a server connection open, run a CREATE DATABASE statement through $conn->query(). For a statement that returns no rows (like CREATE, INSERT, or UPDATE), query() returns TRUE on success and FALSE on failure:

<?php
// SQL to create a database named myDB
$sql = "CREATE DATABASE myDB";

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

$conn->close();
?>

Tip: use CREATE DATABASE IF NOT EXISTS myDB to avoid an error if the database already exists — handy in setup scripts that may run more than once.

Step 3 — Create a table inside the database

A database on its own is empty. Data lives in tables, which you define with CREATE TABLE — a table name followed by its columns and their data types. This time you connect with the database name (the fourth argument) so the new table lands in the right place:

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

// Connect, this time selecting the myDB database
$conn = new mysqli($servername, $username, $password, $dbname);

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

// SQL to create a table
$sql = "CREATE TABLE MyGuests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname  VARCHAR(30) NOT NULL,
    email     VARCHAR(50),
    reg_date  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

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

$conn->close();
?>

What the column definitions mean:

• id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY — a unique whole number generated automatically for each row. UNSIGNED means no negatives; PRIMARY KEY makes it the row's identifier.
• VARCHAR(30) — variable-length text up to 30 characters.
• NOT NULL — the column must always have a value.
• reg_date TIMESTAMP — set to the current time on insert and refreshed on every update.

Note: the old display-width syntax INT(6) is deprecated since MySQL 8.0.17 and never affected storage or range, so plain INT is preferred.

Common errors and how to fix them

• "Access denied for user" — wrong username or password, or the user lacks the CREATE privilege. Check your credentials and grants.
• "Can't create database 'myDB'; database exists" — it's already there. Drop it first, or use CREATE DATABASE IF NOT EXISTS.
• $conn->error is empty but the query failed — make sure you compared with === TRUE; a successful CREATE returns the boolean true, not a result set.

Where to go next

Once your database and tables exist, the next steps are putting data in and reading it back:

• Insert Data into MySQL — add rows with INSERT INTO.
• Select Data from MySQL — read rows with SELECT.
• MySQL Prepared Statements — the safe way to use user input in queries.
• Create a MySQL Table — a deeper look at CREATE TABLE.

Conclusion

Creating a MySQL database in PHP comes down to three moves: open a connection to the server, run CREATE DATABASE, then connect to that database and run CREATE TABLE. Checking $conn->connect_error and the return value of query() at each step turns silent failures into clear messages — the habit that makes the rest of your data layer reliable.

Practice