Introduction
PHP Data Objects (PDO) is a database access layer providing a uniform method of access to multiple databases. It does not provide a database abstraction but rather a data-access abstraction. This means that you can use the same functions to issue queries and fetch data regardless of the database you're using.
Key Concepts
- PDO Class: The main class used to interact with databases.
- DSN (Data Source Name): A string that contains the information required to connect to the database.
- Prepared Statements: A feature that allows you to execute the same statement repeatedly with high efficiency and security.
Setting Up PDO
To use PDO, you need to ensure that the PDO extension is enabled in your PHP installation. This is usually enabled by default, but you can check your php.ini file to confirm.
Connecting to a Database
To connect to a MySQL database using PDO, you need to create a new instance of the PDO class. Here’s an example:
<?php
$dsn = 'mysql:host=localhost;dbname=testdb';
$username = 'root';
$password = '';
try {
$pdo = new PDO($dsn, $username, $password);
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>Explanation
- DSN: Specifies the database type, host, and database name.
- Username and Password: Credentials for accessing the database.
- PDO Exception Handling: Using a try-catch block to handle any connection errors.
Performing CRUD Operations
Create (Insert)
<?php $sql = "INSERT INTO users (name, email) VALUES (:name, :email)"; $stmt = $pdo->prepare($sql); $name = 'John Doe'; $email = '[email protected]'; $stmt->bindParam(':name', $name); $stmt->bindParam(':email', $email); if ($stmt->execute()) { echo "New record created successfully"; } else { echo "Error: " . $stmt->errorInfo()[2]; } ?>
Read (Select)
<?php
$sql = "SELECT * FROM users";
$stmt = $pdo->query($sql);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "Name: " . $row['name'] . " - Email: " . $row['email'] . "<br>";
}
?>Update
<?php $sql = "UPDATE users SET email = :email WHERE name = :name"; $stmt = $pdo->prepare($sql); $name = 'John Doe'; $email = '[email protected]'; $stmt->bindParam(':name', $name); $stmt->bindParam(':email', $email); if ($stmt->execute()) { echo "Record updated successfully"; } else { echo "Error: " . $stmt->errorInfo()[2]; } ?>
Delete
<?php
$sql = "DELETE FROM users WHERE name = :name";
$stmt = $pdo->prepare($sql);
$name = 'John Doe';
$stmt->bindParam(':name', $name);
if ($stmt->execute()) {
echo "Record deleted successfully";
} else {
echo "Error: " . $stmt->errorInfo()[2];
}
?>Using Prepared Statements
Prepared statements are a powerful feature of PDO that help prevent SQL injection attacks and improve performance.
Example with Prepared Statements
<?php $sql = "SELECT * FROM users WHERE email = :email"; $stmt = $pdo->prepare($sql); $email = '[email protected]'; $stmt->bindParam(':email', $email); $stmt->execute(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo "Name: " . $row['name'] . " - Email: " . $row['email'] . "<br>"; } ?>
Practical Exercises
Exercise 1: Connecting to a Database
Task: Write a PHP script to connect to a MySQL database named school using PDO. Handle any connection errors gracefully.
Solution:
<?php
$dsn = 'mysql:host=localhost;dbname=school';
$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();
}
?>Exercise 2: Performing CRUD Operations
Task: Create a PHP script to insert a new student record into a students table, then retrieve and display all student records.
Solution:
<?php
// Insert a new student record
$sql = "INSERT INTO students (name, age) VALUES (:name, :age)";
$stmt = $pdo->prepare($sql);
$name = 'Jane Doe';
$age = 20;
$stmt->bindParam(':name', $name);
$stmt->bindParam(':age', $age);
if ($stmt->execute()) {
echo "New student record created successfully<br>";
} else {
echo "Error: " . $stmt->errorInfo()[2];
}
// Retrieve and display all student records
$sql = "SELECT * FROM students";
$stmt = $pdo->query($sql);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "Name: " . $row['name'] . " - Age: " . $row['age'] . "<br>";
}
?>Conclusion
In this section, you learned how to use PDO for database interaction in PHP. You covered the basics of connecting to a database, performing CRUD operations, and using prepared statements. These skills are essential for building secure and efficient database-driven applications. In the next module, you will learn about database security to further enhance your PHP applications.
PHP Programming Course
Module 1: Introduction to PHP
- What is PHP?
- Setting Up the Development Environment
- Your First PHP Script
- PHP Syntax and Variables
- Data Types in PHP
Module 2: Control Structures
Module 3: Functions
- Defining and Calling Functions
- Function Parameters and Return Values
- Variable Scope
- Anonymous Functions and Closures
Module 4: Arrays
Module 5: Working with Forms
Module 6: Working with Files
Module 7: Object-Oriented Programming (OOP)
- Introduction to OOP
- Classes and Objects
- Properties and Methods
- Inheritance
- Interfaces and Abstract Classes
- Traits
Module 8: Working with Databases
- Introduction to Databases
- Connecting to a MySQL Database
- Performing CRUD Operations
- Using PDO for Database Interaction
- Database Security
Module 9: Advanced PHP Techniques
- Error and Exception Handling
- Sessions and Cookies
- Regular Expressions
- Working with JSON and XML
- PHP and Web Services
Module 10: PHP Frameworks and Best Practices
- Introduction to PHP Frameworks
- Getting Started with Laravel
- MVC Architecture
- Best Practices in PHP Development
- Testing and Debugging