PHP MySQL Create DB

A database itself -- the top-level container that holds all your tables -- can be created programmatically from PHP, not just through a database admin tool. Running a CREATE DATABASE SQL statement through mysqli, once you have a connection to the MySQL server (without necessarily selecting a specific database yet), does exactly this.

In this page:

Creating a Database with CREATE DATABASE
Avoiding Errors with IF NOT EXISTS
Checking for Creation Errors
Selecting a Database After Creating It
Choosing Character Set and Collation for a Database

Creating a Database with CREATE DATABASE

A connection to MySQL that does not specify a particular database can still run a CREATE DATABASE statement to create a brand-new one -- mysqli_query($conn, "CREATE DATABASE dbName") sends that SQL command and creates the database if the connecting user has permission.

Note: Connect without naming a specific database (leave that argument blank) when your script's job is specifically to create one that does not exist yet.

Warning: Creating a database requires the connecting MySQL user to have CREATE privileges -- a restricted application-level user often will not have this and needs an admin account for setup scripts.

Example: Creating a Database with CREATE DATABASE

php

<?php
// mysqli_query($conn, "CREATE DATABASE dbName") on a real MySQL server
$db = new SQLite3('app.db');
echo "Database file created";
?>

Avoiding Errors with IF NOT EXISTS

CREATE DATABASE IF NOT EXISTS dbName makes the statement safe to run more than once -- instead of raising an error when the database already exists, MySQL simply does nothing and lets the script continue, which is useful for setup scripts that might run multiple times.

Note: Use IF NOT EXISTS in any setup script meant to be safely re-runnable, like an installer or a first-run initialization routine.

Warning: IF NOT EXISTS silently does nothing if the database already exists -- it will not warn you if an existing database has a different structure than you expect.

Example: Avoiding Errors with IF NOT EXISTS

php

<?php
// CREATE DATABASE IF NOT EXISTS dbName -- safe to run more than once
$db = new SQLite3('app.db'); // creates the file only if it doesn't already exist
echo "Ready, whether or not it already existed";
?>

Checking for Creation Errors

mysqli_error($conn) returns a description of the most recent error on that connection -- checking it after a CREATE DATABASE call (or any query) tells you specifically why an operation failed, such as insufficient privileges or an invalid database name.

Note: Log the full error message from mysqli_error() during development and setup scripts, since it usually pinpoints the exact permission or syntax problem immediately.

Warning: A CREATE DATABASE failure due to insufficient privileges looks similar to one caused by a naming conflict -- always read the actual error text rather than guessing.

Example: Checking for Creation Errors

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE test (id INTEGER)");
if ($db->lastErrorCode()) {
    echo "Error: " . $db->lastErrorMsg();
} else {
    echo "No errors";
}
?>

Selecting a Database After Creating It

Creating a database does not automatically make it the active one for the current connection -- either run a separate USE dbName statement, or open a fresh connection that names the database directly as its fourth mysqli_connect() argument.

Note: For a setup script, it is often simplest to run CREATE DATABASE first, then immediately open a second connection that selects it, rather than juggling a USE statement mid-script.

Warning: Forgetting to select the database after creating it means any following CREATE TABLE or INSERT statements have no target database and will fail.

Example: Selecting a Database After Creating It

php

<?php
// USE dbName; -- or pass the db name as mysqli_connect()'s 4th argument
$db = new SQLite3('app.db'); // SQLite selects the file directly on connect
echo "Active database selected";
?>

Choosing Character Set and Collation for a Database

CREATE DATABASE dbName CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci sets the default character encoding and sorting rules for the database at creation time -- utf8mb4 is the modern recommended choice since it supports the full range of Unicode, including emoji.

Note: Always set utf8mb4 explicitly at database creation time; the older utf8 charset in MySQL is actually a restricted subset that cannot store every Unicode character.

Warning: Tables created later can override the database's default character set individually, so a database-level setting is a sensible default, not an absolute guarantee for every table.

Example: Choosing Character Set and Collation for a Database

php

<?php
// CREATE DATABASE dbName CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
echo "utf8mb4 supports the full Unicode range, including emoji";
?>

Common Mistakes

Trying to create a database with the same name as one that already exists, which raises an error unless you specifically check with IF NOT EXISTS in the SQL.
Running CREATE DATABASE with a connection that does not have sufficient privileges, since it requires elevated database-creation rights the everyday application user often does not have.
Forgetting that creating a database does not automatically select it -- you still need a separate USE statement or a fresh connection specifying that database.

Chapter Summary

CREATE DATABASE dbName creates a new, empty database, run through mysqli_query() on an existing connection.
A connection used to create a database does not need to select any particular database first, since CREATE DATABASE operates at the server level.
IF NOT EXISTS makes the CREATE DATABASE statement safe to re-run without erroring if the database already exists.

Browser Support

CREATE DATABASE is standard SQL, and running it through mysqli_query() works identically across all MySQL and MariaDB versions PHP supports.

🔒

Chapter Quiz — Complete all 21 topics to unlock

0/21 topics done

Complete these topics first:

← Previous Lesson PHP MySQL Connect Next Lesson → PHP MySQL Create Table