When developing with vanilla PHP (PHP without frameworks), creating a custom database class can greatly simplify and streamline database interactions across your application. This approach ensures better code organization and reusability while allowing for secure handling of SQL queries, prepared statements, and error management.
In this article, we will guide you through creating a custom database class using PDO (PHP Data Objects), a secure and flexible way to interact with databases. This class will handle database connections, query execution, and CRUD operations.
Why Use a Custom Database Class?
Reusability: A custom class allows you to define reusable functions for database operations, reducing redundant code.
Security: PDO prepared statements prevent SQL injection attacks, ensuring that your database interactions are secure.
Error Handling: A custom class can centralize error handling, making it easier to troubleshoot and debug database-related issues.
Maintainability: With a centralized class for database interactions, you can quickly make changes or optimizations to your database logic without touching every individual script.
Setting Up the Database Class
Let's start by creating a Database class that connects to a database, handles queries, and supports prepared statements.
Step 1: Create the Database Class
class Database {
private $host = 'localhost'; // Database host
private $dbname = 'your_database_name'; // Database name
private $username = 'root'; // Database username
private $password = ''; // Database password (if any)
private $dbh; // PDO instance
// Constructor to initialize PDO connection
public function __construct() {
try {
// PDO connection
$this->dbh = new PDO(
'mysql:host=' . $this->host . ';dbname=' . $this->dbname,
$this->username,
$this->password
);
// Set PDO to throw exceptions on errors
$this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
// Handle connection error
echo 'Connection failed: ' . $e->getMessage();
}
}
// Method to run a query with optional parameters
public function query($sql, $params = []) {
try {
// Prepare the SQL statement
$stmt = $this->dbh->prepare($sql);
// Bind parameters if any
if (!empty($params)) {
foreach ($params as $key => $value) {
$stmt->bindValue($key, $value);
}
}
// Execute the query
$stmt->execute();
return $stmt;
} catch (PDOException $e) {
echo 'Query failed: ' . $e->getMessage();
return false;
}
}
// Method to fetch all results
public function fetchAll($sql, $params = []) {
$stmt = $this->query($sql, $params);
if ($stmt) {
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
return [];
}
// Method to fetch a single result
public function fetchOne($sql, $params = []) {
$stmt = $this->query($sql, $params);
if ($stmt) {
return $stmt->fetch(PDO::FETCH_ASSOC);
}
return null;
}
// Method to insert data and return the last inserted ID
public function insert($sql, $params = []) {
$stmt = $this->query($sql, $params);
if ($stmt) {
return $this->dbh->lastInsertId();
}
return null;
}
// Method to update data and return the number of affected rows
public function update($sql, $params = []) {
$stmt = $this->query($sql, $params);
return $stmt ? $stmt->rowCount() : 0;
}
// Method to delete data and return the number of affected rows
public function delete($sql, $params = []) {
$stmt = $this->query($sql, $params);
return $stmt ? $stmt->rowCount() : 0;
}
// Close the connection
public function close() {
$this->dbh = null;
}
}
?>
Key Features of the Database Class
Connection Handling: The constructor connects to the database using PDO and handles connection errors by displaying a message.
Query Method: The query() method prepares and executes SQL queries, allowing optional parameters for dynamic queries.
Fetch Methods:
fetchAll() retrieves all results from a SELECT query.
fetchOne() retrieves a single result from a SELECT query.
CRUD Operations:
insert() executes an INSERT query and returns the last inserted ID.
update() executes an UPDATE query and returns the number of affected rows.
delete() executes a DELETE query and returns the number of affected rows.
Error Handling: Exceptions are thrown for connection errors and query failures, ensuring smooth debugging.
Closing the Connection: The close() method sets the PDO instance to null, effectively closing the connection to the database.
Step 2: Using the Database Class
Now, let's see how to use the custom database class in your application.
Example: Inserting Data
// Include the Database class
require_once 'Database.php';
// Create a new Database object
$db = new Database();
// Insert a new user
$sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
$params = [
':name' => 'John Doe',
':email' => 'john.doe@example.com'
];
$lastInsertId = $db->insert($sql, $params);
echo "New user inserted with ID: " . $lastInsertId;
?>
Example: Fetching Data
// Create a new Database object
$db = new Database();
// Fetch all users
$sql = "SELECT * FROM users";
$users = $db->fetchAll($sql);
// Output users
foreach ($users as $user) {
echo $user['name'] . " - " . $user['email'] . "<br>";
}
?>
Example: Updating Data
// Create a new Database object
$db = new Database();
// Update user email
$sql = "UPDATE users SET email = :email WHERE id = :id";
$params = [
':email' => 'new.email@example.com',
':id' => 1
];
$affectedRows = $db->update($sql, $params);
echo $affectedRows . " rows updated.";
?>
Example: Deleting Data
// Create a new Database object
$db = new Database();
// Delete a user
$sql = "DELETE FROM users WHERE id = :id";
$params = [':id' => 1];
$affectedRows = $db->delete($sql, $params);
echo $affectedRows . " rows deleted.";
?>
Step 3: Improving the Database Class
Transaction Support: You can enhance this class by adding transaction handling methods such as beginTransaction(), commit(), and rollBack() to ensure atomic operations.
Prepared Statements for Security: Always use prepared statements with bound parameters to prevent SQL injection attacks.
Additional Error Handling: Consider logging database errors to a file instead of directly displaying them in production environments.
Conclusion
A custom database class is a powerful tool in vanilla PHP that helps you manage database connections and queries more efficiently. By leveraging PDO, you ensure secure interactions with the database while maintaining flexibility and reusability in your codebase. Whether you’re building a simple website or a complex application, using this approach can make your development process cleaner, more efficient, and easier to maintain.
