Building a Reusable Database Class in PHP Using PDO

Introduction

PHP PDO database class

When working on PHP projects that involve MySQL, managing database connections and queries can quickly become repetitive and error-prone — especially when you have to write try-catch blocks or set up error handling every single time. That’s where a solid database abstraction layer comes in.

In this tutorial, we’ll walk through the creation of a reusable, static DB class using PHP’s PDO (PHP Data Objects). This class simplifies the process of executing SELECT and action queries (like INSERT, UPDATE, DELETE) while maintaining clean, secure, and efficient code.

Let’s dive in and see how it's put together.

Code Walkthrough

Here’s the full class, then we’ll break it down section by section.

abstract class DB{
	private static $username = "admin";
	private static $password = "admin_pass";
	private static $dsn = "mysql:host=localhost;dbname=test;charset=utf8";
	public static $affected_rows;

	public static function connect(){
		try {
		 	$pdo = new PDO(self::$dsn, self::$username, self::$password);
		    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		} catch (PDOException $e) {
		    error_log(date('[Y-m-d H:i:s]') . "Database connection error: " . $e->getMessage(), 3, "errors.log");
            throw new Exception("Database connection failed.");
		}
		return $pdo;
	}

	public static function select($sql, $binding_values = []){
		try{
			$pdo = self::connect();
			$query = $pdo->prepare($sql);
			$query->execute($binding_values);
			$data = $query->fetchAll(PDO::FETCH_ASSOC);
			$query = null; $pdo = null;	return $data;
		}
		catch(PDOException $e){
			error_log(date('[Y-m-d H:i:s] ') ."SQL Error: " . $e->getMessage() ."\n\r \n\r", 3, "errors.log");
			throw new Exception("Error executing SELECT query.");
		}
	}

	public static function query($sql, $binding_values = []){
		try{
			$pdo = self::connect();
			$query = $pdo->prepare($sql);
			$query->execute($binding_values);
			self::$affected_rows = $query->rowCount();
			$query = null; $pdo = null;
		}
		catch(PDOException $e){
			error_log(date('[Y-m-d H:i:s] ') . "SQL Error: " . $e->getMessage() ."\n\r \n\r", 3, "errors.log");
			throw new Exception("Error executing query.");
		}
	}
}
	

1. Database Credentials and DSN Setup

private static $username = "admin";
private static $password = "admin_pass";
private static $dsn = "mysql:host=localhost;dbname=test;charset=utf8";
public static $affected_rows;
	

This section holds our database credentials and DSN (Data Source Name) as static private properties. Storing them statically means we don't need to instantiate the class to use them — which keeps things simple and centralized.

2.The connect() Method

public static function connect(){
	try {
	 	$pdo = new PDO(self::$dsn, self::$username, self::$password);
	    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	} catch (PDOException $e) {
	    error_log(date('[Y-m-d H:i:s]') . "Database connection error: " . $e->getMessage(), 3, "errors.log");
          throw new Exception("Database connection failed.");
	}
	return $pdo;
}
	

This static method is the heart of our class — it handles connection creation. It uses PDO with exception-based error handling and logs issues to an errors.log file. If the connection fails, a generic error is thrown to the application (while the real error message is safely logged).

This method ensures that every operation uses a properly configured PDO object with error mode set to ERRMODE_EXCEPTION, which helps in spotting issues early.

3. The select() Method

public static function select($sql, $binding_values = []){
	try{
		$pdo = self::connect();
		$query = $pdo->prepare($sql);
		$query->execute($binding_values);
		$data = $query->fetchAll(PDO::FETCH_ASSOC);
		$query = null; $pdo = null;	return $data;
	}
	catch(PDOException $e){
		error_log(date('[Y-m-d H:i:s] ') ."SQL Error: " . $e->getMessage() ."\n\r \n\r", 3, "errors.log");
		throw new Exception("Error executing SELECT query.");
	}
}
	

This method is tailored for SELECT queries. It prepares and executes the SQL with optional bound parameters, then fetches the results as an associative array.

The use of fetchAll(PDO::FETCH_ASSOC) makes data retrieval intuitive by returning rows in a key-value format. We also make sure to clean up by setting the PDO and statement objects to null.

4. The query() Method

public static function query($sql, $binding_values = []){
	try{
		$pdo = self::connect();
		$query = $pdo->prepare($sql);
		$query->execute($binding_values);
		self::$affected_rows = $query->rowCount();
		$query = null; $pdo = null;
	}
	catch(PDOException $e){
		error_log(date('[Y-m-d H:i:s] ') . "SQL Error: " . $e->getMessage() ."\n\r \n\r", 3, "errors.log");
		throw new Exception("Error executing query.");
	}
}
	

This method is used for all non-SELECT queries — such as INSERT, UPDATE, and DELETE.

After executing the prepared statement, we store the number of affected rows in a public static property self::$affected_rows. This gives developers immediate feedback on how many rows were impacted by the query, which is often useful for updates and deletes.

Again, error handling and resource cleanup are handled gracefully.

Example 1: SELECT Query — Fetch All Users

In this example we are going to select all users from the database without parameters (binding values).

$sql = "SELECT * FROM users";
$users = DB::select($sql);

foreach ($users as $user) {
    echo "Username: " . htmlspecialchars($user['username']) . "
";
}
	

With Parameters (e.g., get user by ID):

$sql = "SELECT * FROM users WHERE id = ?";
$user = DB::select($sql, [1]);

if (!empty($user)) {
    echo "User found: " . htmlspecialchars($user[0]['username']);
}
	

Example 2: INSERT Query — Add a New User

$sql = "INSERT INTO users (username, email) VALUES (?, ?)";
DB::query($sql, ['johndoe', 'john@example.com']);

if (DB::$affected_rows > 0) {
    echo "User successfully added.";
} else {
    echo "Insert failed.";
}
	

Example 3: UPDATE Query — Change User Email

$sql = "UPDATE users SET email = ? WHERE username = ?";
DB::query($sql, ['newemail@example.com', 'johndoe']);

if (DB::$affected_rows > 0) {
    echo DB::$affected_rows . " row(s) updated.";
} else {
    echo "No changes made.";
}
	

Example 4: DELETE Query — Remove a User

$sql = "DELETE FROM users WHERE username = ?";
DB::query($sql, ['johndoe']);

if (DB::$affected_rows > 0) {
    echo "User deleted.";
} else {
    echo "No matching user found.";
}
	

Summary

This static DB class offers a clean and secure abstraction over raw PDO usage. By centralizing database connection logic and query execution, you reduce code repetition and improve maintainability across your project.

Using the DB class is as simple as calling DB::select() for data retrieval or DB::query() for changes. The static design means there's no need to instantiate anything, and the exception-based error handling ensures problems don’t go unnoticed.

Benefits of This Approach:

• Security: Prepared statements help prevent SQL injection.
• Clarity: Separation of logic into select() and query() improves readability.
• Reusability: Static methods mean no need to instantiate or manage DB objects across files.
• Error Handling: Exceptions are logged properly without exposing sensitive data.

Source code

Download the source code for free.

Times downloaded: 23

Buy me a coffee