PDO Basics

Introduction to PDO

PDO stands for PHP Data Objects. It is a database access layer in PHP that provides a secure and consistent way to connect and work with databases.

PDO allows developers to use the same functions for different database systems such as MySQL, PostgreSQL, SQLite, and Oracle. It is widely used because it improves security, flexibility, and code readability.

Objectives

By the end of this training, you will be able to:

• Understand the purpose of PDO in PHP
• Connect PHP applications with databases using PDO
• Execute SQL queries safely
• Use prepared statements
• Insert, update, delete, and fetch data
• Handle database errors effectively
• Improve database security

What is PDO

PDO is a PHP extension used for database access. It provides a simple and secure method for interacting with databases.

PDO supports multiple database systems and helps prevent SQL injection attacks through prepared statements.

Advantages of PDO

Database Flexibility

PDO supports multiple database systems using the same syntax.

Improved Security

Prepared statements help protect applications from SQL injection.

Better Error Handling

PDO provides exception handling for database errors.

Cleaner Code

PDO makes database operations more organized and readable.

PDO Connection Syntax

To connect PHP with a MySQL database using PDO:

<?php
$host = "localhost";
$dbname = "testdb";
$username = "root";
$password = "";

try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);

    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    echo "Connected Successfully";
}
catch(PDOException $e) {
    echo "Connection Failed: " . $e->getMessage();
}
?>

Understanding the Connection Code

PDO Object

The PDO object creates a connection between PHP and the database.

Try Catch Block

The try catch block handles connection errors safely.

Error Mode

PDO::ERRMODE_EXCEPTION displays database errors as exceptions.

Inserting Data with PDO

<?php
$sql = "INSERT INTO students(name, email)
VALUES('Ali', 'ali@example.com')";

$conn->exec($sql);

echo "Data Inserted Successfully";
?>

Using Prepared Statements

Prepared statements improve security and performance.

<?php
$stmt = $conn->prepare("INSERT INTO students(name, email)
VALUES(:name, :email)");

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

$name = "Ahmed";
$email = "ahmed@example.com";

$stmt->execute();

echo "Record Added";
?>

Fetching Data from Database

<?php
$stmt = $conn->prepare("SELECT * FROM students");
$stmt->execute();

$result = $stmt->fetchAll();

foreach($result as $row) {
    echo $row['name'];
}
?>

Updating Data with PDO

<?php
$sql = "UPDATE students
SET email='newemail@example.com'
WHERE id=1";

$conn->exec($sql);

echo "Record Updated";
?>

Deleting Data with PDO

<?php
$sql = "DELETE FROM students WHERE id=1";

$conn->exec($sql);

echo "Record Deleted";
?>

Fetch Methods in PDO

fetch()

Returns a single row from the result.

fetchAll()

Returns all rows from the result.

fetchColumn()

Returns a single column value.

PDO Error Handling

PDO exceptions help developers identify database issues quickly.

<?php
try {
    $conn = new PDO("mysql:host=localhost;dbname=testdb", "root", "");
}
catch(PDOException $e) {
    echo $e->getMessage();
}
?>

Security Best Practices

• Always use prepared statements
• Validate user input
• Avoid displaying database errors to users
• Use strong database passwords
• Keep PHP and database systems updated

Real World Uses of PDO

PDO is commonly used in:

• Dynamic websites
• Content management systems
• E-commerce platforms
• Login and registration systems
• Student management systems
• Inventory management applications

Career Opportunities

Learning PDO can help you become:

• PHP Developer
• Backend Developer
• Full Stack Developer
• Database Application Developer
• Web Application Developer

Final Presentation

In your final presentation, explain:

• What PDO is
• Advantages of PDO
• Database connection process
• Prepared statements and security
• CRUD operations using PDO
• Error handling techniques
• Real-world applications of PDO