Handling MySQL errors in PHP using procedural style involves checking for errors during database operations and providing meaningful feedback. This ensures that issues can be diagnosed and resolved effectively.

Here’s a comprehensive guide on handling MySQL errors in PHP procedural style:

1. Connecting to the Database

When establishing a connection, you should check if the connection was successful. If not, handle the error appropriately.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test_db";

// Create the connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check if the connection is successful
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
?>

Explanation:

• mysqli_connect(): Establishes a connection to the database.
• mysqli_connect_error(): Returns a description of the last connection error if the connection fails.

2. Executing Queries

When executing SQL queries, check if the query was successful and handle errors if it wasn’t.

<?php
$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);

// Check if the query was successful
if (!$result) {
    die("Query failed: " . mysqli_error($conn));
}

// Process the result if the query was successful
while ($row = mysqli_fetch_assoc($result)) {
    echo "Username: " . htmlspecialchars($row['username']) . "<br>";
}
?>

Explanation:

• mysqli_query(): Executes the SQL query. If the query fails, it returns false.
• mysqli_error(): Returns a description of the last error for the most recent MySQLi function call.

3. Handling Prepared Statements

When using prepared statements, you should also handle errors for statement preparation, binding parameters, and execution.

<?php
// Prepare an SQL statement
$sql = "INSERT INTO users (username, email) VALUES (?, ?)";
$stmt = mysqli_prepare($conn, $sql);

// Check if the statement preparation was successful
if (!$stmt) {
    die("Statement preparation failed: " . mysqli_error($conn));
}

// Bind parameters
mysqli_stmt_bind_param($stmt, "ss", $username, $email);

// Execute the statement
if (!mysqli_stmt_execute($stmt)) {
    die("Statement execution failed: " . mysqli_stmt_error($stmt));
}

echo "Record inserted successfully.";

// Close the statement
mysqli_stmt_close($stmt);
?>

Explanation:

• mysqli_prepare(): Prepares the SQL statement for execution. If preparation fails, it returns false.
• mysqli_stmt_error(): Returns a description of the last error for the prepared statement.
• mysqli_stmt_bind_param(): Binds parameters to the prepared statement.
• mysqli_stmt_execute(): Executes the prepared statement. If execution fails, it returns false.

4. Closing the Connection

Close the database connection when it is no longer needed. Handle any errors that might occur when closing the connection.

<?php
// Close the connection
if (!mysqli_close($conn)) {
    die("Connection close failed: " . mysqli_error($conn));
}
?>

Explanation:

• mysqli_close(): Closes the connection. If closing fails, it returns false.

Summary of Error Handling

1. Connection Errors: Check if the connection was successful using mysqli_connect_error().
2. Query Errors: Check if a query was executed successfully using mysqli_error().
3. Prepared Statement Errors: Handle errors during statement preparation, parameter binding, and execution using mysqli_stmt_error().
4. Connection Closing Errors: Check if the connection was closed successfully.

Example: Full Error Handling Workflow

Here’s an example of a PHP script that connects to a database, performs a query, and handles errors appropriately:

<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test_db";

$conn = mysqli_connect($servername, $username, $password, $dbname);

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Perform a query
$sql = "SELECT id, username FROM users";
$result = mysqli_query($conn, $sql);

if (!$result) {
    die("Query failed: " . mysqli_error($conn));
}

// Fetch and display records
while ($row = mysqli_fetch_assoc($result)) {
    echo "ID: " . htmlspecialchars($row['id']) . " - Username: " . htmlspecialchars($row['username']) . "<br>";
}

// Close the connection
if (!mysqli_close($conn)) {
    die("Connection close failed: " . mysqli_error($conn));
}
?>

Best Practices

• Sanitize Input: Always sanitize user inputs to prevent SQL injection and other vulnerabilities.
• Use Prepared Statements: They provide better security and error handling.
• Handle Errors Gracefully: Provide user-friendly error messages and log detailed errors for debugging.
• Escape Output: Use htmlspecialchars() or similar functions to prevent XSS attacks when displaying data.