**Course Title:** Modern PHP Development: Best Practices and Advanced Techniques **Section Title:** Working with Databases (MySQL/MariaDB) **Topic:** Introduction to database integration in PHP using PDO (PHP Data Objects) As we progress in our journey of learning Modern PHP Development, we've reached a critical aspect of web development: interacting with databases. In this topic, we'll explore how to integrate PHP with databases using PHP Data Objects (PDO). By the end of this topic, you'll be able to set up a connection to a database, perform basic queries, and understand the benefits of using PDO. ### What is PDO? PDO is a lightweight, consistent interface for accessing databases in PHP. It provides a single API for multiple databases, making it easier to switch between different database systems. PDO supports a wide range of databases, including MySQL, PostgreSQL, Microsoft SQL Server, and many more. ### Benefits of Using PDO 1. **Unified API**: PDO provides a single API for multiple databases, making it easier to switch between different database systems. 2. **Improved Security**: PDO supports prepared statements, which help prevent SQL injection attacks. 3. **Error Handling**: PDO provides a robust error handling system, making it easier to diagnose and fix database-related issues. ### Installing PDO PDO is included in the PHP core starting from PHP 5.1. To check if PDO is enabled, you can use the following code: ```php extension_loaded('pdo'); ``` If PDO is not enabled, you can enable it by adding the following line to your `php.ini` file: ```makefile extension=pdo ``` ### Setting Up a PDO Connection To set up a PDO connection, you'll need to create a new PDO object and pass the connection details as an argument. The connection details typically include the following: * **DSN (Data Source Name)**: A string that defines the database driver, hostname, database name, and port. * **Username**: The username to use for the database connection. * **Password**: The password to use for the database connection. * **Options**: An array of options to customize the connection. Here's an example of setting up a PDO connection: ```php $dsn = 'mysql:host=localhost;dbname=mydatabase'; $username = 'myusername'; $password = 'mypassword'; try { $pdo = new PDO($dsn, $username, $password); echo 'Connected to database successfully!'; } catch (PDOException $e) { echo 'Failed to connect to database: ' . $e->getMessage(); } ``` ### Basic PDO Queries Once you've set up a PDO connection, you can perform basic queries using the `query()` method: ```php $result = $pdo->query('SELECT * FROM mytable'); while ($row = $result->fetch(PDO::FETCH_ASSOC)) { print_r($row); } ``` ### Prepared Statements PDO supports prepared statements, which help prevent SQL injection attacks. To use prepared statements, you'll need to create a prepared statement object using the `prepare()` method: ```php $stmt = $pdo->prepare('SELECT * FROM mytable WHERE id = :id'); $stmt->bindParam(':id', $id); $stmt->execute(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { print_r($row); } ``` ### Why Use PDO? PDO is a powerful tool for interacting with databases in PHP. Its unified API, improved security, and robust error handling system make it an ideal choice for web development projects. ### Exercises and Examples * Create a PDO connection to a database of your choice. * Perform a basic query using the `query()` method. * Use prepared statements to prevent SQL injection attacks. ### Resources * [PDO Documentation](https://www.php.net/manual/en/book.pdo.php) (PHP Manual) * [PDO Tutorial](https://www.w3schools.com/php/php_pdo.asp) (W3Schools) ### Next Topic * **CRUD operations (Create, Read, Update, Delete) using SQL** ### Conclusion In this topic, we've introduced you to PDO and explained how to set up a connection to a database, perform basic queries, and use prepared statements to prevent SQL injection attacks. We encourage you to practice the exercises and examples provided to reinforce your understanding of the material. **Got questions or need help? Leave a comment below.**

extension_loaded('pdo');

extension=pdo

$dsn = 'mysql:host=localhost;dbname=mydatabase';
$username = 'myusername';
$password = 'mypassword';

try {
    $pdo = new PDO($dsn, $username, $password);
    echo 'Connected to database successfully!';
} catch (PDOException $e) {
    echo 'Failed to connect to database: ' . $e->getMessage();
}

$result = $pdo->query('SELECT * FROM mytable');

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    print_r($row);
}

$stmt = $pdo->prepare('SELECT * FROM mytable WHERE id = :id');
$stmt->bindParam(':id', $id);
$stmt->execute();

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    print_r($row);
}