How to Connect PHP to a MySQL Database Using MySQLi

You need your website to connect to a database for various operations such as selecting, inserting, updating, or deleting data. In this tutorial, I’ll guide you through the steps to connect to MySql database and perform basic database operations.

Step 1: Create a MySQL Database

Before connecting, you need to have a MySQL database. You can create one using phpMyAdmin or MySQL command line. Here’s how to create a database named my_database:

CREATE DATABASE my_database;
    

Step 2: Create a Database Table

Next, create a table within your database. For this example, we will create a users table:

USE my_database;

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

Step 3: Connect to the MySQL Database Using MySQLi

Now that we have our database and table ready, we can connect to the database using PHP. Below is the PHP code to establish a connection:

connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
    

Explanation:

• mysqli: This is the MySQLi class used for establishing the connection.
• connect_error: This property checks if there was an error during the connection process.
• die(): This function terminates the script if the connection fails and displays the error message.

Step 4: Perform Database Operations

After establishing a successful connection, you can perform various operations. Below are examples for inserting and selecting data:

Insert Data into the Users Table

$sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
    

Select Data from the Users Table

$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";
}
    

Step 5: Close the Database Connection

It’s good practice to close the connection once you are done with the database operations:

$conn->close();
    

Connecting PHP to a MySQL database using MySQLi is simple and efficient. This tutorial provided a basic overview of how to establish a connection, perform basic operations, and close the connection. With this knowledge, you can begin building dynamic web applications that interact with databases.