Working with MySQL Databases

# CHAPTER 7

Working with MySQL Databases #

1. Introduction #

2. Learning Objectives #

• Understand the role of SQL and MySQL in web development.

• Establish a secure database connection using PDO.

• Handle database connection errors gracefully using try...catch.

• Run basic queries to fetch data.

3. Beginner-Friendly Explanation #

4. What is PDO? #

1. 2. Click "New" and create a database named appdb.

1. 3. Create a table named users with three columns:

• id (INT, Primary Key, Auto Increment)

• name (VARCHAR 100)

• email (VARCHAR 150)

6. Step 2: Connecting PHP to MySQL #

db.php

<?php
// Database Credentials for local XAMPP
$host = &#039;localhost';
$dbname = &#039;app_db';
$user = &#039;root';       // Default XAMPP username
$pass = &#039;';           // Default XAMPP password is empty

try {
    // Create the PDO connection string (DSN)
    $dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";
    
    // Instantiate the PDO object
    $pdo = new PDO($dsn, $user, $pass);
    
    // Set PDO to throw an Exception if there is an SQL error
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "Database connected successfully!";
} catch (PDOException $e) {
    // If it fails, catch the error and stop the script
    die("Database connection failed: " . $e->getMessage());
}
?>

7. Step 3: Fetching Data (A Basic Query) #

index.php

<?php
// Include the database connection file
require &#039;db.php';

// Write the SQL note
$sql = "SELECT id, name, email FROM users";

// Ask PDO to run the query
$stmt = $pdo->query($sql);

// Fetch all the rows and format them as an Associative Array
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Loop through the array and display the data in HTML
echo "<h2>Registered Users</h2>";
echo "<ul>";
foreach ($users as $user) {
    echo "<li>" . $user[&#039;name'] . " (" . $user['email'] . ")</li>";
}
echo "</ul>";
?>

8. The Separation of Concerns #

9. Best Practices #

• Keep Passwords Secure: In a production environment, you never hard-code passwords directly into db.php. You use Environment Variables (e.g., a .env file) so that if a hacker steals your code files, they still don't get the database password.

10. Common Mistakes #

• Leaking Errors to the Public: The catch (PDOException $e) block prints the exact reason the database failed. This is great for your local computer, but on a live website, printing $e->getMessage() might reveal your database username to a hacker. On live sites, you just print: "A server error occurred."

11. Exercises #

1. 1. Explain the purpose of a try...catch block when connecting to a database.

12. Coding Challenges #

• Challenge: Use phpMyAdmin to insert 3 fake users into your users table. Then, modify the PHP script to echo only the names of the users, wrapped in HTML <h1> tags.

13. MCQs with Answers #

• Q: Explain the advantages of using PDO over the older mysqli* functions.

• Q: How do you securely handle a failed database connection in PHP so it doesn't crash the entire web application ungracefully?

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #