Master PHP File Handling & Databases in Module 9: From File Operations to Secure MySQL Integration

 Introduction to Working with Files & Databases in PHP

Welcome to Module 9 of our Master PHP from Basics to Advanced series! If you’ve been following along, you’ve mastered PHP basics, arrays, functions, object-oriented programming (OOP), and error handling. Now, it’s time to explore two critical components of modern PHP development: file handling and database integration. These skills are essential for building dynamic web applications, from simple file-based logging systems to complex database-driven platforms like e-commerce sites or content management systems.In this comprehensive guide, we’ll cover:

• Reading & Writing Files: Manipulating files in PHP.
• File Handling Functions: Using PHP’s built-in functions for file operations.
• Introduction to Databases & SQL: Understanding database basics and SQL queries.
• Connecting PHP with MySQL (PDO & MySQLi): Interfacing PHP with MySQL databases.
• Prepared Statements & Security: Preventing SQL injection attacks.
• Transactions & Error Handling: Ensuring data integrity and robust error management.

We’ll use real-world examples—like building a blog post storage system, a user management dashboard, or an e-commerce order system—to make the concepts relatable and engaging. Each section includes detailed explanations, code examples, best practices, pros and cons, and alternatives to ensure you understand every topic thoroughly. Whether you’re storing user data in files or managing a database-driven application, this guide will equip you with the tools to succeed.Let’s get started!

---

1. Reading & Writing Files: Managing File Operations in PHPWhat Is File Handling in PHP?PHP provides powerful functions to read and write files, enabling you to store data, log user activity, or process uploads. File handling is essential for tasks like saving user preferences, generating reports, or managing configuration files.Real-World Example: Blog Post Storage SystemImagine you’re building a simple blog where posts are stored as text files. You need to read existing posts and write new ones, ensuring proper file access and error handling.Code Example: Basic File Reading and Writing

php

<?php
// Writing to a file
$content = "Title: My First Post\nContent: Welcome to my blog!";
$filename = "posts/post1.txt";

try {
    if (file_put_contents($filename, $content) === false) {
        throw new Exception("Failed to write to $filename");
    }
    echo "Post saved successfully.\n";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}

// Reading from a file
try {
    if (!file_exists($filename)) {
        throw new Exception("File $filename does not exist");
    }
    $content = file_get_contents($filename);
    echo "File content:\n$content\n";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>

Explanation:

• file_put_contents() writes the $content string to post1.txt.
• file_get_contents() reads the entire file into a string.
• Exceptions are thrown if file operations fail, ensuring robust error handling.

Advanced Example: Appending User Comments

php

<?php
class CommentManager {
    private $commentFile = "comments.txt";

    public function addComment($username, $comment) {
        $entry = date('Y-m-d H:i:s') . " | $username: $comment\n";
        if (file_put_contents($this->commentFile, $entry, FILE_APPEND) === false) {
            throw new Exception("Failed to save comment");
        }
        return "Comment added successfully.";
    }

    public function getComments() {
        if (!file_exists($this->commentFile)) {
            return "No comments found.";
        }
        return nl2br(file_get_contents($this->commentFile));
    }
}

try {
    $manager = new CommentManager();
    echo $manager->addComment("john_doe", "Great post!") . "\n";
    echo $manager->getComments();
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>

Explanation:

• The CommentManager class appends comments to comments.txt with timestamps.
• FILE_APPEND ensures new comments are added without overwriting existing ones.
• nl2br() converts newlines to HTML line breaks for web display.

Pros:

• Simplicity: File handling is straightforward for small-scale data storage.
• No Dependencies: No need for a database server.
• Flexibility: Store data in various formats (text, JSON, CSV).

Cons:

• Scalability: Files are inefficient for large datasets or concurrent access.
• Security: Improper handling can expose sensitive data.
• Performance: File I/O operations are slower than database queries.

Best Practices:

• Always check if files exist (file_exists()) before reading/writing.
• Use proper file permissions (e.g., chmod 644 for read/write by owner).
• Sanitize user input to prevent directory traversal attacks.

Alternatives:

• Databases: Use MySQL or SQLite for structured data (covered later).
• Caching Systems: Use Redis or Memcached for temporary data storage.

---

2. File Handling Functions: Exploring PHP’s File ToolkitWhat Are File Handling Functions?PHP offers a rich set of file handling functions to perform operations like opening, reading, writing, and closing files. Common functions include fopen(), fread(), fwrite(), fclose(), and more.Real-World Example: Generating a CSV ReportIn an e-commerce system, you might generate a CSV report of recent orders, requiring precise control over file operations.Code Example: Using File Handling Functions

php

<?php
class OrderReport {
    private $filename = "orders.csv";

    public function generateReport($orders) {
        $file = fopen($this->filename, 'w');
        if ($file === false) {
            throw new Exception("Failed to open $this->filename");
        }

        // Write CSV header
        fputcsv($file, ['Order ID', 'Customer', 'Amount']);

        // Write order data
        foreach ($orders as $order) {
            fputcsv($file, [$order['id'], $order['customer'], $order['amount']]);
        }

        fclose($file);
        return "Report generated: $this->filename";
    }

    public function readReport() {
        $file = fopen($this->filename, 'r');
        if ($file === false) {
            throw new Exception("Failed to open $this->filename");
        }

        $content = '';
        while (!feof($file)) {
            $line = fgetcsv($file);
            if ($line !== false) {
                $content .= implode(',', $line) . "\n";
            }
        }
        fclose($file);
        return $content;
    }
}

$orders = [
    ['id' => 1, 'customer' => 'John Doe', 'amount' => 99.99],
    ['id' => 2, 'customer' => 'Jane Smith', 'amount' => 149.50]
];

try {
    $report = new OrderReport();
    echo $report->generateReport($orders) . "\n";
    echo $report->readReport();
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>

Explanation:

• fopen('w') opens the file for writing, creating it if it doesn’t exist.
• fputcsv() writes arrays as CSV rows.
• fopen('r') and fgetcsv() read the CSV file line by line.

Pros:

• Control: Functions like fopen() provide fine-grained control over file operations.
• Versatility: Support various file formats (text, CSV, JSON).
• Portability: Works on any system with file system access.

Cons:

• Complexity: Manual resource management (e.g., closing files) is error-prone.
• Concurrency: No built-in locking for concurrent access.

Best Practices:

• Always close files with fclose() to free resources.
• Use file locking (flock()) for concurrent access.
• Handle errors using try-catch blocks.

Alternatives:

• High-Level Functions: Use file_get_contents() or file_put_contents() for simpler tasks.
• Filesystem Libraries: Use libraries like Flysystem for abstraction.

---

3. Introduction to Databases & SQL: Laying the FoundationWhat Are Databases and SQL?A database is an organized collection of data, typically stored in tables. SQL (Structured Query Language) is used to interact with databases, performing operations like creating, reading, updating, and deleting data (CRUD). MySQL is a popular relational database used with PHP.Real-World Example: User Management DashboardIn a user management dashboard, you store user data (e.g., name, email) in a MySQL database and use SQL to query or update records.Code Example: Basic SQL ConceptsHere’s an example SQL table for users and sample queries:

sql

-- Create a users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert a user
INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');

-- Select all users
SELECT * FROM users;

-- Update a user
UPDATE users SET name = 'John Smith' WHERE id = 1;

-- Delete a user
DELETE FROM users WHERE id = 1;

Explanation:

• The CREATE TABLE statement defines a users table with columns id, name, email, and created_at.
• INSERT, SELECT, UPDATE, and DELETE perform CRUD operations.

Pros:

• Structured Data: Databases organize data efficiently.
• Scalability: Handle large datasets and concurrent access.
• Query Power: SQL enables complex data retrieval and manipulation.

Cons:

• Setup: Requires a database server (e.g., MySQL).
• Learning Curve: SQL syntax can be complex for beginners.

Best Practices:

• Use meaningful table and column names.
• Define appropriate data types and constraints (e.g., NOT NULL, UNIQUE).
• Index frequently queried columns for performance.

Alternatives:

• NoSQL Databases: MongoDB or Redis for unstructured data.
• Flat Files: Store data in files for simple applications (less scalable).

---

4. Connecting PHP with MySQL: PDO & MySQLiWhat Are PDO and MySQLi?PHP provides two primary extensions for MySQL:

• PDO (PHP Data Objects): A database-agnostic interface supporting multiple databases (MySQL, PostgreSQL, SQLite).
• MySQLi: A MySQL-specific extension with procedural and object-oriented interfaces.

Real-World Example: E-Commerce Order SystemIn an e-commerce system, you connect to a MySQL database to store and retrieve order details.Code Example: Connecting with PDO

php

<?php
class OrderDatabase {
    private $pdo;

    public function __construct($host, $dbname, $user, $pass) {
        try {
            $this->pdo = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            echo "Connected to database.\n";
        } catch (PDOException $e) {
            throw new Exception("Connection failed: " . $e->getMessage());
        }
    }

    public function addOrder($customer, $amount) {
        $sql = "INSERT INTO orders (customer, amount) VALUES (:customer, :amount)";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['customer' => $customer, 'amount' => $amount]);
        return "Order added for $customer.";
    }
}

try {
    $db = new OrderDatabase('localhost', 'ecommerce', 'root', 'password');
    echo $db->addOrder('Jane Doe', 199.99) . "\n";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>

Code Example: Connecting with MySQLi

php

<?php
class OrderDatabaseMySQLi {
    private $conn;

    public function __construct($host, $dbname, $user, $pass) {
        $this->conn = new mysqli($host, $user, $pass, $dbname);
        if ($this->conn->connect_error) {
            throw new Exception("Connection failed: " . $this->conn->connect_error);
        }
        echo "Connected to database.\n";
    }

    public function addOrder($customer, $amount) {
        $sql = "INSERT INTO orders (customer, amount) VALUES (?, ?)";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('sd', $customer, $amount);
        $stmt->execute();
        return "Order added for $customer.";
    }

    public function __destruct() {
        $this->conn->close();
    }
}

try {
    $db = new OrderDatabaseMySQLi('localhost', 'ecommerce', 'root', 'password');
    echo $db->addOrder('John Smith', 249.99) . "\n";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>

Explanation:

• PDO: Uses named placeholders (:customer) and supports multiple databases.
• MySQLi: Uses ? placeholders and is MySQL-specific.
• Both examples connect to a database and insert an order.

Pros:

• PDO: Database-agnostic, supports prepared statements, and has consistent error handling.
• MySQLi: Faster for MySQL-specific tasks, supports procedural code.
• Security: Both support prepared statements (covered next).

Cons:

• PDO: Slightly steeper learning curve for beginners.
• MySQLi: Limited to MySQL, less flexible for other databases.

Best Practices:

• Use PDO for portability across database systems.
• Enable exception mode in PDO (PDO::ATTR_ERRMODE).
• Close MySQLi connections explicitly or in the destructor.

Alternatives:

• ORMs: Use Doctrine or Eloquent for object-relational mapping.
• Other Databases: Connect to PostgreSQL, SQLite, or MongoDB with PDO.

---

5. Prepared Statements & Security: Preventing SQL InjectionWhat Are Prepared Statements?Prepared statements separate SQL queries from user input, preventing SQL injection attacks where malicious input manipulates queries. They use placeholders for data, which the database engine safely processes.Real-World Example: User Login SystemIn a login system, prepared statements ensure user input (e.g., username, password) doesn’t compromise the database.Code Example: Prepared Statements with PDO

php

<?php
class UserLogin {
    private $pdo;

    public function __construct($host, $dbname, $user, $pass) {
        $this->pdo = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    public function login($username, $password) {
        $sql = "SELECT * FROM users WHERE username = :username AND password = :password";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['username' => $username, 'password' => $password]);
        $user = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($user) {
            return "Login successful for $username.";
        }
        throw new Exception("Invalid credentials.");
    }
}

try {
    $login = new UserLogin('localhost', 'ecommerce', 'root', 'password');
    echo $login->login('john_doe', 'securepass') . "\n";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>

Explanation:

• The query uses :username and :password placeholders.
• execute() binds the input values safely, preventing SQL injection.

Advanced Example: Preventing SQL Injection with MySQLi

php

<?php
class UserLoginMySQLi {
    private $conn;

    public function __construct($host, $dbname, $user, $pass) {
        $this->conn = new mysqli($host, $user, $pass, $dbname);
        if ($this->conn->connect_error) {
            throw new Exception("Connection failed.");
        }
    }

    public function login($username, $password) {
        $sql = "SELECT * FROM users WHERE username = ? AND password = ?";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param('ss', $username, $password);
        $stmt->execute();
        $result = $stmt->get_result();
        if ($result->num_rows > 0) {
            return "Login successful for $username.";
        }
        throw new Exception("Invalid credentials.");
    }
}

try {
    $login = new UserLoginMySQLi('localhost', 'ecommerce', 'root', 'password');
    echo $login->login("john_doe'; DROP TABLE users; --", 'securepass') . "\n";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>

Explanation:

• The malicious input (john_doe'; DROP TABLE users; --) is safely handled by the prepared statement, preventing SQL injection.

Pros:

• Security: Eliminates SQL injection risks.
• Performance: Prepared statements are cached by the database, improving speed for repeated queries.
• Clarity: Separates query logic from data.

Cons:

• Verbosity: More code than concatenating strings (though insecure).
• Learning Curve: Requires understanding placeholders and binding.

Best Practices:

• Always use prepared statements for user input.
• Validate and sanitize input before binding (e.g., filter_var() for emails).
• Use named placeholders in PDO for readability.

Alternatives:

• Escaping: Use mysqli_real_escape_string(), but less secure than prepared statements.
• ORMs: Use libraries like Doctrine to handle queries safely.

---

6. Transactions & Error Handling: Ensuring Data IntegrityWhat Are Transactions?A transaction is a set of database operations that must succeed or fail together, ensuring data consistency. PHP supports transactions with PDO and MySQLi, combined with error handling to manage failures.Real-World Example: Bank Transfer SystemIn a banking application, transferring money between accounts requires a transaction to ensure both the debit and credit operations succeed or are rolled back.Code Example: Transactions with PDO

php

<?php
class BankAccount {
    private $pdo;

    public function __construct($host, $dbname, $user, $pass) {
        $this->pdo = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    public function transfer($fromAccount, $toAccount, $amount) {
        try {
            $this->pdo->beginTransaction();

            // Debit from source account
            $stmt = $this->pdo->prepare("UPDATE accounts SET balance = balance - :amount WHERE id = :id");
            $stmt->execute(['amount' => $amount, 'id' => $fromAccount]);
            if ($stmt->rowCount() === 0) {
                throw new Exception("Invalid source account.");
            }

            // Credit to destination account
            $stmt = $this->pdo->prepare("UPDATE accounts SET balance = balance + :amount WHERE id = :id");
            $stmt->execute(['amount' => $amount, 'id' => $toAccount]);
            if ($stmt->rowCount() === 0) {
                throw new Exception("Invalid destination account.");
            }

            $this->pdo->commit();
            return "Transfer of $$amount completed.";
        } catch (Exception $e) {
            $this->pdo->rollback();
            throw new Exception("Transfer failed: " . $e->getMessage());
        }
    }
}

try {
    $bank = new BankAccount('localhost', 'bank', 'root', 'password');
    echo $bank->transfer(1, 2, 100) . "\n";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>

Explanation:

• beginTransaction() starts the transaction.
• If any operation fails, rollback() undoes all changes.
• commit() finalizes the transaction if all operations succeed.

Pros:

• Data Integrity: Ensures all operations succeed or none are applied.
• Error Handling: Combines with try-catch for robust failure management.
• Consistency: Maintains database state in critical operations.

Cons:

• Performance: Transactions can be slower due to locking.
• Complexity: Requires careful planning for transaction boundaries.

Best Practices:

• Keep transactions short to minimize locking.
• Use try-catch to handle transaction failures gracefully.
• Log rollback errors for debugging.

Alternatives:

• Manual Rollbacks: Update records individually with manual checks (error-prone).
• ORM Transactions: Use Doctrine or Eloquent for simplified transaction handling.

---

ConclusionCongratulations on completing Module 9: Working with Files & Databases in PHP! You’ve learned how to:

• Read and write files for data storage and processing.
• Use file handling functions for precise control.
• Understand databases and SQL for structured data management.
• Connect PHP to MySQL using PDO and MySQLi.
• Implement prepared statements to prevent SQL injection.
• Manage transactions for data integrity and error handling.