How MySQLi Works: A Comprehensive Guide for PHP Developers

MySQLi (MySQL Improved) is a PHP extension that provides an interface for interacting with MySQL databases. It builds upon the original MySQL extension, introducing improvements and additional features. Here’s a basic overview of how MySQLi works:

1. Connection Establishment:

To use MySQLi, you first need to establish a connection to the MySQL database. This involves specifying the server hostname, username, password, and database name. The mysqli_connect() function is commonly used for this purpose. Here’s an example:

$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

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

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

2. Executing Queries:

Once the connection is established, you can execute SQL queries using MySQLi. There are two main methods for executing queries: procedural style and object-oriented style.

Procedural Style:

$sql = "SELECT * FROM your_table";
$result = mysqli_query($conn, $sql);

if ($result) {
    // Process the result set
    while ($row = mysqli_fetch_assoc($result)) {
        // Process each row
    }
} else {
    echo "Error: " . mysqli_error($conn);
}

// Close the result set
mysqli_free_result($result);

Object-oriented Style:

$sql = "SELECT * FROM your_table";
$result = $conn->query($sql);

if ($result) {
    // Process the result set
    while ($row = $result->fetch_assoc()) {
        // Process each row
    }
} else {
    echo "Error: " . $conn->error;
}

// Close the result set
$result->free_result();

3. Prepared Statements:

MySQLi supports prepared statements, which are useful for preventing SQL injection attacks and improving performance when executing the same SQL query multiple times with different parameter values. Here’s an example:

$sql = "INSERT INTO your_table (column1, column2) VALUES (?, ?)";
$stmt = $conn->prepare($sql);

// Bind parameters
$stmt->bind_param("ss", $value1, $value2);

// Set parameter values
$value1 = "some_value";
$value2 = "another_value";

// Execute the statement
$stmt->execute();

// Close the statement
$stmt->close();

4. Transactions:

MySQLi supports transactions, allowing you to group multiple SQL statements into a single transaction. This ensures that either all the statements are executed successfully, or none of them are. Here’s a basic example:

// Start a transaction
mysqli_begin_transaction($conn);

// SQL statements within the transaction
$sql1 = "UPDATE your_table SET column1 = value1 WHERE condition";
$sql2 = "INSERT INTO your_table (column2) VALUES (value2)";

// Execute the statements
mysqli_query($conn, $sql1);
mysqli_query($conn, $sql2);

// Commit the transaction
mysqli_commit($conn);

5. Error Handling:

MySQLi provides improved error handling compared to the original MySQL extension. You can check for errors using functions like mysqli_error() or $conn->error and handle them accordingly.

6. Closing the Connection:

After you’ve completed your database operations, it’s essential to close the MySQLi connection using the mysqli_close() function or $conn->close() method:

mysqli_close($conn);
// or
$conn->close();

This is a basic overview of how MySQLi works in PHP. It offers a flexible and secure way to interact with MySQL databases, supporting both procedural and object-oriented programming styles.

Browse MySQL freelancing jobs here –