How to handle and log database queries and errors for debugging purposes in PDO in PHP?

To handle and log database queries and errors for debugging purposes in PDO in PHP, you can follow these steps:

Configure PDO error handling: Set the error handling mode of PDO to PDO::ERRMODE_EXCEPTION. This mode throws exceptions for all errors, allowing you to catch them and log them appropriately. Here's an example:

$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Wrap your database queries in a try-catch block: Surround your PDO database queries with a try-catch block to catch any exceptions that occur during query execution. Inside the catch block, you can log the error message and any other information you need. Here's an example:

try {
    $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
    $stmt->execute([$userId]);
    
    // Process the query results
} catch (PDOException $e) {
    // Log the error message
    error_log($e->getMessage());
}

Log the query parameters: To log the query parameters along with the error message, you can use the error_log() function to save the query parameters to your server's error log file. This can be helpful for debugging and finding issues with specific queries. Here's an example:

try {
    $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
    $stmt->execute([$userId]);
    
    // Process the query results
} catch (PDOException $e) {
    error_log($e->getMessage() . " Query parameters: " . json_encode([$userId]));
}

By following these steps, you can handle and log database queries and errors in PDO for debugging purposes.