Connecting MySQL with PHP Applications

Course: MySQL Training

Topic: Connecting MySQL with PHP Applications

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

PHP MySQLi Documentation: Official MySQLi Documentation

PHP PDO MySQL Documentation: Official PDO MySQL Documentation

MySQL PHP API Guide: Official MySQL PHP API Documentation

1. Introduction to Connecting MySQL with PHP Applications

PHP and MySQL are widely used together to develop dynamic websites and database-driven web applications. PHP is a server-side programming language that processes user requests, executes business logic, and generates responses. MySQL is a relational database management system that stores and organizes structured data.

Connecting PHP with MySQL allows applications to save user information, display records, update data, delete records, authenticate users, manage products, process orders, and generate reports.

For example, when a user registers on a website, PHP receives the submitted form, validates the information, connects to MySQL, and stores the details in a database table. When the user logs in later, PHP retrieves the account record and verifies the submitted credentials.

1.1 What Does Connecting PHP with MySQL Mean?

Connecting PHP with MySQL means establishing communication between a PHP script and a MySQL database server through a supported database interface. PHP sends SQL statements to MySQL, MySQL processes those statements, and the result is returned to PHP for further processing.

1.2 Why Is This Connection Important?

• It allows websites to display dynamic database content.
• It enables registration, login, and account management.
• It supports Create, Read, Update, and Delete operations.
• It helps manage products, customers, orders, and payments.
• It enables database-driven dashboards and reports.
• It stores information permanently instead of keeping it only in temporary variables.
• It supports content management systems and administrative portals.
• It helps developers build interactive applications backed by persistent data.

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand the architecture of PHP and MySQL applications.
• Identify the software and PHP extensions required for database connectivity.
• Explain the differences between MySQLi and PDO.
• Create a MySQL database and tables for a PHP application.
• Establish and reuse a database connection.
• Execute SQL statements from PHP.
• Insert, retrieve, update, and delete records.
• Use prepared statements to reduce SQL injection risks.
• Handle database errors securely.
• Apply transactions and database security principles.
• Build a basic PHP and MySQL CRUD application.

3. Understanding PHP and MySQL Architecture

A PHP and MySQL application generally follows a client-server architecture. The browser sends an HTTP request to a web server. The web server invokes PHP, and PHP communicates with MySQL when database operations are required.

3.1 Main Components

3.2 Request Processing Workflow

1. The user opens a website or submits a form.
2. The browser sends an HTTP request to the web server.
3. The web server invokes the PHP application.
4. PHP validates the request and prepares the required operation.
5. PHP connects to MySQL using MySQLi or PDO.
6. MySQL executes the SQL statement.
7. PHP processes the result or handles the error.
8. The web server returns an HTTP response to the browser.

3.3 Architecture Diagram

+-------------------------+
|      Web Browser        |
| Forms and Web Pages     |
+------------+------------+
             |
             | HTTP Request
             v
+-------------------------+
|       Web Server        |
| Apache / Nginx          |
+------------+------------+
             |
             v
+-------------------------+
|       PHP Runtime       |
| Application Logic       |
+------------+------------+
             |
             | MySQLi / PDO
             v
+-------------------------+
|       MySQL Server      |
| SQL Query Processing    |
+------------+------------+
             |
             v
+-------------------------+
| Database and Tables     |
| Persistent Application  |
| Data                    |
+-------------------------+

4. Requirements for Connecting PHP with MySQL

Before writing database connection code, install and configure the necessary software and database extensions.

4.1 Required Tools

4.2 Development Environment Options

• XAMPP: A local development package commonly used for PHP and database development. Check the database server included with your installed version.
• WampServer: A Windows environment for Apache, PHP, and database development.
• Laragon: A local development environment that can be configured for PHP and MySQL projects.
• Manual installation: Install PHP, a web server, and MySQL separately.
• Hosted server: Connect the PHP application to a permitted database server endpoint.

4.3 Verify PHP Installation

Open a terminal or command prompt and execute:

php -v

This command displays the PHP version if PHP is installed and available in the system path.

4.4 Verify Database Extensions

Run the following command to list loaded PHP modules:

php -m

Check for mysqli, PDO, and pdo_mysql, depending on the interface you intend to use. The PHP command-line environment and web server can use different configuration files, so verify the extensions in the runtime that serves your application.

4.5 Test PHP Through the Web Server

Create a temporary file named info.php:

<?php
phpinfo();
?>

Open the file through your local web server to inspect the PHP configuration. Delete the file after testing because publicly exposing PHP configuration details may reveal sensitive system information.

5. Understanding MySQLi and PDO

MySQLi and PDO are two widely used options for connecting PHP applications to MySQL. Both support prepared statements and can be used to execute database operations.

5.1 What Is MySQLi?

MySQLi stands for MySQL Improved. It is a PHP extension specifically designed for MySQL database connectivity. It supports procedural and object-oriented programming styles, prepared statements, transactions, and result processing.

Basic example:

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $conn = new mysqli(
        "localhost",
        "app_user",
        "your_password",
        "student_db"
    );

    $conn->set_charset("utf8mb4");

    echo "Connected successfully.";
} catch (mysqli_sql_exception $e) {
    error_log($e->getMessage());
    exit("Unable to connect to the database.");
}
?>

5.2 What Is PDO?

PDO stands for PHP Data Objects. It provides a consistent database access interface, while the PDO MySQL driver handles communication with MySQL. PDO supports prepared statements and transactions and can make switching between supported database systems easier.

Basic example:

<?php
$host = "localhost";
$dbname = "student_db";
$username = "app_user";
$password = "your_password";

$dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";

try {
    $pdo = new PDO($dsn, $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false
    ]);

    echo "Connected successfully.";
} catch (PDOException $e) {
    error_log($e->getMessage());
    exit("Unable to connect to the database.");
}
?>

5.3 MySQLi vs PDO

Both options are suitable for modern PHP applications. Select the interface that fits your project and use it consistently. Avoid legacy mysql_* functions because the old MySQL extension has been removed from modern PHP versions.

6. Creating a MySQL Database

Before connecting PHP to MySQL, create a database and the tables required by your application.

6.1 Create the Database

CREATE DATABASE student_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

The student_db database will store student records for the examples in this lesson.

6.2 Create a Students Table

USE student_db;

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    course_name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

6.3 Understand the Table Structure

7. Connecting PHP to MySQL Using MySQLi

7.1 Basic Connection

The object-oriented MySQLi connection accepts the database host, username, password, and database name.

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$host = "localhost";
$username = "app_user";
$password = "your_password";
$database = "student_db";

try {
    $conn = new mysqli(
        $host,
        $username,
        $password,
        $database
    );

    $conn->set_charset("utf8mb4");

    echo "MySQL connection established.";
} catch (mysqli_sql_exception $e) {
    error_log($e->getMessage());
    http_response_code(500);
    exit("A database connection error occurred.");
}
?>

7.2 Explanation of the Code

• $host identifies the database server.
• $username identifies the database account.
• $password contains the account password.
• $database specifies the database to select.
• mysqli_report() configures MySQLi to throw exceptions for reported errors.
• new mysqli() creates the database connection.
• set_charset("utf8mb4") configures the connection character set.
• try and catch handle connection failures.

Replace the example credentials with your own secure configuration. For production applications, avoid hardcoding secrets in source code.

7.3 Understanding Connection Parameters

7.4 Connecting Through a Custom Port

<?php
$conn = new mysqli(
    "127.0.0.1",
    "app_user",
    "your_password",
    "student_db",
    3307
);

$conn->set_charset("utf8mb4");
?>

This example assumes that MySQL listens on port 3307. The hostname localhost may use a local socket on some systems, while 127.0.0.1 explicitly requests a TCP/IP connection.

8. Connecting PHP to MySQL Using PDO

8.1 PDO Connection Example

<?php
$host = "localhost";
$database = "student_db";
$username = "app_user";
$password = "your_password";

$dsn = "mysql:host=$host;dbname=$database;charset=utf8mb4";

try {
    $pdo = new PDO($dsn, $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false
    ]);

    echo "Connected to MySQL successfully.";
} catch (PDOException $e) {
    error_log($e->getMessage());
    http_response_code(500);
    exit("A database connection error occurred.");
}
?>

8.2 Explanation of the PDO Code

• $dsn specifies the MySQL driver, host, database name, and character set.
• new PDO() establishes the connection.
• PDO::ATTR_ERRMODE configures exception-based error handling.
• PDO::ATTR_DEFAULT_FETCH_MODE makes associative arrays the default result format.
• PDO::ATTR_EMULATE_PREPARES set to false requests native prepared statements where supported.
• catch handles connection exceptions without exposing sensitive details to users.

8.3 Create a Reusable PDO Connection File

In a larger application, store connection setup in a separate file. Create a file named db.php:

<?php
$host = getenv("DB_HOST") ?: "localhost";
$dbname = getenv("DB_NAME") ?: "student_db";
$username = getenv("DB_USER");
$password = getenv("DB_PASSWORD");

if (!$username || !$password) {
    error_log("Database credentials are not configured.");
    http_response_code(500);
    exit("Application configuration error.");
}

$dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";

try {
    $pdo = new PDO($dsn, $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false
    ]);
} catch (PDOException $e) {
    error_log($e->getMessage());
    http_response_code(500);
    exit("A database error occurred.");
}

Configure the environment variables in the web server, PHP runtime, container, or hosting platform. Environment variables set in an interactive terminal are not necessarily available to PHP-FPM or Apache.

8.4 Reuse the Connection File

<?php
require_once __DIR__ . "/db.php";

$stmt = $pdo->query("SELECT DATABASE() AS database_name");
$row = $stmt->fetch();

echo htmlspecialchars(
    $row["database_name"],
    ENT_QUOTES,
    "UTF-8"
);
?>

The require_once statement includes the connection file only once in the current execution context. Keep configuration files and secrets outside publicly accessible directories where practical.

9. Executing SQL Queries from PHP

After connecting to MySQL, PHP can execute SQL statements to read or modify data. Use a query method for fixed SQL that does not contain untrusted values, and prepared statements for values supplied by users.

9.1 Execute a SELECT Query Using MySQLi

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$conn = new mysqli(
    "localhost",
    "app_user",
    "your_password",
    "student_db"
);
$conn->set_charset("utf8mb4");

$result = $conn->query(
    "SELECT student_id, full_name, email, course_name
     FROM students
     ORDER BY student_id DESC"
);

while ($row = $result->fetch_assoc()) {
    echo htmlspecialchars(
        $row["full_name"],
        ENT_QUOTES,
        "UTF-8"
    ) . " - ";

    echo htmlspecialchars(
        $row["email"],
        ENT_QUOTES,
        "UTF-8"
    ) . "<br>";
}

$conn->close();
?>

The query() method executes the SQL statement, while fetch_assoc() retrieves each row as an associative array. The htmlspecialchars() function helps prevent database content from being interpreted as HTML or JavaScript when displayed in an HTML context.

9.2 Execute a SELECT Query Using PDO

<?php
require_once __DIR__ . "/db.php";

$stmt = $pdo->query(
    "SELECT student_id, full_name, email, course_name
     FROM students
     ORDER BY student_id DESC"
);

foreach ($stmt as $row) {
    echo htmlspecialchars(
        $row["full_name"],
        ENT_QUOTES,
        "UTF-8"
    ) . " - ";

    echo htmlspecialchars(
        $row["email"],
        ENT_QUOTES,
        "UTF-8"
    ) . "<br>";
}
?>

10. Inserting Data into MySQL from PHP

The INSERT statement adds new records to a database table. In a web application, it is commonly used for registration forms, product entry, contact forms, and order creation.

10.1 Create an HTML Registration Form

<form action="insert_student.php" method="post">
    <label for="full_name">Full Name:</label>
    <input type="text" id="full_name" name="full_name" required>

    <label for="email">Email:</label>
    <input type="email" id="email" name="email" required>

    <label for="course_name">Course Name:</label>
    <input type="text" id="course_name" name="course_name" required>

    <button type="submit">Register Student</button>
</form>

10.2 Insert Data Using PDO Prepared Statements

<?php
require_once __DIR__ . "/db.php";

if ($_SERVER["REQUEST_METHOD"] !== "POST") {
    http_response_code(405);
    exit("Method not allowed.");
}

$fullName = trim($_POST["full_name"] ?? "");
$email = trim($_POST["email"] ?? "");
$courseName = trim($_POST["course_name"] ?? "");

if (
    $fullName === "" ||
    $courseName === "" ||
    !filter_var($email, FILTER_VALIDATE_EMAIL)
) {
    http_response_code(400);
    exit("Please provide valid form details.");
}

$sql = "INSERT INTO students (full_name, email, course_name)
        VALUES (:full_name, :email, :course_name)";

$stmt = $pdo->prepare($sql);
$stmt->execute([
    ":full_name" => $fullName,
    ":email" => $email,
    ":course_name" => $courseName
]);

echo "Student registered successfully.";
?>

The prepared statement separates the SQL structure from the supplied values. It reduces the risk of SQL injection and safely handles values containing quotation marks. In a production application, also apply length limits, authorization checks, CSRF protection, and appropriate handling for duplicate email addresses.

10.3 Insert Data Using MySQLi Prepared Statements

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
require_once __DIR__ . "/mysqli_db.php";

$fullName = trim($_POST["full_name"] ?? "");
$email = trim($_POST["email"] ?? "");
$courseName = trim($_POST["course_name"] ?? "");

if (
    $fullName === "" ||
    $courseName === "" ||
    !filter_var($email, FILTER_VALIDATE_EMAIL)
) {
    http_response_code(400);
    exit("Invalid form data.");
}

$stmt = $conn->prepare(
    "INSERT INTO students (full_name, email, course_name)
     VALUES (?, ?, ?)"
);

$stmt->bind_param("sss", $fullName, $email, $courseName);
$stmt->execute();

echo "Student registered successfully.";
?>

Here, mysqli_db.php represents a reusable MySQLi connection file. The string sss indicates that the three bound parameters are strings.

11. Retrieving Data from MySQL Using PHP

SELECT statements retrieve information from a database. PHP can display student records, products, employee details, orders, and other stored information.

11.1 Retrieve All Students

<?php
require_once __DIR__ . "/db.php";

$sql = "SELECT student_id, full_name, email, course_name, created_at
        FROM students
        ORDER BY student_id DESC";

$stmt = $pdo->query($sql);
$students = $stmt->fetchAll();

foreach ($students as $student) {
    echo "<h3>" .
        htmlspecialchars($student["full_name"], ENT_QUOTES, "UTF-8") .
        "</h3>";

    echo "<p>Email: " .
        htmlspecialchars($student["email"], ENT_QUOTES, "UTF-8") .
        "</p>";

    echo "<p>Course: " .
        htmlspecialchars($student["course_name"], ENT_QUOTES, "UTF-8") .
        "</p>";
}
?>

11.2 Retrieve One Student by ID

<?php
require_once __DIR__ . "/db.php";

$studentId = filter_input(INPUT_GET, "id", FILTER_VALIDATE_INT);

if (!$studentId || $studentId < 1) {
    http_response_code(400);
    exit("Invalid student ID.");
}

$stmt = $pdo->prepare(
    "SELECT student_id, full_name, email, course_name
     FROM students
     WHERE student_id = :id"
);

$stmt->execute([":id" => $studentId]);
$student = $stmt->fetch();

if (!$student) {
    http_response_code(404);
    exit("Student not found.");
}

echo htmlspecialchars($student["full_name"], ENT_QUOTES, "UTF-8");
?>

A prepared statement treats the supplied ID as a value rather than SQL code. The application should also verify that the user is authorized to view the requested record.

12. Updating Records from PHP

UPDATE statements modify existing rows. For example, an administrator may update a student's email address or course name.

12.1 Update a Student Record Using PDO

<?php
require_once __DIR__ . "/db.php";

$studentId = filter_input(INPUT_POST, "student_id", FILTER_VALIDATE_INT);
$courseName = trim($_POST["course_name"] ?? "");

if (!$studentId || $studentId < 1 || $courseName === "") {
    http_response_code(400);
    exit("Invalid update information.");
}

$sql = "UPDATE students
        SET course_name = :course_name
        WHERE student_id = :student_id";

$stmt = $pdo->prepare($sql);
$stmt->execute([
    ":course_name" => $courseName,
    ":student_id" => $studentId
]);

echo "Update operation completed.";
?>

A successful execution does not necessarily mean a row changed. If needed, inspect the affected-row count and check whether the record exists to distinguish an unchanged record from a missing record.

12.2 Importance of the WHERE Clause

The WHERE clause specifies which rows should be updated. Without it, the statement can modify every row in the table.

Example of a potentially dangerous statement:

UPDATE students
SET course_name = 'PHP Training';

This query changes the course name for every student. Always check the conditions before executing data modification statements.

13. Deleting Records from PHP

DELETE statements remove records from a database table. Deletion should be protected by validation, authorization, and safeguards against accidental requests.

13.1 Delete a Student Using PDO

<?php
require_once __DIR__ . "/db.php";

$studentId = filter_input(INPUT_POST, "student_id", FILTER_VALIDATE_INT);

if (!$studentId || $studentId < 1) {
    http_response_code(400);
    exit("Invalid student ID.");
}

$stmt = $pdo->prepare(
    "DELETE FROM students WHERE student_id = :student_id"
);

$stmt->execute([":student_id" => $studentId]);

if ($stmt->rowCount() > 0) {
    echo "Student deleted successfully.";
} else {
    echo "No matching student was found.";
}
?>

For production applications, use POST or another suitable state-changing HTTP method, verify permissions, and include CSRF protection. Do not expose destructive operations through unprotected GET links.

14. Understanding Prepared Statements

A prepared statement defines an SQL statement with placeholders and supplies the actual values separately. This is a key security practice when connecting PHP applications to MySQL.

14.1 What Is SQL Injection?

SQL injection occurs when untrusted input changes the intended structure or meaning of an SQL statement. If an application builds SQL queries unsafely, an attacker may be able to access, change, or delete data beyond the intended operation.

14.2 Unsafe Query Example

<?php
$email = $_POST["email"] ?? "";

$sql = "SELECT * FROM students WHERE email = '$email'";
$result = $conn->query($sql);
?>

This example inserts user-controlled input directly into SQL and should not be used in production.

14.3 Safe PDO Prepared Statement

<?php
$email = trim($_POST["email"] ?? "");

$stmt = $pdo->prepare(
    "SELECT student_id, full_name, email
     FROM students
     WHERE email = :email"
);

$stmt->execute([":email" => $email]);
$student = $stmt->fetch();
?>

14.4 Benefits of Prepared Statements

• Separate SQL instructions from user-supplied values.
• Reduce the risk of SQL injection.
• Handle quotation marks and special characters safely as values.
• Make repeated execution of the same query easier to manage.
• Improve the clarity and maintainability of database code.

Placeholders are for values, not table names, column names, or SQL keywords. If a query needs a dynamic column or sort direction, validate the selected option against a fixed allowlist before including it in the SQL statement.

15. Handling Database Errors in PHP

Database errors can occur because of incorrect credentials, unavailable servers, missing tables, invalid SQL, constraint violations, or insufficient permissions.

15.1 Common Database Errors

15.2 Secure Error Handling with PDO

<?php
try {
    $pdo = new PDO(
        "mysql:host=localhost;dbname=student_db;charset=utf8mb4",
        "app_user",
        "your_password",
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        ]
    );

    $stmt = $pdo->query("SELECT COUNT(*) FROM students");
    $count = $stmt->fetchColumn();

    echo "Student records: " . (int) $count;
} catch (PDOException $e) {
    error_log($e->getMessage());
    http_response_code(500);
    echo "A database operation could not be completed.";
}
?>

Detailed exception messages should be recorded in protected logs rather than displayed to ordinary users. Logs should also be secured because they can contain internal paths, query details, or other sensitive information.

16. Creating a Secure MySQL User for PHP

Using the MySQL root account for routine application operations is not recommended. Create a dedicated account with only the privileges the application requires.

16.1 Create an Application User

CREATE USER 'app_user'@'localhost'
IDENTIFIED BY 'Replace_With_A_Strong_Unique_Password';

GRANT SELECT, INSERT, UPDATE, DELETE
ON student_db.*
TO 'app_user'@'localhost';

Replace the example password with a strong unique secret and manage it securely. Execute account administration statements using an authorized administrator account. If the application does not need every listed permission, grant fewer privileges. Database schema changes can be handled through a separate migration or administrative account.

16.2 Why Is Least Privilege Important?

• It limits the impact of a compromised application.
• It prevents access to unrelated databases.
• It reduces the risk of accidental destructive operations.
• It supports access auditing and security management.

17. Managing Database Connection Files

Separating connection configuration from application logic improves maintainability. A reusable connection file avoids repeating connection code across multiple pages.

17.1 Suggested Project Structure

php-mysql-app/
├── config/
│   └── database.php
├── public/
│   ├── index.php
│   ├── add_student.php
│   ├── edit_student.php
│   └── delete_student.php
├── src/
│   └── StudentRepository.php
└── logs/
    └── application.log

This is an illustrative project structure. Keep secrets and logs outside the public document root. Only files intended to be served to browsers should be accessible from the public directory.

17.2 Store Credentials Securely

Instead of hardcoding credentials in PHP files, production applications can load configuration from environment variables or an approved secrets manager.

<?php
$host = getenv("DB_HOST") ?: "localhost";
$dbname = getenv("DB_NAME") ?: "student_db";
$username = getenv("DB_USER");
$password = getenv("DB_PASSWORD");

if (!$username || !$password) {
    error_log("Database credentials are not configured.");
    http_response_code(500);
    exit("Application configuration error.");
}

$dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";

try {
    $pdo = new PDO($dsn, $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false
    ]);
} catch (PDOException $e) {
    error_log($e->getMessage());
    http_response_code(500);
    exit("Database connection failed.");
}
?>

Configure these variables in the appropriate web server, PHP runtime, container, or deployment platform. Never commit real passwords or secrets to a public source repository.

18. Using Transactions in PHP and MySQL

A transaction groups related database operations into a unit of work. It is useful when several changes must succeed together or be rolled back if an error occurs.

18.1 Transaction Example Using PDO

<?php
require_once __DIR__ . "/db.php";

try {
    $pdo->beginTransaction();

    $stmt = $pdo->prepare(
        "INSERT INTO students (full_name, email, course_name)
         VALUES (:name, :email, :course)"
    );

    $stmt->execute([
        ":name" => "Riya Sharma",
        ":email" => "[email protected]",
        ":course" => "MySQL Training"
    ]);

    // Perform other related database operations here.

    $pdo->commit();
    echo "Transaction completed successfully.";
} catch (Throwable $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }

    error_log($e->getMessage());
    echo "The operation could not be completed.";
}
?>

18.2 ACID Properties

• Atomicity: Related operations are committed together or rolled back.
• Consistency: Database constraints and application rules should remain satisfied.
• Isolation: Concurrent transactions are managed according to the selected isolation level.
• Durability: Committed changes are preserved according to the database's durability guarantees and configuration.

Transactions require a transactional storage engine such as InnoDB for the relevant operations. Some statements, especially certain DDL operations, may cause implicit commits and should not be casually mixed into application transactions.

19. Improving PHP and MySQL Performance

• Select only the columns required by the application.
• Use indexes that match common filtering, sorting, and joining operations.
• Use prepared statements for user-supplied values.
• Paginate large result sets instead of retrieving every row.
• Avoid unnecessary queries inside loops.
• Use transactions for related data changes.
• Release database resources when appropriate, especially in long-running processes.
• Measure query execution time and review slow-query logs when needed.
• Use persistent connections only when justified by the deployment environment and application behavior.
• Keep PHP, its database extensions, and MySQL versions maintained.

19.1 Pagination Example Using PDO

<?php
require_once __DIR__ . "/db.php";

$page = filter_input(INPUT_GET, "page", FILTER_VALIDATE_INT);
$page = max(1, $page ?: 1);

$perPage = 10;
$offset = ($page - 1) * $perPage;

$stmt = $pdo->prepare(
    "SELECT student_id, full_name, email
     FROM students
     ORDER BY student_id DESC
     LIMIT :limit OFFSET :offset"
);

$stmt->bindValue(":limit", $perPage, PDO::PARAM_INT);
$stmt->bindValue(":offset", $offset, PDO::PARAM_INT);
$stmt->execute();

$students = $stmt->fetchAll();
?>

Pagination reduces the amount of data retrieved and rendered at one time. For very large datasets, consider cursor-based pagination and suitable indexes. Also validate page numbers and apply sensible limits to prevent excessively large offsets.

20. Common Connection Problems and Troubleshooting

20.1 MySQL Server Is Not Running

Symptoms: PHP reports a connection failure or connection refusal.

Solution: Confirm that the MySQL service is running and listening on the expected host and port. Review the database server logs if the service fails to start.

20.2 Incorrect Host or Port

Symptoms: The PHP application cannot reach the database.

Solution: Verify the host, port, socket configuration, firewall rules, and whether the database accepts connections from the application's environment.

20.3 Access Denied

Symptoms: MySQL rejects the username, password, or connection origin.

Solution: Check the credentials and account host permissions. A MySQL account allowed from localhost may not be allowed from a remote application server.

20.4 Missing PHP Extension

Symptoms: PHP reports that the MySQLi class or PDO MySQL driver is unavailable.

Solution: Install or enable the relevant PHP extension, restart the applicable service if necessary, and verify the configuration used by the web server.

20.5 Unknown Database or Table

Symptoms: The connection succeeds, but queries fail.

Solution: Check database and table names, confirm that schema migrations have run, and verify that PHP is connecting to the intended MySQL instance.

20.6 Character Encoding Problems

Symptoms: Non-English names or other Unicode characters are stored or displayed incorrectly.

Solution: Use a suitable character set such as utf8mb4 for the database objects and connection. Ensure that the HTML response uses the appropriate character encoding.

20.7 Troubleshooting Checklist

• Confirm PHP and MySQL are running.
• Check the host, port, username, password, and database name.
• Verify that the required PHP extension is loaded.
• Test the credentials with an authorized database client.
• Review PHP and MySQL logs.
• Check SQL syntax and table structure.
• Confirm the account has the required permissions.
• Check network access for remote database connections.
• Keep detailed errors in protected logs rather than showing them publicly.

21. Practical Project: Student Management System

21.1 Project Objective

Build a student management application using PHP and MySQL. The application should allow users with appropriate permissions to create, view, update, and delete student records.

21.2 Database Structure

CREATE DATABASE student_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE student_db;

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    course_name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

21.3 Main Features

• Reusable database connection through PDO.
• Student registration form.
• Prepared statements for inserting records.
• Student listing page.
• Student search by ID or email.
• Form for updating student information.
• Protected delete operation.
• Server-side input validation.
• HTML output escaping.
• Secure database error logging.

21.4 Example Student Listing Page

<?php
require_once __DIR__ . "/db.php";

$stmt = $pdo->query(
    "SELECT student_id, full_name, email, course_name
     FROM students
     ORDER BY student_id DESC"
);

$students = $stmt->fetchAll();
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Student Management</title>
</head>
<body>
    <h1>Student Management System</h1>
    <table border="1" cellpadding="8" cellspacing="0">
        <thead>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Email</th>
                <th>Course</th>
            </tr>
        </thead>
        <tbody>
        <?php foreach ($students as $student): ?>
            <tr>
                <td><?= (int) $student["student_id"] ?></td>
                <td><?= htmlspecialchars($student["full_name"], ENT_QUOTES, "UTF-8") ?></td>
                <td><?= htmlspecialchars($student["email"], ENT_QUOTES, "UTF-8") ?></td>
                <td><?= htmlspecialchars($student["course_name"], ENT_QUOTES, "UTF-8") ?></td>
            </tr>
        <?php endforeach; ?>
        </tbody>
    </table>
</body>
</html>

21.5 Project Testing Steps

1. Create the database and students table in MySQL.
2. Create a dedicated application database user.
3. Configure the reusable PHP connection file.
4. Open the application through the configured web server.
5. Register a sample student.
6. Verify that the student appears on the listing page.
7. Test updating and deleting records.
8. Try invalid input and duplicate email addresses.
9. Verify that errors are logged without exposing credentials.
10. Test the application using the PHP version and configuration intended for deployment.

22. Security Best Practices

• Use MySQLi or PDO instead of removed legacy MySQL APIs.
• Use prepared statements for user-supplied SQL values.
• Validate data on the server, even if browser-side validation is also used.
• Escape untrusted output according to its context, such as HTML.
• Keep database credentials out of public source repositories.
• Use a dedicated database account with least-privilege permissions.
• Use encrypted database connections when traffic crosses untrusted networks.
• Log exceptions securely and avoid exposing internal error details.
• Use CSRF protection for state-changing form submissions.
• Require authentication and authorization for sensitive operations.
• Use transactions for related database changes.
• Back up databases and test restoration procedures.
• Keep PHP and MySQL software maintained and updated.
• Separate development, testing, staging, and production environments.
• Monitor database availability, query performance, and application errors.

23. Exercises and Practice Questions

Exercise 1: Establish a Connection

Create a PHP script that connects to MySQL using PDO. Display a success message when the connection is established and a generic error message when it fails.

Exercise 2: Insert Student Records

Create an HTML form that accepts a student's name, email, and course. Validate the information and insert it into the students table using a prepared statement.

Exercise 3: Retrieve Records

Create a PHP page that displays all student records in an HTML table. Escape text values before displaying them in the browser.

Exercise 4: Update a Record

Create a form that updates a student's course name. Use a prepared statement and verify that the current user is authorized to make the change.

Exercise 5: Delete a Record

Implement a deletion feature using POST, input validation, authorization, and CSRF protection.

Exercise 6: Troubleshoot Connection Errors

In a local test environment, use an incorrect database name or password. Identify the issue through protected logs and correct the configuration.

Exercise 7: Compare MySQLi and PDO

Write equivalent connection and SELECT examples using MySQLi and PDO. Compare their connection code, error handling, and prepared statement syntax.

24. Interview Questions and Answers

Q1. How does PHP connect to MySQL?

Answer: PHP uses a database interface such as MySQLi or PDO with the PDO MySQL driver to communicate with the MySQL server.

Q2. What is MySQLi?

Answer: MySQLi is the MySQL Improved PHP extension. It provides procedural and object-oriented interfaces for database operations.

Q3. What is PDO?

Answer: PDO stands for PHP Data Objects. It provides a common database access interface for supported database systems through their appropriate drivers.

Q4. What is the difference between MySQLi and PDO?

Answer: MySQLi is designed specifically for MySQL, whereas PDO provides a consistent interface for multiple supported database systems. Both support prepared statements and transactions.

Q5. What is a database connection?

Answer: A database connection enables an application to communicate with a database server, execute SQL statements, and receive results.

Q6. What is a prepared statement?

Answer: A prepared statement uses placeholders for values supplied separately when executing SQL, helping reduce SQL injection risks.

Q7. What is SQL injection?

Answer: SQL injection is a vulnerability in which untrusted input alters the intended SQL statement. Prepared statements and careful query construction help prevent it.

Q8. Why should credentials not be hardcoded?

Answer: Hardcoded credentials can leak through source control, code sharing, or accidental disclosure. Environment variables or a secrets manager provide better ways to manage sensitive configuration.

Q9. What does set_charset("utf8mb4") do?

Answer: It configures the MySQLi connection character set so that the connection can handle Unicode text appropriately.

Q10. Why is the WHERE clause important?

Answer: It specifies which rows should be selected, updated, or deleted. Without it, a modification query may affect every row.

Q11. How should database exceptions be handled?

Answer: Catch exceptions, log useful diagnostic details in a protected location, and return a generic error message to users.

Q12. Why use a dedicated database user?

Answer: A dedicated account can be restricted to the required database and operations, reducing the potential impact of a compromised application.

Q13. What is a transaction?

Answer: A transaction groups database operations so that they can be committed together or rolled back if an error occurs, provided the relevant operations support transactions.

Q14. How can PHP and MySQL performance be improved?

Answer: Use appropriate indexes, retrieve only necessary columns, paginate large results, avoid repeated queries, and measure query performance.

Q15. Why should database output be escaped?

Answer: Escaping output for its intended context, such as HTML, helps prevent untrusted content from being interpreted as executable markup or script.

25. Quick Revision Checklist

• Understand PHP, MySQL, web servers, and database drivers.
• Know the differences between MySQLi and PDO.
• Verify PHP and the required database extensions are installed.
• Create the MySQL database and tables before connecting.
• Use reusable connection configuration.
• Execute SELECT, INSERT, UPDATE, and DELETE statements.
• Use prepared statements for user-supplied values.
• Validate inputs and escape HTML output.
• Handle exceptions without exposing internal details.
• Use least-privilege database accounts.
• Protect credentials and use encrypted connections when appropriate.
• Use transactions for related data changes.
• Monitor database errors and application performance.

26. Conclusion

Connecting MySQL with PHP applications is a fundamental skill in server-side web development. It enables PHP scripts to create, retrieve, update, and delete persistent data for websites such as student management systems, e-commerce platforms, dashboards, and content management systems.

MySQLi and PDO are both suitable choices for modern PHP applications. Secure and reliable implementations combine prepared statements, input validation, output escaping, appropriate permissions, protected error logging, transactions, and performance monitoring.

Continue Learning: Visit the MySQL Training Course to explore more database topics, or register for a course demo to learn more about the training program.