Connecting PHP with MySQL using MySQLi

Table of Contents

• What is MySQLi?
• Why Use MySQLi for MySQL Connections?
• Setting Up MySQL and PHP for MySQLi
• Connecting to MySQL using MySQLi
  • Using MySQLi Object-Oriented Method
  • Using MySQLi Procedural Method
• Executing SQL Queries with MySQLi
  • SELECT Query with MySQLi
  • INSERT Query with MySQLi
  • UPDATE Query with MySQLi
  • DELETE Query with MySQLi
• Error Handling in MySQLi
• Closing Database Connection with MySQLi
• Best Practices for Using MySQLi
• Summary

What is MySQLi?

MySQLi (MySQL Improved) is a PHP extension that allows developers to interact with MySQL databases. It was introduced to provide a more advanced and secure interface to MySQL, offering improvements over the original mysql extension, which was deprecated in PHP 5.5.0. The mysqli extension provides both a procedural and object-oriented interface for working with MySQL databases.

Unlike the older mysql extension, mysqli supports more advanced features such as:

• Prepared Statements: Protects against SQL injection.
• Transactions: Allows multiple queries to be executed as a single unit.
• Stored Procedures: Support for executing stored procedures within the database.
• Multiple Statements: Allows sending multiple SQL queries in one request.

In this module, we will focus on using the object-oriented and procedural methods of connecting PHP to MySQL using the MySQLi extension.

Why Use MySQLi for MySQL Connections?

There are several reasons why MySQLi is preferred for connecting to MySQL databases over other methods, such as mysql or PDO:

1. Improved Security: MySQLi supports prepared statements, which protect your application from SQL injection attacks, a common security risk in web applications.
2. Performance: MySQLi is optimized for working with MySQL databases and supports features that improve performance, like persistent connections and stored procedures.
3. Multiple Query Support: Unlike the older mysql extension, MySQLi allows multiple queries to be executed in one call.
4. Support for MySQL-Specific Features: MySQLi offers built-in support for MySQL features like transactions, stored procedures, and more.

Setting Up MySQL and PHP for MySQLi

Before you can connect PHP to MySQL using MySQLi, ensure that your server or local development environment is properly configured. If you’re using a local environment, install XAMPP, WAMP, or MAMP, which come pre-configured with PHP and MySQL.

After installation:

1. Start Apache and MySQL: These services must be running to access PHP and MySQL.
2. Access phpMyAdmin: Use phpMyAdmin or the MySQL command line to manage databases.
3. Create a MySQL Database: For demonstration purposes, create a sample database like sample_db and a table, users, with some fields.

CREATE DATABASE sample_db;
USE sample_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

Now, you’re ready to start integrating PHP with MySQL using the MySQLi extension.

Connecting to MySQL using MySQLi

1. Using MySQLi Object-Oriented Method

The object-oriented approach is the preferred method when working with MySQLi, as it offers better organization and flexibility.

<?php
// Database credentials
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "sample_db";

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

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

echo "Connected successfully!";
?>

In the code above:

• We create a new mysqli object that establishes a connection to the database.
• The connect_error method checks if the connection was successful. If not, it prints an error message.
• If the connection is successful, it prints “Connected successfully”.

2. Using MySQLi Procedural Method

The procedural method is similar to the object-oriented approach but uses procedural-style functions rather than object methods.

<?php
// Database credentials
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "sample_db";

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

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

echo "Connected successfully!";
?>

In the procedural method:

• The mysqli_connect() function is used to establish a connection.
• The mysqli_connect_error() function checks if the connection was successful.

Both methods perform the same task; it’s a matter of preference whether to use the object-oriented or procedural style.

Executing SQL Queries with MySQLi

Once connected, you can use MySQLi to interact with the database and perform various CRUD operations.

1. SELECT Query with MySQLi

A SELECT query retrieves data from the database. The example below demonstrates how to fetch data from the users table.

<?php
$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Output data of each row
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 results";
}
?>

This code:

• Executes the SELECT query using the query() method.
• Iterates over the results using fetch_assoc() to display the id, name, and email fields.

2. INSERT Query with MySQLi

The INSERT query allows you to insert data into a database.

<?php
$sql = "INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
?>

This code:

• Executes the INSERT query using the query() method.
• Checks if the query was successful and displays a success or error message.

3. UPDATE Query with MySQLi

To update data in the database, use the UPDATE query.

<?php
$sql = "UPDATE users SET email='[email protected]' WHERE name='John Doe'";

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
?>

This code:

• Updates the email field of the user where the name is ‘John Doe’.

4. DELETE Query with MySQLi

To delete data from the database, use the DELETE query.

<?php
$sql = "DELETE FROM users WHERE name='John Doe'";

if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
?>

This code:

• Deletes the user record where the name is ‘John Doe’.

Error Handling in MySQLi

Error handling is critical when interacting with a MySQL database. MySQLi provides methods to check for errors when establishing a connection or executing queries. In the examples above, the connect_error and error methods are used to handle any connection or query errors.

To ensure better error handling, you can also use mysqli_report() to enable MySQLi’s built-in error reporting.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

This will automatically throw exceptions for MySQLi errors, which can be caught using try-catch blocks.

Closing Database Connection with MySQLi

It is essential to close your database connection once you’re done with your queries to free up system resources. This can be done using the close() method:

$conn->close();

Best Practices for Using MySQLi

1. Use Prepared Statements: Always use prepared statements to prevent SQL injection. This is especially important when dealing with user input.
2. Validate User Input: Always validate and sanitize user inputs before using them in SQL queries.
3. Limit Query Results: Use the LIMIT keyword in SELECT queries to avoid overloading your application with too much data.
4. Use Transactions for Critical Queries: Use MySQLi’s transaction support to group multiple queries together and ensure data consistency.

Summary

In this module, we covered how to connect PHP to MySQL using the MySQLi extension, both via the object-oriented and procedural methods. We explored executing basic SQL queries like SELECT, INSERT, UPDATE, and DELETE with MySQLi, as well as handling errors and closing the database connection. Additionally, we discussed best practices for using MySQLi effectively in your PHP applications.