PHP Database Integration

Introduction to PHP Database Integration

Database integration is fundamental to modern web development. PHP provides several ways to interact with databases, with PDO (PHP Data Objects) being the recommended approach for its security, flexibility, and database independence.

This guide covers connecting to databases, performing CRUD operations, and implementing security best practices.

Why Use PDO?

PDO offers several advantages over older MySQL extensions:

• Database Independence: Works with MySQL, PostgreSQL, SQLite, and more
• Security: Built-in protection against SQL injection
• Object-Oriented Interface: Clean, modern syntax
• Prepared Statements: Efficient and secure query execution
• Error Handling: Comprehensive exception handling

Database Connection

Basic PDO Connection

<?php
class Database {
    private $host = 'localhost';
    private $dbname = 'your_database';
    private $username = 'your_username';
    private $password = 'your_password';
    private $pdo;
    
    public function __construct() {
        $dsn = "mysql:host={$this->host};dbname={$this->dbname};charset=utf8mb4";
        
        $options = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ];
        
        try {
            $this->pdo = new PDO($dsn, $this->username, $this->password, $options);
        } catch (PDOException $e) {
            throw new PDOException($e->getMessage(), (int)$e->getCode());
        }
    }
    
    public function getConnection() {
        return $this->pdo;
    }
}

// Usage
try {
    $database = new Database();
    $pdo = $database->getConnection();
    echo "Database connected successfully!";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Configuration-Based Connection

<?php
class DatabaseConfig {
    private static $config = [
        'host' => 'localhost',
        'dbname' => 'myapp',
        'username' => 'root',
        'password' => '',
        'charset' => 'utf8mb4',
        'options' => [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]
    ];
    
    public static function getConnection() {
        static $pdo = null;
        
        if ($pdo === null) {
            $dsn = "mysql:host={self::$config['host']};dbname={self::$config['dbname']};charset={self::$config['charset']}";
            $pdo = new PDO($dsn, self::$config['username'], self::$config['password'], self::$config['options']);
        }
        
        return $pdo;
    }
}

// Usage
$pdo = DatabaseConfig::getConnection();
?>

CRUD Operations

Create (INSERT)

<?php
class UserRepository {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    public function create($username, $email, $password) {
        $sql = "INSERT INTO users (username, email, password, created_at) VALUES (?, ?, ?, NOW())";
        $stmt = $this->pdo->prepare($sql);
        
        $hashedPassword = password_hash($password, PASSWORD_DEFAULT);
        
        return $stmt->execute([$username, $email, $hashedPassword]);
    }
    
    public function createUser(array $userData) {
        $sql = "INSERT INTO users (username, email, password, first_name, last_name, created_at) 
                VALUES (:username, :email, :password, :first_name, :last_name, NOW())";
        
        $stmt = $this->pdo->prepare($sql);
        
        $userData['password'] = password_hash($userData['password'], PASSWORD_DEFAULT);
        
        return $stmt->execute($userData);
    }
    
    public function getLastInsertId() {
        return $this->pdo->lastInsertId();
    }
}

// Usage
$userRepo = new UserRepository($pdo);

// Method 1: Positional parameters
$userRepo->create('johndoe', '[email protected]', 'secretpassword');

// Method 2: Named parameters
$userData = [
    'username' => 'janedoe',
    'email' => '[email protected]',
    'password' => 'anothersecret',
    'first_name' => 'Jane',
    'last_name' => 'Doe'
];
$userRepo->createUser($userData);

echo "New user ID: " . $userRepo->getLastInsertId();
?>

Read (SELECT)

<?php
class UserRepository {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    public function findById($id) {
        $sql = "SELECT * FROM users WHERE id = ?";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$id]);
        
        return $stmt->fetch();
    }
    
    public function findByEmail($email) {
        $sql = "SELECT * FROM users WHERE email = ?";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$email]);
        
        return $stmt->fetch();
    }
    
    public function findAll($limit = 100, $offset = 0) {
        $sql = "SELECT id, username, email, created_at FROM users 
                ORDER BY created_at DESC 
                LIMIT ? OFFSET ?";
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$limit, $offset]);
        
        return $stmt->fetchAll();
    }
    
    public function search($searchTerm) {
        $sql = "SELECT id, username, email, created_at FROM users 
                WHERE username LIKE ? OR email LIKE ?
                ORDER BY username";
        
        $stmt = $this->pdo->prepare($sql);
        $searchParam = "%{$searchTerm}%";
        $stmt->execute([$searchParam, $searchParam]);
        
        return $stmt->fetchAll();
    }
    
    public function countUsers() {
        $sql = "SELECT COUNT(*) FROM users";
        $stmt = $this->pdo->query($sql);
        
        return $stmt->fetchColumn();
    }
}

// Usage
$userRepo = new UserRepository($pdo);

// Find single user
$user = $userRepo->findById(1);
if ($user) {
    echo "User: " . $user['username'];
}

// Find all users with pagination
$users = $userRepo->findAll(10, 0); // First 10 users
foreach ($users as $user) {
    echo $user['username'] . " - " . $user['email'] . "\n";
}

// Search users
$results = $userRepo->search('john');
echo "Found " . count($results) . " users";
?>

Update

<?php
class UserRepository {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    public function update($id, array $data) {
        // Build dynamic UPDATE query
        $fields = [];
        $values = [];
        
        foreach ($data as $field => $value) {
            $fields[] = "{$field} = ?";
            $values[] = $value;
        }
        
        $values[] = $id; // Add ID for WHERE clause
        
        $sql = "UPDATE users SET " . implode(', ', $fields) . " WHERE id = ?";
        $stmt = $this->pdo->prepare($sql);
        
        return $stmt->execute($values);
    }
    
    public function updateEmail($id, $newEmail) {
        $sql = "UPDATE users SET email = ?, updated_at = NOW() WHERE id = ?";
        $stmt = $this->pdo->prepare($sql);
        
        return $stmt->execute([$newEmail, $id]);
    }
    
    public function updatePassword($id, $newPassword) {
        $sql = "UPDATE users SET password = ?, updated_at = NOW() WHERE id = ?";
        $stmt = $this->pdo->prepare($sql);
        
        $hashedPassword = password_hash($newPassword, PASSWORD_DEFAULT);
        
        return $stmt->execute([$hashedPassword, $id]);
    }
    
    public function updateLastLogin($id) {
        $sql = "UPDATE users SET last_login = NOW() WHERE id = ?";
        $stmt = $this->pdo->prepare($sql);
        
        return $stmt->execute([$id]);
    }
}

// Usage
$userRepo = new UserRepository($pdo);

// Update multiple fields
$updateData = [
    'first_name' => 'John',
    'last_name' => 'Smith',
    'phone' => '+1234567890'
];
$userRepo->update(1, $updateData);

// Update specific fields
$userRepo->updateEmail(1, '[email protected]');
$userRepo->updatePassword(1, 'newpassword123');
?>

Delete

<?php
class UserRepository {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    public function delete($id) {
        $sql = "DELETE FROM users WHERE id = ?";
        $stmt = $this->pdo->prepare($sql);
        
        return $stmt->execute([$id]);
    }
    
    public function softDelete($id) {
        $sql = "UPDATE users SET deleted_at = NOW(), is_active = 0 WHERE id = ?";
        $stmt = $this->pdo->prepare($sql);
        
        return $stmt->execute([$id]);
    }
    
    public function deleteMultiple(array $ids) {
        $placeholders = str_repeat('?,', count($ids) - 1) . '?';
        $sql = "DELETE FROM users WHERE id IN ({$placeholders})";
        $stmt = $this->pdo->prepare($sql);
        
        return $stmt->execute($ids);
    }
    
    public function deleteInactive($days = 30) {
        $sql = "DELETE FROM users WHERE last_login < DATE_SUB(NOW(), INTERVAL ? DAY)";
        $stmt = $this->pdo->prepare($sql);
        
        return $stmt->execute([$days]);
    }
}

// Usage
$userRepo = new UserRepository($pdo);

// Delete single user
$userRepo->delete(1);

// Soft delete (mark as deleted without removing)
$userRepo->softDelete(2);

// Delete multiple users
$userRepo->deleteMultiple([3, 4, 5]);
?>

Advanced Database Operations

Transactions

<?php
class OrderService {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    public function createOrderWithItems($customerId, $items) {
        try {
            $this->pdo->beginTransaction();
            
            // Create the order
            $orderSql = "INSERT INTO orders (customer_id, total, created_at) VALUES (?, ?, NOW())";
            $orderStmt = $this->pdo->prepare($orderSql);
            
            $total = array_sum(array_column($items, 'price'));
            $orderStmt->execute([$customerId, $total]);
            
            $orderId = $this->pdo->lastInsertId();
            
            // Add order items
            $itemSql = "INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)";
            $itemStmt = $this->pdo->prepare($itemSql);
            
            foreach ($items as $item) {
                $itemStmt->execute([
                    $orderId,
                    $item['product_id'],
                    $item['quantity'],
                    $item['price']
                ]);
                
                // Update product stock
                $stockSql = "UPDATE products SET stock = stock - ? WHERE id = ?";
                $stockStmt = $this->pdo->prepare($stockSql);
                $stockStmt->execute([$item['quantity'], $item['product_id']]);
            }
            
            $this->pdo->commit();
            return $orderId;
            
        } catch (Exception $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }
}

// Usage
$orderService = new OrderService($pdo);

$items = [
    ['product_id' => 1, 'quantity' => 2, 'price' => 29.99],
    ['product_id' => 3, 'quantity' => 1, 'price' => 15.50]
];

try {
    $orderId = $orderService->createOrderWithItems(123, $items);
    echo "Order created with ID: $orderId";
} catch (Exception $e) {
    echo "Error creating order: " . $e->getMessage();
}
?>

Joins and Complex Queries

<?php
class BlogRepository {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    public function getPostsWithAuthors($limit = 10) {
        $sql = "SELECT p.id, p.title, p.content, p.created_at,
                       u.username, u.email as author_email
                FROM posts p
                INNER JOIN users u ON p.author_id = u.id
                WHERE p.published = 1
                ORDER BY p.created_at DESC
                LIMIT ?";
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$limit]);
        
        return $stmt->fetchAll();
    }
    
    public function getPostsWithCommentCount() {
        $sql = "SELECT p.id, p.title, p.created_at,
                       u.username as author,
                       COUNT(c.id) as comment_count
                FROM posts p
                INNER JOIN users u ON p.author_id = u.id
                LEFT JOIN comments c ON p.id = c.post_id
                WHERE p.published = 1
                GROUP BY p.id, p.title, p.created_at, u.username
                ORDER BY p.created_at DESC";
        
        $stmt = $this->pdo->query($sql);
        return $stmt->fetchAll();
    }
    
    public function getPostsByCategory($categoryId) {
        $sql = "SELECT p.*, u.username as author, c.name as category_name
                FROM posts p
                INNER JOIN users u ON p.author_id = u.id
                INNER JOIN categories c ON p.category_id = c.id
                WHERE p.category_id = ? AND p.published = 1
                ORDER BY p.created_at DESC";
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$categoryId]);
        
        return $stmt->fetchAll();
    }
}
?>

Database Query Builder

<?php
class QueryBuilder {
    private $pdo;
    private $table;
    private $select = ['*'];
    private $where = [];
    private $orderBy = [];
    private $limit;
    private $offset;
    private $params = [];
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    public function table($table) {
        $this->table = $table;
        return $this;
    }
    
    public function select($columns) {
        $this->select = is_array($columns) ? $columns : func_get_args();
        return $this;
    }
    
    public function where($column, $operator, $value) {
        $this->where[] = "$column $operator ?";
        $this->params[] = $value;
        return $this;
    }
    
    public function whereIn($column, array $values) {
        $placeholders = str_repeat('?,', count($values) - 1) . '?';
        $this->where[] = "$column IN ($placeholders)";
        $this->params = array_merge($this->params, $values);
        return $this;
    }
    
    public function orderBy($column, $direction = 'ASC') {
        $this->orderBy[] = "$column $direction";
        return $this;
    }
    
    public function limit($limit) {
        $this->limit = $limit;
        return $this;
    }
    
    public function offset($offset) {
        $this->offset = $offset;
        return $this;
    }
    
    public function get() {
        $sql = $this->buildSelectQuery();
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($this->params);
        
        return $stmt->fetchAll();
    }
    
    public function first() {
        $this->limit(1);
        $results = $this->get();
        return $results ? $results[0] : null;
    }
    
    private function buildSelectQuery() {
        $sql = "SELECT " . implode(', ', $this->select) . " FROM " . $this->table;
        
        if ($this->where) {
            $sql .= " WHERE " . implode(' AND ', $this->where);
        }
        
        if ($this->orderBy) {
            $sql .= " ORDER BY " . implode(', ', $this->orderBy);
        }
        
        if ($this->limit) {
            $sql .= " LIMIT " . $this->limit;
        }
        
        if ($this->offset) {
            $sql .= " OFFSET " . $this->offset;
        }
        
        return $sql;
    }
}

// Usage
$query = new QueryBuilder($pdo);

// Simple query
$users = $query->table('users')
               ->select('id', 'username', 'email')
               ->where('active', '=', 1)
               ->orderBy('created_at', 'DESC')
               ->limit(10)
               ->get();

// Complex query
$products = $query->table('products')
                  ->where('category_id', '=', 5)
                  ->where('price', '>', 100)
                  ->whereIn('brand_id', [1, 2, 3])
                  ->orderBy('price', 'ASC')
                  ->get();
?>

Error Handling and Debugging

Exception Handling

<?php
class DatabaseException extends Exception {}

class SafeDatabase {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    public function executeQuery($sql, $params = []) {
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt;
        } catch (PDOException $e) {
            $this->logError($e, $sql, $params);
            throw new DatabaseException("Database query failed: " . $e->getMessage());
        }
    }
    
    public function select($sql, $params = []) {
        $stmt = $this->executeQuery($sql, $params);
        return $stmt->fetchAll();
    }
    
    public function selectOne($sql, $params = []) {
        $stmt = $this->executeQuery($sql, $params);
        return $stmt->fetch();
    }
    
    public function insert($table, array $data) {
        $columns = implode(', ', array_keys($data));
        $placeholders = ':' . implode(', :', array_keys($data));
        
        $sql = "INSERT INTO {$table} ({$columns}) VALUES ({$placeholders})";
        
        $this->executeQuery($sql, $data);
        return $this->pdo->lastInsertId();
    }
    
    private function logError($exception, $sql, $params) {
        $error = [
            'message' => $exception->getMessage(),
            'sql' => $sql,
            'params' => $params,
            'timestamp' => date('Y-m-d H:i:s'),
            'trace' => $exception->getTraceAsString()
        ];
        
        error_log("Database Error: " . json_encode($error));
    }
}

// Usage
try {
    $db = new SafeDatabase($pdo);
    
    $users = $db->select("SELECT * FROM users WHERE active = ?", [1]);
    
    $newUserId = $db->insert('users', [
        'username' => 'newuser',
        'email' => '[email protected]',
        'password' => password_hash('password', PASSWORD_DEFAULT)
    ]);
    
} catch (DatabaseException $e) {
    echo "Application Error: " . $e->getMessage();
    // Log error, show user-friendly message
}
?>

Performance Optimization

Connection Pooling and Caching

<?php
class CachedRepository {
    private $pdo;
    private $cache = [];
    private $cacheExpiry = 300; // 5 minutes
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    public function findUserById($id) {
        $cacheKey = "user_{$id}";
        
        // Check cache first
        if (isset($this->cache[$cacheKey])) {
            $cached = $this->cache[$cacheKey];
            if (time() - $cached['timestamp'] < $this->cacheExpiry) {
                return $cached['data'];
            }
        }
        
        // Fetch from database
        $sql = "SELECT * FROM users WHERE id = ?";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$id]);
        $user = $stmt->fetch();
        
        // Cache the result
        $this->cache[$cacheKey] = [
            'data' => $user,
            'timestamp' => time()
        ];
        
        return $user;
    }
    
    public function clearCache($pattern = null) {
        if ($pattern) {
            foreach ($this->cache as $key => $value) {
                if (strpos($key, $pattern) !== false) {
                    unset($this->cache[$key]);
                }
            }
        } else {
            $this->cache = [];
        }
    }
}
?>

Related Topics

Explore these related database topics:

• PHP PDO Prepared Statements - Secure query execution
• PHP Database Migrations - Managing database schema changes
• PHP ActiveRecord Pattern - ORM-style database interaction
• PHP Database Security - Protecting against SQL injection
• PHP Performance Optimization - Optimizing database queries

Summary

PHP database integration with PDO provides a secure, flexible foundation for building data-driven applications. Key concepts include:

• PDO Connection: Establishing secure database connections
• CRUD Operations: Creating, reading, updating, and deleting data
• Prepared Statements: Preventing SQL injection attacks
• Transactions: Ensuring data integrity
• Error Handling: Proper exception management
• Performance: Query optimization and caching strategies

Understanding these database fundamentals is essential for building robust PHP applications that can scale and maintain data security.