Introduction: In web development, performing CRUD (Create, Read, Update, Delete) operations is a fundamental aspect when working with databases. This blog post will guide you through a practical demonstration of these operations using PHP, PDO (PHP Data Objects), and prepared statements. We’ll use a student dataset as an example to illustrate how to interact with a database efficiently.
Prerequisites:
Before we dive into the demonstration, ensure that you have:
- A web server (like Apache or Nginx) with PHP installed.
- A database server (MySQL, PostgreSQL, etc.) with a database created.
Setting Up the Database:
For this demonstration, let’s consider a simple “students” table with the following structure:
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT,
grade VARCHAR(10)
);Connecting to the Database:
Firstly, establish a connection to the database using PDO:
<?php
$host = 'your_database_host';
$dbname = 'your_database_name';
$username = 'your_username';
$password = 'your_password';
try {
$pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch (PDOException $e) {
die("Connection failed: " . $e->getMessage());
}
?>
Creating (Inserting) Data:
To add a new student to the database:
<?php
$name = 'John Doe';
$age = 20;
$grade = 'A';
try {
$stmt = $pdo->prepare("INSERT INTO students (name, age, grade) VALUES (?, ?, ?)");
$stmt->execute([$name, $age, $grade]);
echo "New student added successfully";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
?>
Reading Data:
Retrieve and display the student data:
<?php
try {
$stmt = $pdo->prepare("SELECT * FROM students");
$stmt->execute();
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($students as $student) {
echo "ID: {$student['id']}, Name: {$student['name']}, Age: {$student['age']}, Grade: {$student['grade']}<br>";
}
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
?>
Updating Data:
Modify a student’s information:
<?php
$newAge = 21;
$idToUpdate = 1;
try {
$stmt = $pdo->prepare("UPDATE students SET age = ? WHERE id = ?");
$stmt->execute([$newAge, $idToUpdate]);
echo "Student information updated successfully";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
?>
Deleting Data:
Remove a student from the database:
<?php
$idToDelete = 2;
try {
$stmt = $pdo->prepare("DELETE FROM students WHERE id = ?");
$stmt->execute([$idToDelete]);
echo "Student deleted successfully";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
?>
Conclusion: In this blog post, we’ve demonstrated how to perform CRUD operations in a database using PHP, PDO, and prepared statements. This approach enhances security by preventing SQL injection and ensures a more robust interaction with the database.
Blog Published by Jay Patel
JV4SOCIETY 