Using PDO in PHP

Introduction to PDO

PDO (PHP Data Objects) is a consistent way to access databases in PHP. It supports multiple databases such as MySQL, PostgreSQL, SQLite, and more. PDO provides a data-access abstraction layer, which means you can use the same functions to issue queries and fetch data regardless of the database you're using.

Connecting to a Database

To connect to a database using PDO, you need to create a new PDO instance. The constructor of the PDO class requires three arguments: the Data Source Name (DSN), the username, and the password.

<?php
$dsn = 'mysql:host=localhost;dbname=testdb';
$username = 'root';
$password = 'password';

try {
    $dbh = new PDO($dsn, $username, $password);
    echo 'Connection successful';
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}
?>
                

Connection successful

Executing Queries

You can execute SQL queries using the exec() method for INSERT, UPDATE, and DELETE statements, or the query() method for SELECT statements.

INSERT Example

<?php
$sql = "INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com')";
$count = $dbh->exec($sql);
echo $count . ' rows inserted';
?>
                

1 rows inserted

SELECT Example

<?php
$sql = 'SELECT * FROM users';
foreach ($dbh->query($sql) as $row) {
    print $row['username'] . "\t";
    print $row['email'] . "\n";
}
?>
                

john_doe    john@example.com

Prepared Statements

Prepared statements are a way to execute the same SQL query repeatedly with different parameters. They help protect against SQL injection attacks.

Using Prepared Statements

<?php
$sql = 'INSERT INTO users (username, email) VALUES (:username, :email)';
$stmt = $dbh->prepare($sql);

$stmt->bindParam(':username', $username);
$stmt->bindParam(':email', $email);

$username = 'jane_doe';
$email = 'jane@example.com';
$stmt->execute();

$username = 'mark_smith';
$email = 'mark@example.com';
$stmt->execute();
?>
                

Error Handling

PDO offers several error handling modes. The default mode is to ignore errors. You can change the error mode using the setAttribute() method.

<?php
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
?>
                

With this mode, an exception will be thrown if an error occurs.

Closing the Connection

To close the connection, simply set the PDO instance to null.

<?php
$dbh = null;
?>