Blog

1. Introduction

Welcome to Lesson 9 of the PHP 8 Free Course! Databases are at the heart of modern web applications, providing a structured and persistent storage solution for managing data. Whether you are developing a simple blog or a complex e-commerce platform, understanding how to interact with databases is crucial. This lesson aims to equip you with the foundational knowledge and skills to work with databases using PHP 8, focusing on the MySQL database system. From establishing a connection to a database, performing basic CRUD (Create, Read, Update, Delete) operations, to ensuring secure interactions, this lesson covers a comprehensive overview to get you started. If you need a refresher on previous topics, feel free to navigate through the PHP 8 Free Course index.

2. Introduction to MySQLi and PDO

PHP offers two primary extensions for interacting with MySQL databases: MySQLi (MySQL Improved) and PDO (PHP Data Objects).

• MySQLi: Specific to MySQL databases, providing both procedural and object-oriented interfaces.
• PDO: A database access layer providing a uniform method of access to multiple databases.

Each extension has its own set of advantages and is suited for different scenarios. Your choice between MySQLi and PDO will depend on your project requirements and personal preferences.

3. Connecting to a Database

Connecting to a database is the first step in database interaction. Below is how you can connect to a MySQL database using both MySQLi and PDO.

// Using PDO
$dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8';
$username = 'username';
$password = 'password';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
try {
    $pdo = new PDO($dsn, $username, $password, $options);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

// Explanation:
// 1. Set up a DSN (Data Source Name) with the database host, name, and charset.
// 2. Provide the username and password for database access.
// 3. Define options for the PDO instance.
// 4. Attempt to create a new PDO instance to connect to the database.
// 5. Catch any connection errors and display an error message.

4. Creating Data (INSERT)

Inserting new data into the database is a common task. Here’s how you can do it using a prepared statement in PDO to prevent SQL injection.

$query = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$query->execute(['name' => 'John Doe', 'email' => 'john.doe@example.com']);

// Explanation:
// 1. Prepare an SQL INSERT statement with placeholders for the values to be inserted.
// 2. Execute the prepared statement with the actual values, securely inserting the data.

5. Reading Data (SELECT)

Retrieve data from the database using an SQL SELECT statement.

$query = $pdo->query("SELECT * FROM users");
while ($row = $query->fetch()) {
    echo $row['name'] . ' - ' . $row['email'] . '<br>';
}

6. Updating Data (UPDATE)

Modify existing data in the database using an SQL UPDATE statement.

$query = $pdo->prepare("UPDATE users SET email = :email WHERE name = :name");
$query->execute(['email' => 'new.email@example.com', 'name' => 'John Doe']);

7. Deleting Data (DELETE)

Remove data from the database using an SQL DELETE statement.

$query = $pdo->prepare("DELETE FROM users WHERE name = :name");
$query->execute(['name' => 'John Doe']);

8. Prepared Statements

Use prepared statements to prevent SQL injection, a common web application security vulnerability.

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

9. Closing the Database Connection

It’s a good practice to close the database connection once done.

$pdo = null;

10. Exercises

1. Database Interaction:

• Set up a local database and create a simple PHP script to insert, read, update, and delete data.

1. Secure Database Interaction:

• Modify your script to use prepared statements for secure database interaction.

11. Further Reading and Resources

1. PHP: MySQL Database – Manual
2. PHP: PDO – Manual
3. W3Schools: PHP MySQL Tutorial

Proceed with Lesson 10: Security to delve into ensuring the security of your PHP applications. For a broader range of programming topics, explore the programming section on our blog.