Every dynamic application eventually needs to store and retrieve real data — user accounts, products, orders — and that means talking to a database. A proper PHP MySQLi Connection is one of the most common ways to link your PHP scripts with a MySQL database using simple, procedural-style code.
This is Tutorial 7 of 11 in our PHP Advanced series. You will learn what MySQLi is, how to establish a PHP MySQLi Connection, handle connection errors, and run several practical queries against a real database. Every section includes a runnable code example — type each one out yourself and test it before moving to the next.
What is MySQLi?
MySQLi stands for MySQL Improved. It is a PHP extension designed specifically for working with MySQL databases, and it supports both procedural and object-oriented styles. This tutorial focuses on the procedural style, which reads much like the regular functions you already know from earlier tutorials.
Why Use MySQLi?
Older functions like mysql_connect() have been completely removed from modern PHP. A PHP MySQLi Connection replaces those outdated functions with a secure, actively maintained extension that also supports prepared statements, protecting your application against SQL injection attacks.
Setting Up a Practice Database
Before writing any connection code, open phpMyAdmin through your XAMPP control panel and create a database called school_db. Inside it, create a table called students using this SQL, which you can paste directly into the SQL tab in phpMyAdmin.
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
age INT
);
INSERT INTO students (name, age) VALUES
("Ahmed", 22),
("Sara", 20),
("Bilal", 24);Running this SQL gives you a real table with sample data, so every PHP MySQLi Connection example below actually returns visible results when you test it.
Example 1: Basic Connection with require_once
Using what you learned in the previous tutorial, store connection details in a shared config file, then require it wherever a PHP MySQLi Connection is needed.
<?php
// File: config.php
$host = "localhost";
$username = "root";
$password = "";
$dbname = "school_db";
?><?php
// File: connect.php
require_once "config.php";
$conn = mysqli_connect($host, $username, $password, $dbname);
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connection successful!";
?>Here, mysqli_connect() attempts the PHP MySQLi Connection, and the if statement checks whether it failed. If it did, mysqli_connect_error() returns the exact reason, helping you quickly identify what went wrong.
Example 2: Handling a Failed Connection
Let’s deliberately break the connection to see error handling in action. Try this with an incorrect database name.
<?php
$conn = mysqli_connect("localhost", "root", "", "wrong_db_name");
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "This line never runs if the connection failed.";
?>The die() function immediately stops script execution and prints a message, preventing the rest of your script from running against a database connection that does not actually exist.
Example 3: Running Your First SELECT Query
Once your PHP MySQLi Connection is established, run SQL queries using mysqli_query(), passing in the connection and the SQL statement as separate arguments.
<?php
require_once "config.php";
$conn = mysqli_connect($host, $username, $password, $dbname);
$result = mysqli_query($conn, "SELECT * FROM students");
while ($row = mysqli_fetch_assoc($result)) {
echo $row["name"] . " is " . $row["age"] . " years old.<br>";
}
?>Here, mysqli_fetch_assoc() pulls one row at a time from the result set as an associative array, letting the while loop print every student’s name and age until no rows remain.
Example 4: Fetching a Single Row
When you expect only one result, call mysqli_fetch_assoc() once instead of inside a loop.
<?php
require_once "config.php";
$conn = mysqli_connect($host, $username, $password, $dbname);
$result = mysqli_query($conn, "SELECT * FROM students WHERE id = 1");
$student = mysqli_fetch_assoc($result);
echo "Found: " . $student["name"];
?>Example 5: Counting Rows Before Looping
The mysqli_num_rows() function tells you how many rows a query returned, which is useful for checking whether any matching records actually exist before processing them.
<?php
require_once "config.php";
$conn = mysqli_connect($host, $username, $password, $dbname);
$result = mysqli_query($conn, "SELECT * FROM students WHERE age > 30");
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
echo $row["name"] . "<br>";
}
} else {
echo "No students found matching that age.";
}
?>Checking mysqli_num_rows() before looping is a common pattern in a real PHP MySQLi Connection workflow, since it avoids running a loop over an empty result set unnecessarily.
Example 6: Displaying Results in an HTML Table
Combining a PHP MySQLi Connection with an HTML table produces a much more realistic output than plain echo statements.
<?php
require_once "config.php";
$conn = mysqli_connect($host, $username, $password, $dbname);
$result = mysqli_query($conn, "SELECT * FROM students");
?>
<table border="1">
<tr><th>ID</th><th>Name</th><th>Age</th></tr>
<?php while ($row = mysqli_fetch_assoc($result)) { ?>
<tr>
<td><?php echo $row["id"]; ?></td>
<td><?php echo $row["name"]; ?></td>
<td><?php echo $row["age"]; ?></td>
</tr>
<?php } ?>
</table>This pattern of mixing PHP with HTML directly is extremely common in real projects, since it lets you build dynamic tables that automatically grow or shrink based on the actual database content.
Example 7: Closing the Connection
Once your script finishes working with the database, mysqli_close() properly closes the PHP MySQLi Connection and frees up server resources.
<?php
require_once "config.php";
$conn = mysqli_connect($host, $username, $password, $dbname);
$result = mysqli_query($conn, "SELECT * FROM students");
while ($row = mysqli_fetch_assoc($result)) {
echo $row["name"] . "<br>";
}
mysqli_close($conn);
?>For complete official documentation covering every MySQLi function and option, refer to the official PHP MySQLi manual.
Common Connection Errors
A failed PHP MySQLi Connection usually comes from one of a few common mistakes: an incorrect database name, wrong username or password, or MySQL not running at all in your XAMPP control panel. Always check the exact message returned by mysqli_connect_error(), since it usually names the specific problem directly.
What’s Next?
You now understand how to establish a PHP MySQLi Connection, handle errors safely, and run several types of queries using the procedural style. In the next tutorial, we will go much deeper with MySQLi, covering prepared statements and full CRUD operations — creating, reading, updating, and deleting data securely.
If you missed the previous lesson, check out Tutorial 6: PHP Include Require Guide, or explore all lessons in our PHP category.
Practice Exercise
Complete the following tasks to reinforce what you learned in this tutorial. Type and run every example — do not just read the code.
- Create the
school_dbdatabase andstudentstable using the SQL provided in this tutorial - Create a
config.phpfile storing your host, username, password, and database name - Write a PHP MySQLi Connection script using require_once and mysqli_connect(), checking for failure with die()
- Write a query using mysqli_query() and a while loop with mysqli_fetch_assoc() to display all student names and ages
- Use mysqli_num_rows() to check if any students exist with age greater than 21 before looping through them
- Build an HTML table that displays every student’s id, name, and age using a mixed PHP/HTML block like Example 6
- Close the connection properly using mysqli_close() at the end of your script
Bonus Challenge: Deliberately misspell the database name in your connection call, run the script, and confirm that mysqli_connect_error() displays a clear error message instead of crashing the page with a raw PHP fatal error. Then add a second query that counts the total number of students using mysqli_num_rows() and displays it above your table.
