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. While the procedural interface (mysqli_connect(), mysqli_query(), etc.) is also available, the object-oriented approach is generally preferred in modern PHP. 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:
How to use the MySQLi extension?
<?php
$mysqli = new mysqli("localhost", "username", "password", "database");
if ($mysqli->connect_error) {
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_error 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:
Advanced usage of PHP MYSQLi
<?php
$age = 18;
$stmt = $mysqli->prepare("SELECT name, email FROM users WHERE age > ?");
$stmt->bind_param("i", $age);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
echo "Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
}
$stmt->close();
?>Note: The get_result() method requires the mysqlnd driver. If your environment uses a different driver, you will need to bind result variables using bind_result() instead.
In this example, we use the prepare() method to prepare a SELECT statement with a placeholder for the age parameter. Prepared statements separate SQL logic from data, which effectively prevents SQL injection attacks by ensuring user input is never interpreted as executable code. 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.
Practice
What are important features of PHP MySQLi?