Connecting MySQL with PHP

# CHAPTER 26

Connecting MySQL with PHP #

1. Introduction #

2. Learning Objectives #

• Differentiate between the mysqli and PDO extensions.

• Establish a secure database connection using PDO.

• Handle connection errors gracefully using Try-Catch blocks.

• Execute basic queries from PHP.

• Architect parameterized Prepared Statements to prevent SQL Injection.

3. The mysqli vs. PDO Debate #

1. 1. mysqli (MySQL Improved): Extremely fast, but it ONLY works with MySQL. If your company ever switches to PostgreSQL, you have to rewrite your entire codebase.

1. 2. PDO (PHP Data Objects): The modern industry standard. It supports 12 different database engines using the exact same code syntax. It is highly secure and object-oriented. We will use PDO.

4. Establishing the Connection #

<?php
// Database configuration variables
$host = &#039;localhost';
$dbname = &#039;company_db';
$username = &#039;root';
$password = &#039;'; // Default XAMPP has no password

// DSN (Data Source Name) tells PDO where to look
$dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";

try {
    // Attempt the connection!
    $pdo = new PDO($dsn, $username, $password);
    
    // Tell PDO to aggressively throw an exception if a query fails
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "Connected successfully to MySQL!";
} catch (PDOException $e) {
    // If the database is offline, intercept the error cleanly
    die("Database Connection Failed: " . $e->getMessage());
}
?>

5. Executing a Simple Query #

// 1. Write the SQL query
$sql = "SELECT id, first_name, email FROM users LIMIT 3";

// 2. Execute it via PDO
$stmt = $pdo->query($sql);

// 3. Fetch all the results into a PHP Array (PDO::FETCH_ASSOC gives us an Associative Array)
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 4. Loop through the array and print the names to the webpage!
foreach ($users as $user) {
    echo "<p>User: " . htmlspecialchars($user[&#039;first_name']) . "</p>";
}

6. The Danger of User Input #

7. The Solution: Prepared Statements #

// Step 1: PREPARE the structure (Notice the :email placeholder)
$sql = "SELECT first_name, salary FROM employees WHERE email = :email";
$stmt = $pdo->prepare($sql);

// Assume the user typed this into the HTML form
$userInput = "john@company.com";

// Step 2: EXECUTE by passing the actual data separately
// PDO automatically sanitizes the data, completely neutralizing SQL Injection!
$stmt->execute(['email' => $userInput]);

// Step 3: Fetch the single user's data
$user = $stmt->fetch(PDO::FETCH_ASSOC);

if ($user) {
    echo "Found " . $user['first_name'] . " with a salary of $" . $user['salary'];
} else {
    echo "User not found.";
}

8. Common Mistakes #

• Exposing Database Credentials: Beginners often type $password = 'secret123' directly into a public GitHub repository. Hackers scan GitHub 24/7 for exposed passwords. You must store credentials in an external, hidden .env file!

• Not using htmlspecialchars: While Prepared Statements prevent SQL Injection (hacking the database), if you print user-submitted data to the screen without wrapping it in htmlspecialchars(), you expose yourself to XSS (Cross-Site Scripting) attacks, which hacks the user's browser!

9. Best Practices #

• Singleton Pattern: Never write the PDO connection code inside every single PHP file. Write it once inside a database.php file, and simply require 'database.php'; at the top of any script that needs database access.

10. Exercises #

1. 1. What does the acronym PDO stand for in PHP?

1. 2. Rewrite the following insecure PHP query using a safe PDO Prepared Statement with a named placeholder :username:

11. MCQ Quiz with Answers #

12. Interview Questions #

• Q: Detail the mechanical process of how a PDO Prepared Statement neutralizes a SQL Injection attack when a malicious string like ' OR 1=1 -- is passed to it.

• Q: Explain why database connection code in PHP should be wrapped inside a try/catch block. What are the consequences of failing to do so if the database server goes offline?

13. FAQs #

14. Summary #

15. Next Chapter Recommendation #