Executing SQL Queries through PHP

Duration: 85 minutes

Lesson 3 of 8

Learning Objectives

Execute different types of SQL queries (SELECT, INSERT, UPDATE, DELETE)
Build dynamic queries safely
Implement batch query operations
Execute multiple queries efficiently
Handle query results properly
Optimize query performance
Execute stored procedures and functions
Manage database transactions

Query Execution Overview

Understanding how to execute SQL queries efficiently and safely is crucial for building robust PHP applications! ⚡

💡

Query Execution Best Practices

Use appropriate method:query() for SELECT, exec() for INSERT/UPDATE/DELETE in PDO
Handle errors:Always check for query execution errors
Optimize queries:Use EXPLAIN to analyze query performance
Batch operations:Group multiple operations when possible
Transaction control:Use transactions for related operations
Resource management:Free results when done

Basic Query Execution

graph TB QUERY[SQL Query] --> PREPARE{Prepared?} PREPARE -->|No| DIRECT[Direct Execution] PREPARE -->|Yes| STMT[Prepare Statement] DIRECT --> EXEC1[Execute Query] STMT --> BIND[Bind Parameters] BIND --> EXEC2[Execute Statement] EXEC1 --> RESULT[Process Result] EXEC2 --> RESULT RESULT --> CHECK{Success?} CHECK -->|Yes| FETCH[Fetch Data] CHECK -->|No| ERROR[Handle Error] style QUERY fill:#dbeafe style STMT fill:#dcfce7 style RESULT fill:#fef3c7 style ERROR fill:#fee2e2

Query Execution Methods

<?php
// ========================================
// PDO QUERY EXECUTION
// ========================================

class PDOQueryExecutor {
    private $pdo;
    private $queryLog = [];
    private $performanceLog = [];
    
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }
    
    /**
     * Execute SELECT query
     */
    public function select($sql, $params = []) {
        $startTime = microtime(true);
        
        try {
            if (empty($params)) {
                // Direct query for simple SELECT
                $stmt = $this->pdo->query($sql);
            } else {
                // Prepared statement for parameterized query
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute($params);
            }
            
            $result = $stmt->fetchAll();
            
            $this->logQuery($sql, $params, microtime(true) - $startTime);
            
            return [
                'success' => true,
                'data' => $result,
                'rowCount' => count($result)
            ];
            
        } catch (PDOException $e) {
            return $this->handleError($e, $sql, $params);
        }
    }
    
    /**
     * Execute INSERT query
     */
    public function insert($table, $data) {
        $startTime = microtime(true);
        
        try {
            // Build INSERT query
            $columns = array_keys($data);
            $placeholders = array_map(function($col) { return ":$col"; }, $columns);
            
            $sql = "INSERT INTO $table (" . implode(', ', $columns) . ") 
                    VALUES (" . implode(', ', $placeholders) . ")";
            
            $stmt = $this->pdo->prepare($sql);
            
            // Bind parameters
            foreach ($data as $key => $value) {
                $stmt->bindValue(":$key", $value);
            }
            
            $result = $stmt->execute();
            
            $this->logQuery($sql, $data, microtime(true) - $startTime);
            
            return [
                'success' => $result,
                'id' => $this->pdo->lastInsertId(),
                'affected' => $stmt->rowCount()
            ];
            
        } catch (PDOException $e) {
            return $this->handleError($e, $sql, $data);
        }
    }
    
    /**
     * Execute UPDATE query
     */
    public function update($table, $data, $where = []) {
        $startTime = microtime(true);
        
        try {
            // Build SET clause
            $setClauses = [];
            foreach ($data as $key => $value) {
                $setClauses[] = "$key = :set_$key";
            }
            
            // Build WHERE clause
            $whereClauses = [];
            foreach ($where as $key => $value) {
                $whereClauses[] = "$key = :where_$key";
            }
            
            $sql = "UPDATE $table SET " . implode(', ', $setClauses);
            if (!empty($whereClauses)) {
                $sql .= " WHERE " . implode(' AND ', $whereClauses);
            }
            
            $stmt = $this->pdo->prepare($sql);
            
            // Bind SET parameters
            foreach ($data as $key => $value) {
                $stmt->bindValue(":set_$key", $value);
            }
            
            // Bind WHERE parameters
            foreach ($where as $key => $value) {
                $stmt->bindValue(":where_$key", $value);
            }
            
            $result = $stmt->execute();
            
            $this->logQuery($sql, array_merge($data, $where), microtime(true) - $startTime);
            
            return [
                'success' => $result,
                'affected' => $stmt->rowCount()
            ];
            
        } catch (PDOException $e) {
            return $this->handleError($e, $sql, array_merge($data, $where));
        }
    }
    
    /**
     * Execute DELETE query
     */
    public function delete($table, $where = []) {
        $startTime = microtime(true);
        
        try {
            $sql = "DELETE FROM $table";
            
            if (!empty($where)) {
                $whereClauses = [];
                foreach ($where as $key => $value) {
                    $whereClauses[] = "$key = :$key";
                }
                $sql .= " WHERE " . implode(' AND ', $whereClauses);
            }
            
            $stmt = $this->pdo->prepare($sql);
            
            foreach ($where as $key => $value) {
                $stmt->bindValue(":$key", $value);
            }
            
            $result = $stmt->execute();
            
            $this->logQuery($sql, $where, microtime(true) - $startTime);
            
            return [
                'success' => $result,
                'affected' => $stmt->rowCount()
            ];
            
        } catch (PDOException $e) {
            return $this->handleError($e, $sql, $where);
        }
    }
    
    /**
     * Execute raw query
     */
    public function raw($sql, $params = []) {
        $startTime = microtime(true);
        
        try {
            if (empty($params)) {
                // Use exec for non-SELECT queries without params
                if (preg_match('/^(INSERT|UPDATE|DELETE|CREATE|DROP|ALTER)/i', $sql)) {
                    $affected = $this->pdo->exec($sql);
                    
                    $this->logQuery($sql, [], microtime(true) - $startTime);
                    
                    return [
                        'success' => true,
                        'affected' => $affected
                    ];
                } else {
                    // Use query for SELECT
                    $stmt = $this->pdo->query($sql);
                    $result = $stmt->fetchAll();
                    
                    $this->logQuery($sql, [], microtime(true) - $startTime);
                    
                    return [
                        'success' => true,
                        'data' => $result
                    ];
                }
            } else {
                // Use prepared statement
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute($params);
                
                if (preg_match('/^SELECT/i', $sql)) {
                    $result = $stmt->fetchAll();
                    
                    $this->logQuery($sql, $params, microtime(true) - $startTime);
                    
                    return [
                        'success' => true,
                        'data' => $result
                    ];
                } else {
                    $this->logQuery($sql, $params, microtime(true) - $startTime);
                    
                    return [
                        'success' => true,
                        'affected' => $stmt->rowCount()
                    ];
                }
            }
            
        } catch (PDOException $e) {
            return $this->handleError($e, $sql, $params);
        }
    }
    
    /**
     * Log query for debugging
     */
    private function logQuery($sql, $params, $executionTime) {
        $this->queryLog[] = [
            'sql' => $sql,
            'params' => $params,
            'execution_time' => round($executionTime * 1000, 2) . ' ms',
            'timestamp' => date('Y-m-d H:i:s')
        ];
        
        // Log slow queries
        if ($executionTime > 1) { // More than 1 second
            $this->performanceLog[] = [
                'sql' => $sql,
                'time' => $executionTime,
                'timestamp' => date('Y-m-d H:i:s')
            ];
        }
    }
    
    /**
     * Handle query errors
     */
    private function handleError($e, $sql, $params) {
        $error = [
            'success' => false,
            'error' => $e->getMessage(),
            'code' => $e->getCode(),
            'sql' => $sql,
            'params' => $params
        ];
        
        // Log error
        error_log("Query Error: " . json_encode($error));
        
        return $error;
    }
    
    /**
     * Get query log
     */
    public function getQueryLog() {
        return $this->queryLog;
    }
    
    /**
     * Get slow queries
     */
    public function getSlowQueries() {
        return $this->performanceLog;
    }
}

// ========================================
// MYSQLI QUERY EXECUTION
// ========================================

class MySQLiQueryExecutor {
    private $mysqli;
    private $queryLog = [];
    
    public function __construct(mysqli $mysqli) {
        $this->mysqli = $mysqli;
    }
    
    /**
     * Execute query with result
     */
    public function query($sql, $params = []) {
        $startTime = microtime(true);
        
        try {
            if (empty($params)) {
                // Direct query
                $result = $this->mysqli->query($sql);
                
                if ($result === false) {
                    throw new Exception($this->mysqli->error);
                }
                
                // Process result based on query type
                if ($result instanceof mysqli_result) {
                    $data = [];
                    while ($row = $result->fetch_assoc()) {
                        $data[] = $row;
                    }
                    $result->free();
                    
                    $this->logQuery($sql, [], microtime(true) - $startTime);
                    
                    return [
                        'success' => true,
                        'data' => $data,
                        'num_rows' => count($data)
                    ];
                } else {
                    // Non-SELECT query
                    $this->logQuery($sql, [], microtime(true) - $startTime);
                    
                    return [
                        'success' => true,
                        'affected_rows' => $this->mysqli->affected_rows,
                        'insert_id' => $this->mysqli->insert_id
                    ];
                }
            } else {
                // Prepared statement
                return $this->executeWithParams($sql, $params);
            }
            
        } catch (Exception $e) {
            return [
                'success' => false,
                'error' => $e->getMessage()
            ];
        }
    }
    
    /**
     * Execute with parameters (prepared statement)
     */
    private function executeWithParams($sql, $params) {
        $stmt = $this->mysqli->prepare($sql);
        
        if (!$stmt) {
            throw new Exception($this->mysqli->error);
        }
        
        // Determine types
        $types = '';
        foreach ($params as $param) {
            if (is_int($param)) {
                $types .= 'i';
            } elseif (is_float($param)) {
                $types .= 'd';
            } else {
                $types .= 's';
            }
        }
        
        // Bind parameters
        if (!empty($params)) {
            $stmt->bind_param($types, ...$params);
        }
        
        // Execute
        if (!$stmt->execute()) {
            throw new Exception($stmt->error);
        }
        
        // Get result
        $result = $stmt->get_result();
        
        if ($result) {
            // SELECT query
            $data = [];
            while ($row = $result->fetch_assoc()) {
                $data[] = $row;
            }
            
            return [
                'success' => true,
                'data' => $data,
                'num_rows' => $result->num_rows
            ];
        } else {
            // Non-SELECT query
            return [
                'success' => true,
                'affected_rows' => $stmt->affected_rows,
                'insert_id' => $this->mysqli->insert_id
            ];
        }
    }
    
    /**
     * Execute multiple queries
     */
    public function multiQuery($sql) {
        $results = [];
        
        if ($this->mysqli->multi_query($sql)) {
            do {
                // Store result
                if ($result = $this->mysqli->store_result()) {
                    $data = [];
                    while ($row = $result->fetch_assoc()) {
                        $data[] = $row;
                    }
                    $results[] = $data;
                    $result->free();
                } else {
                    // Non-SELECT query
                    $results[] = [
                        'affected_rows' => $this->mysqli->affected_rows
                    ];
                }
                
                // Check for more results
                $moreResults = $this->mysqli->more_results();
                
                if ($moreResults) {
                    $this->mysqli->next_result();
                }
                
            } while ($moreResults);
        }
        
        return $results;
    }
    
    /**
     * Call stored procedure
     */
    public function callProcedure($procedure, $params = []) {
        $placeholders = array_fill(0, count($params), '?');
        $sql = "CALL $procedure(" . implode(',', $placeholders) . ")";
        
        return $this->query($sql, $params);
    }
    
    /**
     * Log query
     */
    private function logQuery($sql, $params, $executionTime) {
        $this->queryLog[] = [
            'sql' => $sql,
            'params' => $params,
            'execution_time' => round($executionTime * 1000, 2) . ' ms',
            'timestamp' => date('Y-m-d H:i:s')
        ];
    }
}

// ========================================
// BATCH OPERATIONS
// ========================================

class BatchQueryExecutor {
    private $pdo;
    private $batchSize = 1000;
    
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }
    
    /**
     * Batch insert
     */
    public function batchInsert($table, $data, $columns = null) {
        if (empty($data)) {
            return ['success' => false, 'error' => 'No data provided'];
        }
        
        // Get columns from first row if not provided
        if ($columns === null) {
            $columns = array_keys($data[0]);
        }
        
        $totalInserted = 0;
        $chunks = array_chunk($data, $this->batchSize);
        
        try {
            $this->pdo->beginTransaction();
            
            foreach ($chunks as $chunk) {
                $sql = $this->buildBatchInsertQuery($table, $columns, count($chunk));
                $stmt = $this->pdo->prepare($sql);
                
                // Flatten data for binding
                $values = [];
                foreach ($chunk as $row) {
                    foreach ($columns as $col) {
                        $values[] = $row[$col] ?? null;
                    }
                }
                
                $stmt->execute($values);
                $totalInserted += $stmt->rowCount();
            }
            
            $this->pdo->commit();
            
            return [
                'success' => true,
                'inserted' => $totalInserted
            ];
            
        } catch (PDOException $e) {
            $this->pdo->rollBack();
            
            return [
                'success' => false,
                'error' => $e->getMessage()
            ];
        }
    }
    
    /**
     * Build batch insert query
     */
    private function buildBatchInsertQuery($table, $columns, $rowCount) {
        $columnList = implode(', ', $columns);
        $placeholders = '(' . implode(', ', array_fill(0, count($columns), '?')) . ')';
        $values = implode(', ', array_fill(0, $rowCount, $placeholders));
        
        return "INSERT INTO $table ($columnList) VALUES $values";
    }
    
    /**
     * Batch update
     */
    public function batchUpdate($table, $data, $keyColumn) {
        if (empty($data)) {
            return ['success' => false, 'error' => 'No data provided'];
        }
        
        $totalUpdated = 0;
        
        try {
            $this->pdo->beginTransaction();
            
            foreach ($data as $row) {
                if (!isset($row[$keyColumn])) {
                    continue;
                }
                
                $keyValue = $row[$keyColumn];
                unset($row[$keyColumn]);
                
                $setClauses = [];
                foreach ($row as $col => $value) {
                    $setClauses[] = "$col = ?";
                }
                
                $sql = "UPDATE $table SET " . implode(', ', $setClauses) . 
                       " WHERE $keyColumn = ?";
                
                $stmt = $this->pdo->prepare($sql);
                
                $values = array_values($row);
                $values[] = $keyValue;
                
                $stmt->execute($values);
                $totalUpdated += $stmt->rowCount();
            }
            
            $this->pdo->commit();
            
            return [
                'success' => true,
                'updated' => $totalUpdated
            ];
            
        } catch (PDOException $e) {
            $this->pdo->rollBack();
            
            return [
                'success' => false,
                'error' => $e->getMessage()
            ];
        }
    }
    
    /**
     * Batch delete
     */
    public function batchDelete($table, $column, $values) {
        if (empty($values)) {
            return ['success' => false, 'error' => 'No values provided'];
        }
        
        try {
            $placeholders = str_repeat('?,', count($values) - 1) . '?';
            $sql = "DELETE FROM $table WHERE $column IN ($placeholders)";
            
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($values);
            
            return [
                'success' => true,
                'deleted' => $stmt->rowCount()
            ];
            
        } catch (PDOException $e) {
            return [
                'success' => false,
                'error' => $e->getMessage()
            ];
        }
    }
}

// ========================================
// USAGE EXAMPLES
// ========================================

// PDO Examples
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');
$executor = new PDOQueryExecutor($pdo);

// SELECT
$result = $executor->select("SELECT * FROM users WHERE status = ?", ['active']);
print_r($result);

// INSERT
$result = $executor->insert('users', [
    'name' => 'John Doe',
    'email' => 'john@example.com',
    'status' => 'active'
]);
echo "Inserted ID: " . $result['id'];

// UPDATE
$result = $executor->update('users', 
    ['status' => 'inactive'],
    ['id' => 123]
);
echo "Updated rows: " . $result['affected'];

// DELETE
$result = $executor->delete('users', ['id' => 123]);
echo "Deleted rows: " . $result['affected'];

// Batch operations
$batch = new BatchQueryExecutor($pdo);

// Batch insert
$data = [
    ['name' => 'User 1', 'email' => 'user1@example.com'],
    ['name' => 'User 2', 'email' => 'user2@example.com'],
    // ... more data
];
$result = $batch->batchInsert('users', $data);

// View query log
print_r($executor->getQueryLog());
print_r($executor->getSlowQueries());

Advanced Query Execution

Transaction Management and Optimization

<?php
// ========================================
// TRANSACTION MANAGER
// ========================================

class TransactionManager {
    private $pdo;
    private $transactionLevel = 0;
    private $rollbackOnly = false;
    
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }
    
    /**
     * Begin transaction with nesting support
     */
    public function beginTransaction() {
        if ($this->transactionLevel == 0) {
            $this->pdo->beginTransaction();
        } else {
            $this->pdo->exec("SAVEPOINT LEVEL{$this->transactionLevel}");
        }
        
        $this->transactionLevel++;
        
        return $this->transactionLevel;
    }
    
    /**
     * Commit transaction
     */
    public function commit() {
        if ($this->transactionLevel == 0) {
            throw new Exception("No active transaction");
        }
        
        $this->transactionLevel--;
        
        if ($this->transactionLevel == 0) {
            if ($this->rollbackOnly) {
                $this->pdo->rollBack();
                $this->rollbackOnly = false;
                throw new Exception("Transaction marked for rollback only");
            } else {
                $this->pdo->commit();
            }
        } else {
            if (!$this->rollbackOnly) {
                $this->pdo->exec("RELEASE SAVEPOINT LEVEL{$this->transactionLevel}");
            }
        }
    }
    
    /**
     * Rollback transaction
     */
    public function rollback() {
        if ($this->transactionLevel == 0) {
            throw new Exception("No active transaction");
        }
        
        $this->transactionLevel--;
        
        if ($this->transactionLevel == 0) {
            $this->pdo->rollBack();
            $this->rollbackOnly = false;
        } else {
            $this->pdo->exec("ROLLBACK TO SAVEPOINT LEVEL{$this->transactionLevel}");
            $this->rollbackOnly = true;
        }
    }
    
    /**
     * Execute in transaction
     */
    public function transactional(callable $callback) {
        $this->beginTransaction();
        
        try {
            $result = $callback($this->pdo);
            $this->commit();
            
            return $result;
            
        } catch (Exception $e) {
            $this->rollback();
            throw $e;
        }
    }
}

// ========================================
// QUERY BUILDER
// ========================================

class QueryBuilder {
    private $type;
    private $table;
    private $columns = ['*'];
    private $where = [];
    private $joins = [];
    private $groupBy = [];
    private $having = [];
    private $orderBy = [];
    private $limit;
    private $offset;
    private $bindings = [];
    
    /**
     * SELECT query
     */
    public function select($columns = ['*']) {
        $this->type = 'SELECT';
        $this->columns = is_array($columns) ? $columns : [$columns];
        return $this;
    }
    
    /**
     * FROM table
     */
    public function from($table) {
        $this->table = $table;
        return $this;
    }
    
    /**
     * JOIN clause
     */
    public function join($table, $first, $operator, $second, $type = 'INNER') {
        $this->joins[] = [
            'type' => $type,
            'table' => $table,
            'first' => $first,
            'operator' => $operator,
            'second' => $second
        ];
        return $this;
    }
    
    /**
     * WHERE clause
     */
    public function where($column, $operator, $value = null) {
        if ($value === null) {
            $value = $operator;
            $operator = '=';
        }
        
        $this->where[] = [
            'type' => 'AND',
            'column' => $column,
            'operator' => $operator,
            'value' => $value
        ];
        
        return $this;
    }
    
    /**
     * OR WHERE clause
     */
    public function orWhere($column, $operator, $value = null) {
        if ($value === null) {
            $value = $operator;
            $operator = '=';
        }
        
        $this->where[] = [
            'type' => 'OR',
            'column' => $column,
            'operator' => $operator,
            'value' => $value
        ];
        
        return $this;
    }
    
    /**
     * WHERE IN clause
     */
    public function whereIn($column, array $values) {
        $this->where[] = [
            'type' => 'AND',
            'column' => $column,
            'operator' => 'IN',
            'value' => $values
        ];
        
        return $this;
    }
    
    /**
     * GROUP BY clause
     */
    public function groupBy($columns) {
        $this->groupBy = is_array($columns) ? $columns : [$columns];
        return $this;
    }
    
    /**
     * HAVING clause
     */
    public function having($column, $operator, $value = null) {
        if ($value === null) {
            $value = $operator;
            $operator = '=';
        }
        
        $this->having[] = [
            'column' => $column,
            'operator' => $operator,
            'value' => $value
        ];
        
        return $this;
    }
    
    /**
     * ORDER BY clause
     */
    public function orderBy($column, $direction = 'ASC') {
        $this->orderBy[] = [
            'column' => $column,
            'direction' => strtoupper($direction)
        ];
        
        return $this;
    }
    
    /**
     * LIMIT clause
     */
    public function limit($limit, $offset = null) {
        $this->limit = $limit;
        if ($offset !== null) {
            $this->offset = $offset;
        }
        return $this;
    }
    
    /**
     * Build SQL query
     */
    public function toSql() {
        $sql = $this->type . ' ';
        
        // SELECT columns
        $sql .= implode(', ', $this->columns);
        
        // FROM
        $sql .= " FROM {$this->table}";
        
        // JOINs
        foreach ($this->joins as $join) {
            $sql .= " {$join['type']} JOIN {$join['table']} ON ";
            $sql .= "{$join['first']} {$join['operator']} {$join['second']}";
        }
        
        // WHERE
        if (!empty($this->where)) {
            $sql .= " WHERE ";
            $whereClauses = [];
            
            foreach ($this->where as $i => $condition) {
                $clause = '';
                
                if ($i > 0) {
                    $clause .= " {$condition['type']} ";
                }
                
                if ($condition['operator'] === 'IN') {
                    $placeholders = array_fill(0, count($condition['value']), '?');
                    $clause .= "{$condition['column']} IN (" . implode(', ', $placeholders) . ")";
                    $this->bindings = array_merge($this->bindings, $condition['value']);
                } else {
                    $clause .= "{$condition['column']} {$condition['operator']} ?";
                    $this->bindings[] = $condition['value'];
                }
                
                $whereClauses[] = $clause;
            }
            
            $sql .= implode('', $whereClauses);
        }
        
        // GROUP BY
        if (!empty($this->groupBy)) {
            $sql .= " GROUP BY " . implode(', ', $this->groupBy);
        }
        
        // HAVING
        if (!empty($this->having)) {
            $sql .= " HAVING ";
            $havingClauses = [];
            
            foreach ($this->having as $condition) {
                $havingClauses[] = "{$condition['column']} {$condition['operator']} ?";
                $this->bindings[] = $condition['value'];
            }
            
            $sql .= implode(' AND ', $havingClauses);
        }
        
        // ORDER BY
        if (!empty($this->orderBy)) {
            $sql .= " ORDER BY ";
            $orderClauses = [];
            
            foreach ($this->orderBy as $order) {
                $orderClauses[] = "{$order['column']} {$order['direction']}";
            }
            
            $sql .= implode(', ', $orderClauses);
        }
        
        // LIMIT
        if ($this->limit) {
            $sql .= " LIMIT {$this->limit}";
            
            if ($this->offset) {
                $sql .= " OFFSET {$this->offset}";
            }
        }
        
        return $sql;
    }
    
    /**
     * Get bindings
     */
    public function getBindings() {
        return $this->bindings;
    }
    
    /**
     * Execute query
     */
    public function execute(PDO $pdo) {
        $sql = $this->toSql();
        $stmt = $pdo->prepare($sql);
        $stmt->execute($this->bindings);
        
        return $stmt->fetchAll();
    }
}

// ========================================
// QUERY OPTIMIZATION
// ========================================

class QueryOptimizer {
    private $pdo;
    
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }
    
    /**
     * Analyze query performance
     */
    public function explain($sql) {
        $stmt = $this->pdo->query("EXPLAIN $sql");
        return $stmt->fetchAll();
    }
    
    /**
     * Analyze query with extended information
     */
    public function explainAnalyze($sql) {
        $stmt = $this->pdo->query("EXPLAIN ANALYZE $sql");
        return $stmt->fetchAll();
    }
    
    /**
     * Get query execution plan
     */
    public function getExecutionPlan($sql) {
        $explain = $this->explain($sql);
        
        $plan = [];
        foreach ($explain as $row) {
            $plan[] = [
                'table' => $row['table'],
                'type' => $row['type'],
                'possible_keys' => $row['possible_keys'],
                'key' => $row['key'],
                'rows' => $row['rows'],
                'extra' => $row['Extra']
            ];
        }
        
        return $plan;
    }
    
    /**
     * Suggest optimizations
     */
    public function suggestOptimizations($sql) {
        $plan = $this->getExecutionPlan($sql);
        $suggestions = [];
        
        foreach ($plan as $step) {
            // Check for full table scan
            if ($step['type'] === 'ALL') {
                $suggestions[] = "Consider adding index on {$step['table']} for WHERE/JOIN conditions";
            }
            
            // Check for filesort
            if (strpos($step['extra'], 'filesort') !== false) {
                $suggestions[] = "Query uses filesort on {$step['table']}, consider adding index for ORDER BY";
            }
            
            // Check for temporary table
            if (strpos($step['extra'], 'temporary') !== false) {
                $suggestions[] = "Query creates temporary table for {$step['table']}, review GROUP BY/ORDER BY";
            }
            
            // Check for no index used
            if (empty($step['key']) && !empty($step['possible_keys'])) {
                $suggestions[] = "Index exists but not used for {$step['table']}, consider FORCE INDEX";
            }
        }
        
        return $suggestions;
    }
}

// ========================================
// USAGE EXAMPLES
// ========================================

$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');

// Transaction example
$tm = new TransactionManager($pdo);
$result = $tm->transactional(function($pdo) {
    $pdo->exec("INSERT INTO users (name) VALUES ('John')");
    $pdo->exec("INSERT INTO logs (action) VALUES ('User created')");
    return true;
});

// Query builder example
$query = new QueryBuilder();
$sql = $query->select(['id', 'name', 'email'])
    ->from('users')
    ->join('profiles', 'users.id', '=', 'profiles.user_id')
    ->where('status', 'active')
    ->whereIn('role', ['admin', 'moderator'])
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->toSql();

echo $sql;
$results = $query->execute($pdo);

// Query optimization
$optimizer = new QueryOptimizer($pdo);
$plan = $optimizer->explain("SELECT * FROM large_table WHERE status = 'active'");
$suggestions = $optimizer->suggestOptimizations("SELECT * FROM large_table WHERE status = 'active'");
print_r($suggestions);

Practice Exercise

💻

Query Execution Challenges

Complete these query execution tasks:

Execute a complex SELECT with JOINs
Implement batch insert of 1000 records
Create a transaction with multiple operations
Build and execute dynamic queries safely
Execute stored procedures with parameters
Implement query result caching
Create a query performance monitor
Build a query builder with method chaining

PHP WordPress Development

Course Progress