• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Create DB

← Previous Next →

Creating a database is one of the first steps when building a web application. A database stores structured data that PHP can access, manipulate, and display on your website.
In MySQL, creating a database is simple using SQL commands or PHP scripts. This tutorial will guide you through creating a database manually, using PHP with MySQLi, and using PDO, along with best practices and error handling.

Why Create a Database?

A database organizes information in a structured manner so it can be easily accessed, modified, and managed. For example, if you are building a school management system, you need databases to store:

  • Students

  • Teachers

  • Courses

  • Attendance records

Each database can contain multiple tables to logically separate different kinds of information. Creating a dedicated database ensures your application is organized and scalable.

Creating a Database Using SQL

The basic SQL command to create a database is:

CREATE DATABASE school_db;

  • CREATE DATABASE is the SQL keyword.

  • school_db is the name of your database.

Avoid Duplicate Database Errors

If you try to create a database that already exists, MySQL will throw an error. To avoid this, use:

CREATE DATABASE IF NOT EXISTS school_db;

This command checks whether the database exists and creates it only if it doesn’t.

Viewing Databases

After creating a database, you can view all databases on the MySQL server using:

SHOW DATABASES;

You can also select the current database you are working with:

SELECT DATABASE();

These commands help ensure your database has been created and is ready to use.

Creating a Database Using PHP (MySQLi)

You can also create a database directly from PHP using the MySQLi extension.

Example (Procedural Style)

<?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 to create database
$sql = "CREATE DATABASE IF NOT EXISTS school_db";
if (mysqli_query($conn, $sql)) {
    echo "Database created successfully!";
} else {
    echo "Error creating database: " . mysqli_error($conn);
}

// Close connection
mysqli_close($conn);
?>

Explanation

  • mysqli_connect() establishes a connection to the MySQL server.

  • CREATE DATABASE IF NOT EXISTS ensures no duplicate errors.

  • mysqli_query() executes the SQL command.

  • Errors are handled gracefully using mysqli_error().

Example (Object-Oriented Style)

<?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);
}

// Create database
$sql = "CREATE DATABASE IF NOT EXISTS school_db";
if ($conn->query($sql) === TRUE) {
    echo "Database created successfully!";
} else {
    echo "Error creating database: " . $conn->error;
}

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

This approach is cleaner and aligns with object-oriented programming practices in PHP.

Creating a Database Using PDO

PDO offers a more flexible and secure approach, supporting multiple database types.

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

try {
    $conn = new PDO("mysql:host=$servername", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "CREATE DATABASE IF NOT EXISTS school_db";
    $conn->exec($sql);
    echo "Database created successfully!";
} catch(PDOException $e) {
    echo "Error creating database: " . $e->getMessage();
}

// Close connection
$conn = null;
?>

Explanation

  • new PDO() connects to MySQL.

  • exec() executes SQL statements.

  • Exceptions handle errors, providing clear messages if creation fails.

Choosing Database Names

  • Use descriptive names that reflect the purpose of the database, e.g., inventory_system, student_records.

  • Avoid using special characters or spaces.

  • Use lowercase letters and underscores for readability and consistency.

Best Practices When Creating Databases

  1. Check for existence: Use IF NOT EXISTS to avoid errors.

  2. Set character set: Use utf8mb4 for modern character support.

  3. Backup regularly: Always back up databases to prevent data loss.

  4. Restrict access: Grant database permissions only to specific users.

  5. Use environment variables: Store database credentials securely in production.

Common Errors

Error Cause Solution
Access denied for user Incorrect username/password Check credentials
Unknown MySQL server host Wrong server name Correct the host name
Database already exists Duplicate creation Use IF NOT EXISTS
Syntax error Typo in SQL command Double-check SQL syntax

Summary of the Tutorial

  • Creating a database is the first step in building a structured web application.

  • SQL allows you to create databases manually or conditionally using IF NOT EXISTS.

  • PHP can create databases using MySQLi (procedural or OOP) or PDO.

  • Proper error handling ensures smooth creation and prevents common mistakes.

  • Always follow best practices for naming, permissions, and backups.

Creating a database is foundational for further steps like creating tables, inserting data, and building dynamic web applications with PHP and MySQL.

Practice Questions

  1. Write a SQL command to create a database named library_system.

  2. Modify the SQL command to create the database only if it does not already exist.

  3. Write a SQL command to display all databases on the MySQL server.

  4. Write a PHP script using MySQLi (procedural) to create a database named online_store and display a success or error message.

  5. Write the same PHP script using MySQLi object-oriented style.

  6. Write a PHP script using PDO to create a database named employee_records and include proper error handling.

  7. Explain the difference between using CREATE DATABASE dbname; and CREATE DATABASE IF NOT EXISTS dbname;.

  8. Write a PHP script that tries to create a database named student_portal and prints an error message if the database already exists.

  9. Create a database named inventory_db with the character set utf8mb4 using SQL.

  10. Write a reusable PHP file db_create.php that connects to MySQL and creates a database called school_records, then include it in another PHP script to verify the database creation.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

PHP

online coding class codepractice

PHP Basics

PHP Data

Control Flow

PHP Functions

PHP Arrays

PHP Forms

PHP Advanced

Cookies & Sessions

Filters & JSON

PHP Exceptions

PHP OOP

MySQL with PHP

PHP and XML

AJAX with PHP

codepractice logo
Login
Go Back Top