• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Database

← Previous Next →

When working with PHP, databases are at the heart of most web applications. Whether you’re managing users, storing product data, or handling form submissions, you’ll usually need a database to save and retrieve information efficiently.
In PHP, MySQL is one of the most popular databases due to its speed, reliability, and easy integration. This tutorial explains what a MySQL database is, how it works, and how you can manage it using PHP.

What is a Database?

A database is a structured collection of data. Think of it like a digital filing cabinet where each drawer (table) stores information in a well-organized format.
For example:

  • A users table might hold details like id, name, and email.

  • A products table could store product_id, product_name, and price.

Databases are designed to:

  • Store data persistently (even after the app stops running)

  • Retrieve information quickly

  • Handle multiple users and queries efficiently

What is MySQL?

MySQL is a widely used open-source relational database management system (RDBMS). It organizes data into tables, where each table consists of rows and columns.
It uses Structured Query Language (SQL) to interact with the data — for example, adding, reading, updating, or deleting records.

Common SQL operations:

Task SQL Example
Create a database CREATE DATABASE mydb;
Create a table CREATE TABLE users (id INT, name VARCHAR(50));
Insert data INSERT INTO users VALUES (1, 'Neha');
Read data SELECT * FROM users;

Why Use MySQL with PHP?

PHP and MySQL work seamlessly together. PHP scripts can:

  • Connect to a MySQL server

  • Execute SQL queries

  • Display, modify, or delete data from a web page

Benefits of using MySQL with PHP:

  1. Open-source – Free to use and easy to set up.

  2. Cross-platform – Works on Linux, Windows, or macOS.

  3. Integration – PHP has built-in extensions (mysqli, PDO) for database operations.

  4. Scalable – Suitable for small projects to enterprise-level systems.

MySQL Database Structure

A MySQL database is made up of:

  • Tables – Store data in rows and columns.

  • Columns – Represent specific fields (like name, email).

  • Rows – Contain actual records of data.

  • Primary keys – Unique identifiers for each record.

Example:

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  email VARCHAR(100)
);

Here, students is a table with four columns. The id column uniquely identifies each student.

MySQL Database Management

You can manage MySQL databases in several ways:

  • phpMyAdmin: A web-based interface that allows you to manage databases visually.

  • MySQL Command-Line Client: For direct SQL commands.

  • MySQL Workbench: A desktop tool for designing and managing databases.

  • PHP Scripts: You can perform all database operations through PHP code using the mysqli or PDO extension.

Creating a Database Using SQL

To create a new database manually, use:

CREATE DATABASE school_db;

If the database already exists and you want to avoid errors:

CREATE DATABASE IF NOT EXISTS school_db;

To view all databases:

SHOW DATABASES;

To delete a database:

DROP DATABASE school_db;

Creating a Database Using PHP

You can also create a database directly from PHP.

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

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

$conn->close();
?>

How it works:

  1. The script connects to the MySQL server.

  2. The SQL query CREATE DATABASE school_db runs.

  3. PHP checks if the query executed successfully.

  4. The connection closes after the task is complete.

Best Practices When Creating Databases

  1. Use meaningful names – e.g., student_records, inventory_system.

  2. Define proper encoding – Use utf8mb4 for modern character support.

  3. Backup regularly – Prevent data loss.

  4. Use IF NOT EXISTS – Avoid duplicate database errors.

  5. Restrict privileges – Don’t give full access to every user.

Common Errors

Error Reason Fix
Access denied Wrong username or password Check credentials
Can’t connect to MySQL Server not running Start MySQL service
Database already exists Duplicate creation Use IF NOT EXISTS
Permission denied User lacks privileges Grant required privileges

Summary of the Tutorial

  • A database stores structured data for web applications.

  • MySQL is a relational database system used with PHP for most projects.

  • You can manage databases via phpMyAdmin, command-line, or PHP scripts.

  • Use the CREATE DATABASE and DROP DATABASE commands for management.

  • Always handle errors and secure your connections properly.

This foundational knowledge sets the stage for the next step: connecting PHP to MySQL.

Practice Questions

  1. Write a SQL command to create a new database named college_portal.

  2. Write a PHP script using the mysqli extension to create a database named student_system and display a success or error message.

  3. Write a PHP script that connects to the MySQL server and prints “Connected Successfully” if the connection works, otherwise prints the error.

  4. Write a SQL command to show all existing databases on your MySQL server.

  5. Create a database named attendance_db only if it doesn’t already exist.

  6. Write a SQL command to delete the database named test_data.

  7. Write a query to display the name of the database currently being used.

  8. Write a SQL command to create a database named inventory_db with the character set utf8mb4.

  9. Write a PHP script using the PDO extension to create a database named user_accounts. If the connection fails, catch the exception and display “Connection failed: [error message]”.

  10. Write a PHP script that connects to MySQL, creates a database named school_records, and prints “Database Ready” when it’s successfully created.

← 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