Database Connection Using mysqli Prepared Statements

In this document, we will create a PHP script that connects to a MySQL database using mysqli prepared statements. The script will include error handling and exception handling to ensure robustness and reliability.

<?php
// Database configuration
$host = 'localhost'; // Database host
$username = 'your_username'; // Database username
$password = 'your_password'; // Database password
$database = 'your_database'; // Database name

try {
    // Create a new mysqli instance
    $mysqli = new mysqli($host, $username, $password, $database);

    // Check for connection errors
    if ($mysqli->connect_error) {
        throw new Exception('Connection failed: ' . $mysqli->connect_error);
    }

    // Prepare a sample SQL statement
    $stmt = $mysqli->prepare("SELECT * FROM your_table WHERE your_column = ?");
    
    // Check if the statement was prepared successfully
    if (!$stmt) {
        throw new Exception('Statement preparation failed: ' . $mysqli->error);
    }

    // Bind parameters (example: string)
    $param = 'example_value';
    $stmt->bind_param('s', $param);

    // Execute the statement
    if (!$stmt->execute()) {
        throw new Exception('Statement execution failed: ' . $stmt->error);
    }

    // Fetch results
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        // Process each row
        echo 'Row: ' . json_encode($row) . PHP_EOL;
    }

    // Close the statement (optional, as we are not closing the connection)
    $stmt->close();

} catch (Exception $e) {
    // Handle exceptions
    echo 'Error: ' . $e->getMessage();
}

// Note: We do not close the connection because PHP 8 does this automatically.
?>

What we did:

In this code, we establish a connection to the database using mysqli. We handle potential errors during the connection and statement preparation using exceptions. This ensures that any issues are caught and reported, allowing for easier debugging and maintenance.

We did not close the sql connection because there is no need to. PHP does this automatically.

Happy Coding!

Leave a Reply

Your email address will not be published. Required fields are marked *