PHP & MySQL - Create a database Tutorial

In this tutorial we learn how to create a MySQL/MariaDB database using PHP and SQL queries.


For this tutorial, please start the MySQL module in your XAMPP control panel.

How to create a database

To create a database, we will need to write an SQL query and execute it with the msqli_query() function.

To create a database with an SQL query we simply write the keywords CREATE Database followed by the database name.

Syntax:
 CREATE Database DatabaseName
Example:
<?php

$serverName = "localhost";
$userName = "root";
$password = "";

// create connection
$conn = mysqli_connect($serverName, $userName, $password);

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

// SQL query to create database
$sql = "CREATE DATABASE trainingDB";

// execute and check if query succeeded
if (mysqli_query($conn, $sql)) {
    echo "Database created successfully";
} else {
    echo "Could not create database. Error: " . mysqli_error($conn);
}

// close connection
mysqli_close($conn);

?>

In the example above, we create a database with the name trainingDB.

We can go to localhost/phpmyadmin/index.php to access the phpMyAdmin area.

In the left menu panel we should see a new database called trainingDB.

If you receive the error: Access denied for user ''@'localhost' to database 'trainingDB' it means that the connection wasn’t made.

The most likely reason for the error is wrong servername/username/password credentials or an empty username.

How to connect to the new database

The mysqli_connect() function can connect directly to the server and the database in one execution. To do this, we need to add the database name as a argument after the password.

Syntax:
 $result = mysqli_connect(servername, username, password, databasename);
Example: connect to the database
<?php

$serverName = "localhost";
$userName = "root";
$password = "";
$dbName = "trainingDB";

// create connection
$conn = mysqli_connect($serverName, $userName, $password, $dbName);

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

// close connection
mysqli_close($conn);

?>

Because we now have a database to connect to, we can tell the mysqli_connect() function to connect to it right after it connects to the server.

Summary: Points to remember

  • We create a database with the CREATE DATABASE query.
  • If we already have a database, we can connect to the server and the database in one execution by adding the database name as an argument.