Assignments On Class 18: Working with Databases - Part 4
Here are some assignments based on the topic of error handling in database operations, along with solutions and explanations. These assignments are designed to reinforce the concepts covered in the lecture on error handling in PHP database interactions.
Assignment 1: Connect to a Database and Handle Connection Errors
Task: Create a PHP script that attempts to connect to a MySQL database. If the connection fails, display an error message using mysqli_connect_error().
Solution:
php
Copy code
<?php
// Database credentials
$host = 'localhost';
$username = 'username'; // Replace with your username
$password = 'password'; // Replace with your password
$dbname = 'testdb'; // Replace with your database name
// Create connection
$mysqli = new mysqli($host, $username, $password, $dbname);
// Check connection
if ($mysqli->connect_error) {
die('Connection Error: ' . mysqli_connect_error());
} else {
echo 'Connected successfully to the database.';
}
// Close the connection
$mysqli->close();
?>
Explanation:
- We use the mysqli class to create a new connection.
- After attempting to connect, we check for any connection errors using mysqli_connect_error(). If there is an error, we terminate the script and display the error message.
- If the connection is successful, we output a success message.
Assignment 2: Query Execution and Error Handling
Task: Write a PHP script that executes a SQL query to select data from a non-existent table. Use error handling to display a custom error message if the query fails.
Solution:
php
Copy code
<?php
$host = 'localhost';
$username = 'username'; // Replace with your username
$password = 'password'; // Replace with your password
$dbname = 'testdb'; // Replace with your database name
// Create connection
$mysqli = new mysqli($host, $username, $password, $dbname);
// Check connection
if ($mysqli->connect_error) {
die('Connection Error: ' . mysqli_connect_error());
}
// Execute a query
$result = $mysqli->query('SELECT * FROM nonexistent_table');
if (!$result) {
echo 'Query Error: ' . $mysqli->error; // Display the error message
} else {
// Fetch and display results (if the query was successful)
while ($row = $result->fetch_assoc()) {
echo 'Data: ' . $row['column_name'] . '<br>'; // Replace 'column_name' with your actual column name
}
}
// Close the connection
$mysqli->close();
?>
Explanation:
- We attempt to execute a SQL query to select data from a table that does not exist.
- If the query fails, we use $mysqli->error to get the error message and display it.
- If the query succeeds, we fetch and display the results (this part won't execute in this case due to the nonexistent table).
Assignment 3: Using PDO with Error Handling
Task: Create a PHP script using PDO to connect to a MySQL database. Implement error handling using try-catch blocks to manage any potential exceptions during the connection and query execution.
Solution:
php
Copy code
<?php
$dsn = 'mysql:host=localhost;dbname=testdb';
$username = 'username'; // Replace with your username
$password = 'password'; // Replace with your password
try {
// Create a new PDO instance
$pdo = new PDO($dsn, $username, $password);
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Execute a query
$stmt = $pdo->query('SELECT * FROM nonexistent_table');
// Fetch and display results (if the query was successful)
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo 'Data: ' . $row['column_name'] . '<br>'; // Replace 'column_name' with your actual column name
}
} catch (PDOException $e) {
// Handle the error
echo 'Database Error: ' . $e->getMessage();
}
?>
Explanation:
- We create a new PDO instance and set the error mode to ERRMODE_EXCEPTION, which will throw exceptions for any database errors.
- We use a try-catch block to catch any exceptions that may occur during the connection or query execution.
- If an exception is thrown, we catch it and display the error message using $e->getMessage().
Assignment 4: Custom Error Handling Function
Task: Develop a PHP script that includes a custom error handling function to manage database errors. This function should log errors to a file and display a user-friendly message.
Solution:
php
Copy code
<?php
function handleError($error) {
// Log error to a file
file_put_contents('error_log.txt', date('Y-m-d H:i:s') . ' - ' . $error . PHP_EOL, FILE_APPEND);
// Display a user-friendly message
echo 'An error occurred while processing your request. Please try again later.';
}
$host = 'localhost';
$username = 'username'; // Replace with your username
$password = 'password'; // Replace with your password
$dbname = 'testdb'; // Replace with your database name
$mysqli = new mysqli($host, $username, $password, $dbname);
// Check connection
if ($mysqli->connect_error) {
handleError('Connection Error: ' . mysqli_connect_error());
exit();
}
// Execute a query
$result = $mysqli->query('SELECT * FROM nonexistent_table');
if (!$result) {
handleError('Query Error: ' . $mysqli->error);
} else {
// Fetch and display results (if the query was successful)
while ($row = $result->fetch_assoc()) {
echo 'Data: ' . $row['column_name'] . '<br>'; // Replace 'column_name' with your actual column name
}
}
// Close the connection
$mysqli->close();
?>
Explanation:
- We define a custom error handling function, handleError(), which logs errors to a file named error_log.txt and displays a user-friendly message.
- We check the connection and query execution as before. If there is an error, we call the handleError() function with the appropriate error message.
- This approach centralizes error handling, making it easier to manage and modify in the future.
Conclusion
These assignments cover various aspects of error handling in database operations using PHP, including connection handling, query execution, using PDO, and implementing custom error handling functions. By completing these assignments, students will gain practical experience in managing errors effectively in their applications.
4o mini
You said:
write some more
ChatGPT said:
ChatGPT
Here are additional assignments focusing on error handling techniques in PHP for database operations, along with their solutions and explanations.
Assignment 5: Catching Multiple Exceptions
Task: Create a PHP script that connects to a MySQL database and executes multiple queries. Implement error handling using try-catch blocks to catch different types of exceptions (e.g., connection and query execution).
Solution:
php
Copy code
<?php
$dsn = 'mysql:host=localhost;dbname=testdb';
$username = 'username'; // Replace with your username
$password = 'password'; // Replace with your password
try {
// Create a new PDO instance
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Execute the first query
$stmt1 = $pdo->query('SELECT * FROM users'); // Assuming this table exists
while ($row = $stmt1->fetch(PDO::FETCH_ASSOC)) {
echo 'User: ' . $row['username'] . '<br>'; // Assuming 'username' is a column
}
// Execute a second query on a nonexistent table
$stmt2 = $pdo->query('SELECT * FROM nonexistent_table');
while ($row = $stmt2->fetch(PDO::FETCH_ASSOC)) {
echo 'Data: ' . $row['column_name'] . '<br>'; // Replace 'column_name' with your actual column name
}
} catch (PDOException $e) {
// Handle the database error
echo 'Database Error: ' . $e->getMessage();
}
?>
Explanation:
- This script demonstrates how to handle multiple queries within a single try-catch block.
- If any query fails, the catch block will handle the exception and display the error message. The first query is assumed to succeed, while the second one is expected to fail due to a nonexistent table.
Assignment 6: Rollback Transactions on Errors
Task: Create a PHP script that performs multiple database operations within a transaction. Implement error handling to rollback the transaction if any operation fails.
Solution:
php
Copy code
<?php
$dsn = 'mysql:host=localhost;dbname=testdb';
$username = 'username'; // Replace with your username
$password = 'password'; // Replace with your password
try {
// Create a new PDO instance
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Begin a transaction
$pdo->beginTransaction();
// First insert query
$pdo->exec("INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com')");
// Second insert query that might fail (assuming the email must be unique)
$pdo->exec("INSERT INTO users (username, email) VALUES ('user2', 'user1@example.com')");
// Commit the transaction
$pdo->commit();
echo 'Transaction completed successfully.';
} catch (PDOException $e) {
// Rollback the transaction on error
$pdo->rollBack();
echo 'Transaction failed: ' . $e->getMessage();
}
?>
Explanation:
- The script starts a transaction before executing any database operations.
- If an error occurs during any of the operations, the catch block rolls back the transaction to maintain data integrity.
- The first insert should succeed, but the second might fail if it violates a unique constraint (e.g., duplicate email), triggering the rollback.
Assignment 7: Custom Error Handling with Logging
Task: Write a PHP script that connects to a MySQL database and executes a query. If an error occurs, log the error details to a file along with a timestamp.
Solution:
php
Copy code
<?php
function logError($error) {
$logFile = 'error_log.txt'; // Log file path
$timestamp = date('Y-m-d H:i:s'); // Current timestamp
file_put_contents($logFile, "$timestamp - $error" . PHP_EOL, FILE_APPEND);
}
$dsn = 'mysql:host=localhost;dbname=testdb';
$username = 'username'; // Replace with your username
$password = 'password'; // Replace with your password
try {
// Create a new PDO instance
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Execute a query
$stmt = $pdo->query('SELECT * FROM nonexistent_table');
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo 'Data: ' . $row['column_name'] . '<br>'; // Replace 'column_name' with your actual column name
}
} catch (PDOException $e) {
// Log the error and display a user-friendly message
logError('Database Error: ' . $e->getMessage());
echo 'An error occurred. Please try again later.';
}
?>
Explanation:
- The logError() function appends error details, along with a timestamp, to a log file called error_log.txt.
- If an error occurs during the database query, the error is logged, and a user-friendly message is displayed to the user.
Assignment 8: Implementing a User-Friendly Error Message
Task: Write a PHP script that connects to a MySQL database and executes a query. If the query fails, provide a user-friendly error message without exposing sensitive information.
Solution:
php
Copy code
<?php
$dsn = 'mysql:host=localhost;dbname=testdb';
$username = 'username'; // Replace with your username
$password = 'password'; // Replace with your password
try {
// Create a new PDO instance
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Execute a query
$stmt = $pdo->query('SELECT * FROM nonexistent_table');
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo 'Data: ' . $row['column_name'] . '<br>'; // Replace 'column_name' with your actual column name
}
} catch (PDOException $e) {
// Display a user-friendly error message without exposing sensitive details
echo 'Oops! Something went wrong. Please try again later.';
// Log the detailed error for debugging (optional)
error_log('Database Error: ' . $e->getMessage()); // Logs detailed error in server log
}
?>
Explanation:
- The catch block provides a generic error message to the user, preventing the exposure of sensitive information that could be exploited.
- The actual error message is logged in the server logs using error_log(), which can be useful for debugging.
Assignment 9: Validating Data Before Query Execution
Task: Create a PHP script that validates user input before executing a database query. If the validation fails, handle the error gracefully.
Solution:
php
Copy code
<?php
function validateInput($username) {
// Check if username is empty
return !empty($username);
}
$dsn = 'mysql:host=localhost;dbname=testdb';
$username = 'username'; // Replace with your username
$password = 'password'; // Replace with your password
$userInput = ' '; // Simulate user input
if (!validateInput(trim($userInput))) {
echo 'Error: Username cannot be empty.';
exit();
}
try {
// Create a new PDO instance
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Execute a query with validated input
$stmt = $pdo->prepare('INSERT INTO users (username) VALUES (:username)');
$stmt->execute(['username' => $userInput]);
echo 'User added successfully!';
} catch (PDOException $e) {
echo 'Database Error: ' . $e->getMessage();
}
?>
Explanation:
- The script includes a validation function to check if the username is empty.
- If the validation fails, an error message is displayed, and the script exits without attempting to execute the database query.
- This ensures that invalid data does not reach the database, helping to prevent errors.
Assignment 10: Advanced Logging with Error Levels
Task: Develop a PHP script that logs database errors with different severity levels (e.g., INFO, WARNING, ERROR) based on the type of error encountered.
Solution:
php
Copy code
<?php
function logError($message, $level = 'ERROR') {
$logFile = 'error_log.txt';
$timestamp = date('Y-m-d H:i:s');
file_put_contents($logFile, "[$level] $timestamp - $message" . PHP_EOL, FILE_APPEND);
}
$dsn = 'mysql:host=localhost;dbname=testdb';
$username = 'username'; // Replace with your username
$password = 'password'; // Replace with your password
try {
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Example of a successful operation
$stmt = $pdo->query('SELECT * FROM users');
// Log INFO level message
logError('Query executed successfully.', 'INFO');
} catch (PDOException $e) {
// Determine error level based on the error code
if ($e->getCode() == 1049) { // Example: Unknown database
logError('Unknown database error: ' . $e->getMessage(), 'ERROR');
} else {
logError('Database error occurred: ' . $e->getMessage(), 'WARNING');
}
echo 'An error occurred. Please try again later.';
}
?>
Explanation:
- The logError() function logs errors with different severity levels, allowing for better categorization of issues.
- The script checks the error code in the catch block to determine whether to log it as an ERROR or WARNING, providing more insight into the nature of the error.