Detailed Description

Integrating SQL with programming languages is a fundamental skill that bridges the gap between database management and application development. While SQL is powerful for managing and querying data, most real-world applications require the dynamic capabilities of general-purpose programming languages like Python, Java, and PHP to create user interfaces, implement business logic, and deliver interactive experiences. This integration enables developers to build robust, data-driven applications that can respond to user input, process information in real-time, and scale to meet enterprise demands.

The importance of this integration cannot be overstated in modern software development. Nearly every web application, mobile app, or enterprise system relies on databases to store and retrieve information. Whether you're building an e-commerce platform that needs to manage product inventories and customer orders, a social media application that handles user profiles and posts, or a financial system that processes transactions and generates reports, you'll need to seamlessly connect your application code with database operations. Understanding how to properly integrate SQL with programming languages is essential for creating secure, efficient, and maintainable applications that can handle millions of transactions and serve thousands of concurrent users.

At its core, integrating SQL with programming languages involves several key concepts and components. First, you need a database driver or connector – a software library that enables communication between your programming language and the database management system. These drivers implement the necessary protocols and provide APIs that your application code can use to establish connections, send queries, and receive results. Second, you'll work with connection objects that maintain the link between your application and the database, managing authentication, network communication, and session state. Third, you'll utilize cursor objects or similar constructs that allow you to execute SQL statements and iterate through result sets. Additionally, modern development often involves concepts like connection pooling for managing multiple database connections efficiently, prepared statements for preventing SQL injection attacks, and transaction management for ensuring data consistency.

Common use cases for SQL integration span virtually every industry and application type. In web development, you'll connect to databases to authenticate users, retrieve dynamic content, and process form submissions. In data science and analytics, Python scripts connect to databases to extract large datasets for analysis and visualization. Enterprise applications use Java's JDBC to interact with Oracle or PostgreSQL databases for complex business operations. Content management systems built with PHP rely on database connections to store and retrieve articles, media files, and user comments. Mobile applications connect to cloud-hosted databases to sync data across devices. Even desktop applications and scripts for automation and reporting depend on database integration to function effectively.

By completing this lesson, students will master several critical competencies. You'll learn how to establish secure database connections using proper authentication and connection parameters. You'll understand how to execute various types of SQL statements – from simple SELECT queries to complex transactions involving multiple operations. You'll gain proficiency in handling query results, processing rows of data, and managing different data types between your programming language and SQL. You'll discover best practices for error handling, resource management, and security, including how to protect against SQL injection vulnerabilities. Finally, you'll develop the ability to design and implement database access layers that separate data operations from business logic, creating more maintainable and testable code. These skills form the foundation for building professional-grade applications and will serve you throughout your career as a developer.

Practical Examples

Example 1: Python Database Connection and Basic Queries with SQLite

This example demonstrates the fundamentals of connecting to a database, creating tables, inserting data, and retrieving results using Python's built-in SQLite support. SQLite is perfect for learning because it requires no separate server installation.


import sqlite3
from datetime import datetime

# Establish a connection to the database
# If the file doesn't exist, SQLite will create it
def create_connection(database_path):
    """
    Create a database connection to a SQLite database
    Returns connection object or None
    """
    try:
        conn = sqlite3.connect(database_path)
        print(f"Successfully connected to SQLite version: {sqlite3.version}")
        return conn
    except sqlite3.Error as e:
        print(f"Error connecting to database: {e}")
        return None

# Create a table for storing employee information
def create_employees_table(conn):
    """
    Create employees table if it doesn't exist
    """
    try:
        cursor = conn.cursor()
        
        create_table_sql = """
        CREATE TABLE IF NOT EXISTS employees (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            first_name TEXT NOT NULL,
            last_name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            department TEXT,
            salary REAL,
            hire_date TEXT
        );
        """
        
        cursor.execute(create_table_sql)
        conn.commit()
        print("Employees table created successfully")
    except sqlite3.Error as e:
        print(f"Error creating table: {e}")

# Insert a new employee record
def insert_employee(conn, employee_data):
    """
    Insert a new employee into the employees table
    employee_data: tuple containing (first_name, last_name, email, department, salary, hire_date)
    """
    try:
        cursor = conn.cursor()
        
        insert_sql = """
        INSERT INTO employees (first_name, last_name, email, department, salary, hire_date)
        VALUES (?, ?, ?, ?, ?, ?);
        """
        
        cursor.execute(insert_sql, employee_data)
        conn.commit()
        print(f"Employee {employee_data[0]} {employee_data[1]} inserted successfully with ID: {cursor.lastrowid}")
        return cursor.lastrowid
    except sqlite3.IntegrityError as e:
        print(f"Integrity error: {e} - Email might already exist")
        return None
    except sqlite3.Error as e:
        print(f"Error inserting employee: {e}")
        return None

# Query and display all employees
def get_all_employees(conn):
    """
    Retrieve and display all employees from the database
    """
    try:
        cursor = conn.cursor()
        
        select_sql = "SELECT * FROM employees ORDER BY last_name;"
        
        cursor.execute(select_sql)
        rows = cursor.fetchall()
        
        print("\n--- All Employees ---")
        for row in rows:
            print(f"ID: {row[0]}, Name: {row[1]} {row[2]}, Email: {row[3]}, "
                  f"Department: {row[4]}, Salary: ${row[5]:,.2f}")
        
        return rows
    except sqlite3.Error as e:
        print(f"Error retrieving employees: {e}")
        return []

# Main execution
def main():
    # Create connection
    database = "company.db"
    conn = create_connection(database)
    
    if conn is not None:
        # Create table
        create_employees_table(conn)
        
        # Insert sample employees
        employees = [
            ("John", "Doe", "john.doe@company.com", "Engineering", 75000.00, "2023-01-15"),
            ("Jane", "Smith", "jane.smith@company.com", "Marketing", 68000.00, "2023-02-20"),
            ("Michael", "Johnson", "michael.j@company.com", "Sales", 72000.00, "2023-03-10")
        ]
        
        for emp in employees:
            insert_employee(conn, emp)
        
        # Retrieve and display all employees
        get_all_employees(conn)
        
        # Always close the connection when done
        conn.close()
        print("\nDatabase connection closed")
    else:
        print("Failed to create database connection")

if __name__ == "__main__":
    main()

Output:


Successfully connected to SQLite version: 2.6.0
Employees table created successfully
Employee John Doe inserted successfully with ID: 1
Employee Jane Smith inserted successfully with ID: 2
Employee Michael Johnson inserted successfully with ID: 3

--- All Employees ---
ID: 1, Name: John Doe, Email: john.doe@company.com, Department: Engineering, Salary: $75,000.00
ID: 2, Name: Jane Smith, Email: jane.smith@company.com, Department: Marketing, Salary: $68,000.00
ID: 3, Name: Michael Johnson, Email: michael.j@company.com, Department: Sales, Salary: $72,000.00

Database connection closed

Best Practices Demonstrated:

Using parameterized queries (?) to prevent SQL injection
Proper error handling with try-except blocks
Always closing database connections to free resources
Using commit() explicitly for data modification operations
Organizing code into reusable functions

Common Mistakes to Avoid:

Forgetting to commit changes after INSERT, UPDATE, or DELETE operations
Not closing connections, which can lead to resource leaks
Using string formatting instead of parameterized queries (security risk)
Not handling exceptions, leading to unclear error messages

Example 2: Python with MySQL - Advanced Queries and Transaction Management

This example shows how to connect to a MySQL database using the mysql-connector-python library, demonstrating more advanced features like transactions and error rollback.


import mysql.connector
from mysql.connector import Error
from decimal import Decimal

def create_mysql_connection():
    """
    Create a connection to MySQL database
    Returns connection object or None
    """
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='store_db',
            user='your_username',
            password='your_password',
            port=3306,
            autocommit=False  # Disable autocommit for transaction control
        )
        
        if connection.is_connected():
            db_info = connection.get_server_info()
            print(f"Successfully connected to MySQL Server version {db_info}")
            return connection
    except Error as e:
        print(f"Error while connecting to MySQL: {e}")
        return None

def create_tables(connection):
    """
    Create necessary tables for a simple store database
    """
    cursor = connection.cursor()
    
    try:
        # Create customers table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS customers (
                customer_id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                email VARCHAR(100) UNIQUE NOT NULL,
                balance DECIMAL(10, 2) DEFAULT 0.00,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        
        # Create orders table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS orders (
                order_id INT AUTO_INCREMENT PRIMARY KEY,
                customer_id INT NOT NULL,
                order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                total_amount DECIMAL(10, 2) NOT NULL,
                status VARCHAR(20) DEFAULT 'pending',
                FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
            )
        """)
        
        connection.commit()
        print("Tables created successfully")
    except Error as e:
        print(f"Error creating tables: {e}")
        connection.rollback()
    finally:
        cursor.close()

def process_order_transaction(connection, customer_id, order_amount):
    """
    Process an order using a transaction to ensure data consistency
    This function deducts from customer balance and creates an order
    """
    cursor = connection.cursor()
    
    try:
        # Start transaction (already implicit with autocommit=False)
        
        # Check customer balance
        cursor.execute(
            "SELECT balance FROM customers WHERE customer_id = %s FOR UPDATE",
            (customer_id,)
        )
        result = cursor.fetchone()
        
        if result is None:
            raise Exception(f"Customer with ID {customer_id} not found")
        
        current_balance = result[0]
        
        if current_balance < order_amount:
            raise Exception(f"Insufficient balance. Current: ${current_balance}, Required: ${order_amount}")
        
        # Deduct from customer balance
        new_balance = current_balance - order_amount
        cursor.execute(
            "UPDATE customers SET balance = %s WHERE customer_id = %s",
            (new_balance, customer_id)
        )
        
        # Create order record
        cursor.execute(
            "INSERT INTO orders (customer_id, total_amount, status) VALUES (%s, %s, %s)",
            (customer_id, order_amount, 'completed')
        )
        
        order_id = cursor.lastrowid
        
        # Commit the transaction
        connection.commit()
        print(f"Order {order_id} processed successfully. New balance: ${new_balance}")
        return order_id
        
    except Exception as e:
        # Rollback on any error
        connection.rollback()
        print(f"Transaction failed: {e}")
        return None
    finally:
        cursor.close()

def get_customer_orders(connection, customer_id):
    """
    Retrieve all orders for a specific customer using JOIN
    """
    cursor = connection.cursor(dictionary=True)  # Return results as dictionaries
    
    try:
        query = """
            SELECT 
                c.name,
                c.email,
                c.balance,
                o.order_id,
                o.order_date,
                o.total_amount,
                o.status
            FROM customers c
            INNER JOIN orders o ON c.customer_id = o.customer_id
            WHERE c.customer_id = %s
            ORDER BY o.order_date DESC
        """
        
        cursor.execute(query, (customer_id,))
        orders = cursor.fetchall()
        
        if orders:
            print(f"\n--- Orders for {orders[0]['name']} ---")
            print(f"Email: {orders[0]['email']}")
            print(f"Current Balance: ${orders[0]['balance']}")
            print("\nOrder History:")
            for order in orders:
                print(f"  Order #{order['order_id']}: ${order['total_amount']} "
                      f"on {order['order_date']} - {order['status']}")
        else:
            print(f"No orders found for customer {customer_id}")
        
        return orders
        
    except Error as e:
        print(f"Error retrieving orders: {e}")
        return []
    finally:
        cursor.close()

def main():
    # Create connection
    connection = create_mysql_connection()
    
    if connection is not None and connection.is_connected():
        try:
            # Create tables
            create_tables(connection)
            
            # Insert sample customer
            cursor = connection.cursor()
            cursor.execute(
                "INSERT INTO customers (name, email, balance) VALUES (%s, %s, %s)",
                ("Alice Brown", "alice.brown@email.com", 500.00)
            )
            connection.commit()
            customer_id = cursor.lastrowid
            cursor.close()
            
            print(f"\nCustomer created with ID: {customer_id}")
            
            # Process orders with transaction management
            process_order_transaction(connection, customer_id, 150.00)
            process_order_transaction(connection, customer_id, 200.00)
            
            # Attempt order that will fail (insufficient funds)
            process_order_transaction(connection, customer_id, 300.00)
            
            # Retrieve and display customer orders
            get_customer_orders(connection, customer_id)
            
        finally:
            if connection.is_connected():
                connection.close()
                print("\nMySQL connection closed")

if __name__ == "__main__":
    main()

Key Learning Points:

Transaction Management: Using commit() and rollback() to ensure data consistency
Parameterized Queries: Using %s placeholders specific to MySQL connector
Row Locking: FOR UPDATE clause prevents concurrent modification issues
Dictionary Cursors: Returning results as dictionaries for easier access
Error Recovery: Automatic rollback on exceptions maintains database integrity

Example 3: Java JDBC with PostgreSQL - Connection Pooling and Prepared Statements

This example demonstrates enterprise-level database integration using Java with JDBC, including connection pooling with HikariCP for better performance.


import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Product Data Access Object demonstrating JDBC best practices
 */
public class ProductDAO {
    private static HikariDataSource dataSource;
    
    /**
     * Initialize the connection pool
     */
    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/inventory_db");
        config.setUsername("your_username");
        config.setPassword("your_password");
        
        // Connection pool configuration
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(2);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);
        
        // Performance settings
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        
        dataSource = new HikariDataSource(config);
        System.out.println("Connection pool initialized successfully");
    }
    
    /**
     * Product entity class
     */
    public static class Product {
        private int id;
        private String name;
        private String category;
        private double price;
        private int quantity;
        
        // Constructor
        public Product(int id, String name, String category, double price, int quantity) {
            this.id = id;
            this.name = name;
            this.category = category;
            this.price = price;
            this.quantity = quantity;
        }
        
        // Getters
        public int getId() { return id; }
        public String getName() { return name; }
        public String getCategory() { return category; }
        public double getPrice() { return price; }
        public int getQuantity() { return quantity; }
        
        @Override
        public String toString() {
            return String.format("Product[ID=%d, Name=%s, Category=%s, Price=$%.2f, Qty=%d]",
                id, name, category, price, quantity);
        }
    }
    
    /**
     * Create the products table if it doesn't exist
     */
    public void createTable() throws SQLException {
        String createTableSQL = """
            CREATE TABLE IF NOT EXISTS products (
                product_id SERIAL PRIMARY KEY,
                name VARCHAR(200) NOT NULL,
                category VARCHAR(100),
                price DECIMAL(10, 2) NOT NULL,
                quantity INTEGER DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """;
        
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement()) {
            
            stmt.execute(createTableSQL);
            System.out.println("Products table created successfully");
        }
    }
    
    /**
     * Insert a new product using prepared statement
     */
    public int insertProduct(String name, String category, double price, int quantity) 
            throws SQLException {
        String insertSQL = """
            INSERT INTO products (name, category, price, quantity)
            VALUES (?, ?, ?, ?)
            RETURNING product_id
        """;
        
        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
            
            // Set parameters
            pstmt.setString(1, name);
            pstmt.setString(2, category);
            pstmt.setDouble(3, price);
            pstmt.setInt(4, quantity);
            
            // Execute and get generated ID
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                int productId = rs.getInt(1);
                System.out.println("Product inserted with ID: " + productId);
                return productId;
            }
            
            throw new SQLException("Failed to insert product");
        }
    }
    
    /**
     * Retrieve products by category with pagination
     */
    public List getProductsByCategory(String category, int limit, int offset) 
            throws SQLException {
        String selectSQL = """
            SELECT product_id, name, category, price, quantity
            FROM products
            WHERE category = ?
            ORDER BY name
            LIMIT ? OFFSET ?
        """;
        
        List products = new ArrayList<>();
        
        try (Connection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(selectSQL)) {
            
            pstmt.setString(1, category);
            pstmt.setInt(2, limit);
            pstmt.setInt(3, offset);
            
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Product product = new Product(
                    rs.getInt("product_id"),
                    rs.getString("name"),
                    rs.getString("category"),
                    rs.getDouble("price"),
                    rs.getInt("quantity")
                );
                products.add(product);
            }
        }
        
        return products;
    }
    
    /**
     * Update product quantity using a transaction
     */
    public boolean updateProductQuantity(int productId, int quantityChange) {
        Connection conn = null;
        PreparedStatement checkStmt = null;
        PreparedStatement updateStmt = null;
        
        try {
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);  // Start transaction
            
            // Check current quantity
            String checkSQL = "SELECT quantity FROM products WHERE product_id = ? FOR UPDATE";
            checkStmt = conn.prepareStatement(checkSQL);
            checkStmt.setInt(1, productId);
            ResultSet rs = checkStmt.executeQuery();
            
            if (!rs.next()) {
                throw new SQLException("Product not found: " + productId);
            }
            
            int currentQuantity = rs.getInt("quantity");
            int newQuantity = currentQuantity + quantityChange;
            
            if (newQuantity < 0) {
                throw new SQLException("Insufficient quantity. Current: " + currentQuantity);
            }
            
            // Update quantity
            String updateSQL = "UPDATE products SET quantity = ? WHERE product_id = ?";
            updateStmt = conn.prepareStatement(updateSQL);
            updateStmt.setInt(1, newQuantity);
            updateStmt.setInt(2, productId);
            updateStmt.executeUpdate();
            
            conn.commit();  // Commit transaction
            System.out.println("Product quantity updated: " + currentQuantity + " -> " + newQuantity);
            return true;
            
        } catch (SQLException e) {
            System.err.println("Error updating product: " + e.getMessage());
            if (conn != null) {
                try {
                    conn.rollback();  // Rollback on error
                } catch (SQLException ex) {
                    System.err.println("Error rolling back: " + ex.getMessage());
                }
            }
            return false;
        } finally {
            // Clean up resources
            try {
                if (checkStmt != null) checkStmt.close();
                if (updateStmt != null) updateStmt.close();
                if (conn != null) {
                    conn.setAutoCommit(true);
                    conn.close();
                }
            } catch (SQLException e) {
                System.err.println("Error closing resources: " + e.getMessage());
            }
        }
    }
    
    /**
     * Close the connection pool
     */
    public static void shutdown() {
        if (dataSource != null) {
            dataSource.close();
            System.out.println("Connection pool shut down");
        }
    }
    
    /**
     * Main method demonstrating usage
     */
    public static void main(String[] args) {
        ProductDAO dao = new ProductDAO();
        
        try {
            // Create table
            dao.createTable();
            
            // Insert products
            dao.insertProduct("Laptop", "Electronics", 999.99, 50);
            dao.insertProduct("Mouse", "Electronics", 29.99, 200);
            dao.insertProduct("Desk", "Furniture", 299.99, 15);
            
            // Retrieve products
            List electronics = dao.getProductsByCategory("Electronics", 10, 0);
            System.out.println("\nElectronics Products:");
            electronics.forEach(System.out::println);
            
            // Update quantity
            dao.updateProductQuantity(1, -5);  // Sell 5 laptops
            
        } catch (SQLException e) {
            System.err.println("Database error: " + e.getMessage());
            e.printStackTrace();
        } finally {
            ProductDAO.shutdown();
        }
    }
}

Best Practices Demonstrated:

Connection Pooling: Using HikariCP for efficient connection management
Prepared Statements: Preventing SQL injection and improving performance
Try-with-Resources: Automatic resource cleanup for statements and connections
Transaction Management: Explicit control over commit and rollback
Data Access Object Pattern: Separating database logic from business logic

Example 4: PHP with PDO - Web Application Database Integration

This example shows how to build a secure database layer for a web application using PHP's PDO (PHP Data Objects) with proper error handling and security measures.


<?php
/**
 * Database connection class using Singleton pattern
 */
class Database {
    private static $instance = null;
    private $pdo;
    
    // Private constructor to prevent direct instantiation
    private function __construct() {
        $host = 'localhost';
        $dbname = 'blog_db';
        $username = 'your_username';
        $password = 'your_password';
        
        $dsn = "mysql:host={$host};dbname={$dbname};charset=utf8mb4";
        
        $options = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_PERSISTENT => false
        ];
        
        try {
            $this->pdo = new PDO($dsn, $username, $password, $options);
            error_log("Database connection established successfully");
        } catch (PDOException $e) {
            error_log("Connection failed: " . $e->getMessage());
            throw new Exception("Database connection failed");
        }
    }
    
    /**
     * Get the singleton instance
     */
    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    
    /**
     * Get PDO connection
     */
    public function getConnection() {
        return $this->pdo;
    }
    
    // Prevent cloning
    private function __clone() {}
    
    // Prevent unserialization
    public function __wakeup() {
        throw new Exception("Cannot unserialize singleton");
    }
}

/**
 * Blog Post model class
 */
class BlogPost {
    private $db;
    
    public function __construct() {
        $this->db = Database::getInstance()->getConnection();
    }
    
    /**
     * Create the posts table
     */
    public function createTable() {
        $sql = "CREATE TABLE IF NOT EXISTS posts (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(200) NOT NULL,
            author VARCHAR(100) NOT NULL,
            content TEXT NOT NULL,
            category VARCHAR(50),
            published BOOLEAN DEFAULT 0,
            views INT DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_category (category),
            INDEX idx_published (published)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        
        try {
            $this->db->exec($sql);
            echo "Posts table created successfully\n";
            return true;
        } catch (PDOException $e) {
            error_log("Error creating table: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Insert a new blog post
     */
    public function create($title, $author, $content, $category, $published = false) {
        $sql = "INSERT INTO posts (title, author, content, category, published) 
                VALUES (:title, :author, :content, :category, :published)";
        
        try {
            $stmt = $this->db->prepare($sql);
            
            // Bind parameters
            $stmt->bindParam(':title', $title, PDO::PARAM_STR);
            $stmt->bindParam(':author', $author, PDO::PARAM_STR);
            $stmt->bindParam(':content', $content, PDO::PARAM_STR);
            $stmt->bindParam(':category', $category, PDO::PARAM_STR);
            $stmt->bindParam(':published', $published, PDO::PARAM_BOOL);
            
            $stmt->execute();
            $postId = $this->db->lastInsertId();
            
            echo "Post created successfully with ID: {$postId}\n";
            return $postId;
        } catch (PDOException $e) {
            error_log("Error creating post: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Retrieve posts with filtering and pagination
     */
    public function getPosts($category = null, $published = true, $limit = 10, $offset = 0) {
        $sql = "SELECT id, title, author, category, views, created_at 
                FROM posts 
                WHERE 1=1";
        
        $params = [];
        
        // Add category filter if provided
        if ($category !== null) {
            $sql .= " AND category = :category";
            $params[':category'] = $category;
        }
        
        // Add published filter
        $sql .= " AND published = :published";
        $params[':published'] = $published;
        
        // Add ordering and pagination
        $sql .= " ORDER BY created_at DESC LIMIT :limit OFFSET :offset";
        
        try {
            $stmt = $this->db->prepare($sql);
            
            // Bind parameters
            foreach ($params as $key => $value) {
                $stmt->bindValue($key, $value);
            }
            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
            $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
            
            $stmt->execute();
            $posts = $stmt->fetchAll();
            
            echo "Retrieved " . count($posts) . " posts\n";
            return $posts;
        } catch (PDOException $e) {
            error_log("Error retrieving posts: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Get a single post by ID and increment view count
     */
    public function getPostById($id) {
        try {
            // Start transaction
            $this->db->beginTransaction();
            
            //

SQL Full course

Module 1: Introduction to SQL and Relational Databases

Module 2: SQL Data Manipulation Language (DML)

Module 3: SQL Querying Multiple Tables

Module 4: Database Design and Data Integrity

Module 5: Advanced SQL Techniques

Module 6: SQL in Practice and Real-World Applications