Getting Started with PHP: Working with Databases using PDO
What is PDO?
PDO is a database access layer that provides a uniform method of access to multiple databases. It offers a data-access abstraction layer, meaning you can use the same functions to interact with different database systems (e.g., MySQL, PostgreSQL, SQLite).
Benefits of Using PDO
- Database Agnostic: Switch between different database systems with minimal code changes.
- Prepared Statements: Protect against SQL injection attacks by using prepared statements.
- Error Handling: Built-in error handling mechanisms.
Setting Up PDO
Before you can use PDO, ensure you have the PDO extension installed and enabled in your PHP environment. You can verify this by checking your PHP configuration with phpinfo();.
Connecting to a Database
Here’s how to establish a connection to a MySQL database using PDO:
<?php
$dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8';
$username = 'root';
$password = '';
try {
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>Explanation of the Code
- DSN (Data Source Name): Specifies the database type, host, and database name.
- Error Mode: Setting the error mode to
PDO::ERRMODE_EXCEPTIONallows exceptions to be thrown on errors, which is useful for debugging.
Executing Queries
Using Prepared Statements
Prepared statements are a way to execute SQL queries safely. Here’s an example of inserting data into a table:
<?php
$sql = "INSERT INTO users (username, email) VALUES (:username, :email)";
$stmt = $pdo->prepare($sql);
$username = 'johndoe';
$email = '[email protected]';
$stmt->bindParam(':username', $username);
$stmt->bindParam(':email', $email);
$stmt->execute();
echo "New record created successfully";
?>Explanation of the Code
- Placeholders: Use
:usernameand:emailas placeholders in the SQL statement. - Binding Parameters:
bindParambinds the variables to the placeholders, ensuring safe data handling.
Fetching Data
To retrieve data from the database, you can use the SELECT statement:
<?php
$sql = "SELECT * FROM users";
$stmt = $pdo->query($sql);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "Username: " . $row['username'] . " - Email: " . $row['email'] . "<br>";
}
?>Explanation of the Code
- Fetching Data: The
fetchmethod retrieves the next row from the result set. ThePDO::FETCH_ASSOCfetch style returns an associative array.
Handling Transactions
Transactions allow you to execute a set of operations as a single unit of work. Here’s how to use transactions with PDO:
<?php
try {
$pdo->beginTransaction();
$stmt1 = $pdo->prepare("INSERT INTO users (username, email) VALUES (:username, :email)");
$stmt1->execute([':username' => 'janedoe', ':email' => '[email protected]']);
$stmt2 = $pdo->prepare("INSERT INTO profiles (user_id, bio) VALUES (LAST_INSERT_ID(), :bio)");
$stmt2->execute([':bio' => 'Hello, I am Jane.']);
$pdo->commit();
echo "Transaction completed successfully";
} catch (Exception $e) {
$pdo->rollBack();
echo "Transaction failed: " . $e->getMessage();
}
?>Explanation of the Code
- beginTransaction(): Starts a new transaction.
- commit(): Commits the transaction, making all changes permanent.
- rollBack(): Reverts changes if an error occurs during the transaction.
Error Handling
Using exceptions for error handling is a best practice. You can catch exceptions thrown by PDO and handle them gracefully:
<?php
try {
// Database operations
} catch (PDOException $e) {
echo "Database error: " . $e->getMessage();
} catch (Exception $e) {
echo "General error: " . $e->getMessage();
}
?>Summary
| Feature | PDO | MySQLi |
|---|---|---|
| Database Support | Multiple databases | MySQL only |
| Prepared Statements | Yes | Yes |
| Named Parameters | Yes | No |
| Error Handling | Exception-based | Procedural or OOP |
PDO is a powerful tool for database interaction in PHP. By following best practices such as using prepared statements and handling errors gracefully, you can build secure and efficient applications.