If you don't have database lesson yet, please take our database course before commencing the course.
Connecting your web application to a database is one of the most critical steps in backend development. Writing raw, repetitive SQL queries all over your application leads to messy code and security vulnerabilities.
By wrapping database operations into a Reusable PHP Database Class using PDO (PHP Data Objects), you ensure clean code, reusability, and strong defense against SQL injection through prepared statements.
<?php
class Database {
private $host = 'localhost';
private $db_name = 'school_db';
private $username = 'root';
private $password = '';
private $conn;
/**
* Constructor: Establishes the database connection via PDO
*/
public function __construct() {
try {
$dsn = "mysql:host=" . $this->host . ";dbname=" . $this->db_name . ";charset=utf8mb4";
$this->conn = new PDO($dsn, $this->username, $this->password);
// Set error mode to exception for easy debugging
$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Set default fetch mode to associative array
$this->conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
} catch (PDOException $e) {
die("Database Connection Error: " . $e->getMessage());
}
}
/**
* 1. General Query Function (For complex queries, JOINs, custom statements)
*/
public function query($sql, $params = []) {
try {
$stmt = $this->conn->prepare($sql);
$stmt->execute($params);
return $stmt;
} catch (PDOException $e) {
die("Query Error: " . $e->getMessage());
}
}
/**
* 2. Insert Function
* Automatically builds an INSERT query from an associative array.
*/
public function insert($table, $data) {
$keys = array_keys($data);
$fields = implode(', ', $keys);
$placeholders = ':' . implode(', :', $keys);
$sql = "INSERT INTO {$table} ({$fields}) VALUES ({$placeholders})";
try {
$stmt = $this->conn->prepare($sql);
$stmt->execute($data);
return $this->conn->lastInsertId(); // Returns the new record's ID
} catch (PDOException $e) {
die("Insert Error: " . $e->getMessage());
}
}
/**
* 3. Update Function
* Updates rows dynamically based on an associative data array and a WHERE clause.
*/
public function update($table, $data, $where, $whereParams = []) {
$setParts = [];
foreach (array_keys($data) as $key) {
$setParts[] = "{$key} = :{$key}";
}
$setStr = implode(', ', $setParts);
$sql = "UPDATE {$table} SET {$setStr} WHERE {$where}";
// Merge data array and WHERE parameters for execution
$params = array_merge($data, $whereParams);
try {
$stmt = $this->conn->prepare($sql);
return $stmt->execute($params); // Returns true on success
} catch (PDOException $e) {
die("Update Error: " . $e->getMessage());
}
}
/**
* 4. Delete Function
* Deletes records matching a specific WHERE condition.
*/
public function delete($table, $where, $params = []) {
$sql = "DELETE FROM {$table} WHERE {$where}";
try {
$stmt = $this->conn->prepare($sql);
return $stmt->execute($params); // Returns true on success
} catch (PDOException $e) {
die("Delete Error: " . $e->getMessage());
}
}
/**
* 5. Fetch Single Row Function
* Retrieves one specific record (e.g., getting a user by ID).
*/
public function fetchRow($sql, $params = []) {
$stmt = $this->query($sql, $params);
return $stmt->fetch();
}
/**
* 6. Fetch Multiple Rows Function
* Retrieves lists of records (e.g., getting all active students).
*/
public function fetchRows($sql, $params = []) {
$stmt = $this->query($sql, $params);
return $stmt->fetchAll();
}
}
?>
To use the class on any file, you must first include the database class
<?php
require_once 'Database.php';
$db = new Database();
?>
Pass the table name and an associative array where keys correspond to column names.
<?php
$newStudent = [
'first_name' => 'John',
'last_name' => 'Doe',
'grade_level' => 'Grade 3',
'status' => 'Pending'
];
$studentId = $db->insert('admissions', $newStudent);
echo "New student registered with ID: " . $studentId;
?>
Great for looking up specific records like user profiles or applicant details using placeholders (? or named parameters).
<?php
$sql = "SELECT * FROM admissions WHERE id = :id";
$student = $db->fetchRow($sql, ['id' => 5]);
if ($student) {
echo "Welcome back, " . $student['first_name'] . "!";
}
?>
Great for displaying tables, lists, or catalogs of records.
<?php
$sql = "SELECT * FROM admissions WHERE grade_level = :grade ORDER BY created_at DESC";
$gradeThreeApplicants = $db->fetchRows($sql, ['grade' => 'Grade 3']);
foreach ($gradeThreeApplicants as $applicant) {
echo "" . $applicant['first_name'] . " " . $applicant['last_name'] . " ";
}
?>
Pass the table, the associative array of columns/values to change, the WHERE clause, and any required parameters.
<?php
$updatedData = [
'status' => 'Approved',
'reviewed_by' => 'Admin_Sarah'
];
// Updates record where id equals 5
$success = $db->update('admissions', $updatedData, "id = :id", ['id' => 5]);
if ($success) {
echo "Applicant status updated successfully!";
}
?>
Remove records securely using a conditional WHERE clause.
<?php
// Deletes applicant where id equals 12
$deleted = $db->delete('admissions', "id = :id", ['id' => 12]);
if ($deleted) {
echo "Record deleted successfully.";
}
?>
When you need advanced SQL operations like multi-table JOIN statements, table groupings, or database statistics where helper shortcuts don't fit, use the general query method.
<?php
$sql = "SELECT a.first_name, a.last_name, p.payment_status
FROM admissions a
JOIN payments p ON a.id = p.student_id
WHERE p.amount > :amount";
// Execute complex query and fetch results manually
$results = $db->query($sql, ['amount' => 100])->fetchAll();
foreach ($results as $row) {
echo $row['first_name'] . " - Payment Status: " . $row['payment_status'] . "
";
}
?>
Run this SQL query in your database tool (like phpMyAdmin):
CREATE DATABASE IF NOT EXISTS library_db;
USE library_db;
CREATE TABLE IF NOT EXISTS books (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
author VARCHAR(255) NOT NULL,
image VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Make sure image-1.jpg, image-2.jpg, image-3.jpg and image-4.jpg are placed in the images folder.
Place the database class we've already written earliner in the database.php file
In index.php, we call the insert function directly using PHP code, followed immediately by fetching and displaying the books table with their images below it:
<?php
require_once 'database.php';
$db = new Database();
// Step 5: Call the insert function programmatically (demonstrating how data is added)
// Note: In a real app without forms, you can test inserting a record like this:
/*
$db->insert('books', [
'title' => 'Learning PHP Programming',
'author' => 'Jane Smith',
'image' => 'sample1.jpg'
]);
*/
// Fetch all inserted books from the database
$books = $db->fetchRows("SELECT * FROM books ORDER BY id DESC");
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Book Inventory</title>
<style>
body { font-family: Arial, sans-serif; max-width: 800px; margin: 40px auto; padding: 20px; background: #f8fafc; color: #1e293b; }
.card { background: #fff; padding: 20px; border-radius: 8px; box-shadow: 0 4px 6px rgba(0,0,0,0.05); margin-bottom: 30px; }
table { width: 100%; border-collapse: collapse; background: #fff; border-radius: 8px; overflow: hidden; box-shadow: 0 4px 6px rgba(0,0,0,0.05); }
th, td { padding: 12px 15px; text-align: left; border-bottom: 1px solid #e2e8f0; }
th { background: #f1f5f9; font-weight: bold; }
/* Step 8: Styling the book image in the table */
img.book-thumb { width: 50px; height: 70px; object-fit: cover; border-radius: 4px; border: 1px solid #cbd5e1; }
code { background: #e2e8f0; padding: 2px 6px; border-radius: 4px; font-size: 0.9em; }
</style>
</head>
<body>
<div class="card">
<h2>Database Insert Demo</h2>
<p>The insert function was called using code (form processing will be covered in the next lesson).
<p>To test adding a book manually, uncomment the $db->insert(...) code block in your index.php file.
</div>
<div class="card">
<h2>Book Inventory List</h2>
<table>
<thead>
<tr>
<th>Cover Image</th>
<th>Book Title</th>
<th>Author</th>
<th>Date Added</th>
</tr>
</thead>
<tbody>
<?php if (count($books) > 0): ?>
<?php foreach ($books as $book): ?>
<tr>
<td>
<!-- Displaying image stored in the images folder using the database column value -->
<img src="images/" alt="Book Cover" class="book-thumb">
</td>
<td></td>
<td></td>
<td></td>
</tr>
<?php endforeach; ?>
<?php else: ?>
<tr>
<td colspan="4" style="text-align: center; color: #64748b; padding: 20px;">
No books found in the database yet. Try running an insert statement!
</td>
</tr>
<?php endif; ?>
</tbody>
</table>
</div>
</body>
</html>