PHP MySQLi

In this article, we will focus on the PHP MySQLi extension, which is used to interact with MySQL databases in PHP. We will provide you with an overview of the extension, how it works, and examples of its use.

Introduction to the MySQLi extension

The MySQLi extension is a PHP extension that provides an interface for interacting with MySQL databases in PHP. It is an improved version of the older MySQL extension and provides better security, performance, and functionality.

The MySQLi extension provides two different interfaces for interacting with MySQL databases: an object-oriented interface and a procedural interface. In this article, we will focus on the object-oriented interface.

How to use the MySQLi extension

Using the MySQLi extension is very simple. You just need to create a new MySQLi object, which represents a connection to a MySQL database. Here is an example:

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli->connect_error;
    exit();
}

$result = $mysqli->query("SELECT * FROM users");
while ($row = $result->fetch_assoc()) {
    echo "Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
}

$mysqli->close();
?>

In this example, we create a new MySQLi object with the mysqli() constructor, passing in the hostname, username, password, and database name as arguments. We then check if the connection was successful by checking the connect_errno property of the MySQLi object.

We then execute a SELECT query to retrieve all rows from a table named users. We iterate over the result set using a while loop and output the name and email of each user.

Finally, we close the MySQLi connection using the close() method of the MySQLi object.

Advanced usage

The MySQLi extension provides many other methods for interacting with MySQL databases. For example, you can use the prepare() method to prepare a statement with placeholders, and then execute the statement with different parameter values. Here is an example:

<?php
$stmt = $mysqli->prepare("SELECT name, email FROM users WHERE age > ?");
$stmt->bind_param("i", $age);
$age = 18;
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo "Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
}
$stmt->close();
?>

In this example, we use the prepare() method to prepare a SELECT statement with a placeholder for the age parameter. We then bind a value of 18 to the parameter using the bind_param() method. We execute the statement with the execute() method and retrieve the result set with the get_result() method. We then iterate over the result set and output the name and email of each user.

Conclusion

In conclusion, the MySQLi extension is a powerful tool for interacting with MySQL databases in PHP. By understanding how to use the extension and its advanced usage scenarios, you can take advantage of this feature to create powerful and secure database-driven applications in your PHP scripts.