This article will guide you through the process of installing the MySQLi extension and configuring PHP to use it.
What is MySQLi?
Before diving into the installation process, it’s important to understand what MySQLi is and why it’s useful. MySQLi is an extension for PHP that improves upon the original MySQL extension. It supports both procedural and object-oriented programming, making it versatile and flexible. Key features of MySQLi include:
Prepared Statements: Enhance security by preventing SQL injection.
Multiple Statements: Execute multiple SQL queries in one call.
Transactions: Ensure data integrity with commit and rollback functionality.
Object-Oriented Interface: Organize code more effectively using classes and objects.
Installing MySQLi Extension
Checking for MySQLi Extension
First, you need to check if the MySQLi extension is already installed and enabled on your PHP setup. You can do this by creating a simple PHP script:
<?php
phpinfo();
?>
Save this script as info.php and place it in your web server’s document root directory. Access this script via your web browser (e.g., http://localhost/info.php). Look for the section titled mysqli in the output. If you see it, the MySQLi extension is already installed.
Installing MySQLi on Windows
If the MySQLi extension is not installed, you can follow these steps to install it.
Locate php.ini: Find the php.ini file, which is the configuration file for PHP. This file is typically located in the PHP installation directory.
Enable MySQLi Extension: Open php.ini in a text editor and find the line that references mysqli. Uncomment this line by removing the semicolon at the beginning:
;extension=mysqli
Change it to:
extension=mysqli
Restart Web Server: After saving the changes to php.ini, restart your web server (e.g., Apache or Nginx) to apply the changes.
Installing MySQLi on macOS
On macOS, you can enable the MySQLi extension by following these steps:
Locate php.ini: Find the php.ini file. If it doesn’t exist, you can create it from the default configuration file php.ini.default.
sudo cp /etc/php.ini.default /etc/php.ini
Enable MySQLi Extension: Open php.ini in a text editor and uncomment the line for the MySQLi extension:
extension=mysqli
Restart Web Server: Restart the web server to apply the changes.
Installing MySQLi on Linux
For Linux distributions such as Ubuntu, you can install and enable the MySQLi extension using the package manager.
Update Package List: Ensure your package list is up-to-date.
sudo apt update
Install PHP MySQLi Extension: Install the MySQLi extension using the following command:
sudo apt install php-mysqli
Restart Web Server: Restart the web server to apply the changes.
sudo systemctl restart apache2
Verifying Installation
After enabling the MySQLi extension, you can verify the installation by running the info.php script again and checking for the mysqli section. If it appears, the extension is successfully installed and enabled.
Configuring PHP to Use MySQLi
Once the MySQLi extension is installed and enabled, you can start using it in your PHP scripts. Here are some examples of how to use MySQLi to interact with a MySQL database.
Connecting to a MySQL Database
Using MySQLi, you can connect to a MySQL database either procedurally or using object-oriented programming.
Procedural Style
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
// Create connection
$connection = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$connection) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
mysqli_close($connection);
?>
Object-Oriented Style
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
// Create connection
$mysqli = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
echo "Connected successfully";
$mysqli->close();
?>
Performing CRUD Operations
Creating a Table
You can create a new table in the database using MySQLi
<?php
$mysqli = new mysqli($servername, $username, $password, $dbname);
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$sql = "CREATE TABLE Users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";
if ($mysqli->query($sql) === TRUE) {
echo "Table Users created successfully";
} else {
echo "Error creating table: " . $mysqli->error;
}
$mysqli->close();
?>
Inserting Data
You can insert data into the table using MySQLi.
<?php
$mysqli = new mysqli($servername, $username, $password, $dbname);
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$sql = "INSERT INTO Users (username, email) VALUES ('JohnDoe', 'john@example.com')";
if ($mysqli->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $mysqli->error;
}
$mysqli->close();
?>
Retrieving Data
You can retrieve data from the table using MySQLi.
<?php
$mysqli = new mysqli($servername, $username, $password, $dbname);
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$sql = "SELECT id, username, email FROM Users";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
}
} else {
echo "0 results";
}
$mysqli->close();
?>
Updating Data
You can update existing records in the table using MySQLi.
<?php
$mysqli = new mysqli($servername, $username, $password, $dbname);
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$sql = "UPDATE Users SET email='jane@example.com' WHERE username='JohnDoe'";
if ($mysqli->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $mysqli->error;
}
$mysqli->close();
?>
Deleting Data
You can delete records from the table using MySQLi.
<?php
$mysqli = new mysqli($servername, $username, $password, $dbname);
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
$sql = "DELETE FROM Users WHERE username='JohnDoe'";
if ($mysqli->query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $mysqli->error;
}
$mysqli->close();
?>
Conclusion
Installing and configuring the MySQLi extension for PHP is a crucial step for developers who need to interact with MySQL databases. MySQLi offers a range of enhanced features over the older MySQL extension, making it a powerful tool for building robust, secure, and efficient applications. By following the steps outlined in this article, you can ensure that your PHP environment is properly set up to use MySQLi, allowing you to take full advantage of its capabilities in your projects.