The Project | About Us | Contribute | Donations | License

HOME

In this section, we will learn how to integrate a database into our web application. This involves connecting to the database, performing CRUD (Create, Read, Update, Delete) operations, and ensuring data integrity and security. We will use MySQL as our database management system and PDO (PHP Data Objects) for database interaction.

Objectives

Understand how to connect to a MySQL database using PDO.
Perform CRUD operations.
Handle database errors and exceptions.
Ensure database security.

Connecting to a MySQL Database

Step-by-Step Guide

Create a Database and Table:

CREATE DATABASE webapp_db;
USE webapp_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL
);

Database Configuration: Create a configuration file config.php to store database credentials.

<?php
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_NAME', 'webapp_db');
?>

Connecting to the Database:

<?php
require 'config.php';

try {
    $dsn = 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME;
    $pdo = new PDO($dsn, DB_USER, DB_PASS);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Explanation

DSN (Data Source Name): Specifies the database type, host, and database name.
PDO: PHP Data Objects, a database access layer providing a uniform method of access to multiple databases.
Error Handling: PDO::ATTR_ERRMODE is set to PDO::ERRMODE_EXCEPTION to throw exceptions on errors.

Performing CRUD Operations

Create (Insert Data)

<?php
require 'config.php';

try {
    $pdo = new PDO($dsn, DB_USER, DB_PASS);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "INSERT INTO users (username, email, password) VALUES (:username, :email, :password)";
    $stmt = $pdo->prepare($sql);

    $stmt->execute([
        ':username' => 'john_doe',
        ':email' => '[email protected]',
        ':password' => password_hash('password123', PASSWORD_DEFAULT)
    ]);

    echo "New record created successfully";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Read (Retrieve Data)

<?php
require 'config.php';

try {
    $pdo = new PDO($dsn, DB_USER, DB_PASS);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "SELECT * FROM users";
    $stmt = $pdo->query($sql);

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "ID: " . $row['id'] . " - Name: " . $row['username'] . " - Email: " . $row['email'] . "<br>";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Update (Modify Data)

<?php
require 'config.php';

try {
    $pdo = new PDO($dsn, DB_USER, DB_PASS);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "UPDATE users SET email = :email WHERE username = :username";
    $stmt = $pdo->prepare($sql);

    $stmt->execute([
        ':email' => '[email protected]',
        ':username' => 'john_doe'
    ]);

    echo "Record updated successfully";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Delete (Remove Data)

<?php
require 'config.php';

try {
    $pdo = new PDO($dsn, DB_USER, DB_PASS);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "DELETE FROM users WHERE username = :username";
    $stmt = $pdo->prepare($sql);

    $stmt->execute([
        ':username' => 'john_doe'
    ]);

    echo "Record deleted successfully";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Handling Database Errors and Exceptions

Common Mistakes

Incorrect DSN: Ensure the DSN string is correctly formatted.
Wrong Credentials: Verify the database username and password.
SQL Syntax Errors: Double-check SQL statements for syntax errors.

Error Handling Tips

Use try-catch blocks to handle exceptions.
Log errors to a file instead of displaying them to users for security reasons.

Ensuring Database Security

Prepared Statements

Use prepared statements to prevent SQL injection.

Example:

$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username");
$stmt->execute([':username' => $username]);

Password Hashing

Always hash passwords before storing them in the database.
Use password_hash() and password_verify() functions.

User Input Validation

Validate and sanitize all user inputs before using them in SQL queries.

Conclusion

In this section, we covered how to integrate a MySQL database into a PHP web application. We learned how to connect to the database, perform CRUD operations, handle errors, and ensure security. These skills are essential for building robust and secure web applications. In the next section, we will focus on testing and deploying our web application.

Integrating with a Database

Objectives

Connecting to a MySQL Database

Step-by-Step Guide

Explanation

Performing CRUD Operations

Create (Insert Data)

Read (Retrieve Data)

Update (Modify Data)

Delete (Remove Data)

Handling Database Errors and Exceptions

Common Mistakes

Error Handling Tips

Ensuring Database Security

Prepared Statements

Password Hashing

User Input Validation

Conclusion

PHP Programming Course

Module 1: Introduction to PHP

Module 2: Control Structures

Module 3: Functions

Module 4: Arrays

Module 5: Working with Forms

Module 6: Working with Files

Module 7: Object-Oriented Programming (OOP)

Module 8: Working with Databases

Module 9: Advanced PHP Techniques

Module 10: PHP Frameworks and Best Practices

Module 11: Project: Building a Web Application