Connecting PHP with MySQL (MySQLi)

Introduction

You now know what MySQL is and how to write basic SQL commands. But those commands are only useful to a PHP application once PHP can actually talk to the database. That is exactly what the mysqli extension does.

In this lesson you will connect a PHP script to a MySQL database, run a query, and pull the results back into PHP variables you can loop over and display.

What is MySQLi?

MySQLi ("MySQL Improved") is a PHP extension built specifically for communicating with MySQL databases. It replaced the older, now-removed `mysql_*` functions and added support for prepared statements, transactions, and an object-oriented interface, among other improvements.

MySQLi is enabled by default in most PHP installations, including XAMPP, so you generally do not need to install anything extra to use it.

Procedural vs Object-Oriented Style

MySQLi offers two equivalent ways of doing the exact same work. The procedural style calls global functions like `mysqli_connect()` and passes the connection around as an argument. The object-oriented style creates a `mysqli` object with `new mysqli(...)` and calls methods directly on it.

Both styles are fully supported and equally valid. This lesson shows both, but most modern PHP codebases lean toward the object-oriented style since it reads more naturally and pairs well with other OOP code.

Connecting to MySQL

To connect, you need four pieces of information: the database host (usually "localhost" for local development), a username, a password, and the name of the database you want to use.

<?php
$host = "localhost";
$user = "root";
$password = "";
$database = "school";

$conn = mysqli_connect($host, $user, $password, $database);
?>

The object-oriented equivalent creates a new `mysqli` instance with the same four arguments.

<?php
$host = "localhost";
$user = "root";
$password = "";
$database = "school";

$conn = new mysqli($host, $user, $password, $database);
?>

Checking for Connection Errors

Connections can fail — the wrong password, MySQL not running, or a typo in the database name. Always check whether the connection succeeded before running any queries against it.

<?php
$conn = new mysqli("localhost", "root", "", "school");

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

echo "Connected successfully!";
?>

Connected successfully!

The procedural equivalent uses `mysqli_connect_error()` after checking whether `mysqli_connect()` returned false.

<?php
$conn = mysqli_connect("localhost", "root", "", "school");

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
?>

Running a Query

Once connected, you send SQL statements to the database with `mysqli_query()` (or the `->query()` method). It returns a result set for SELECT queries, or a simple true/false for INSERT, UPDATE, and DELETE.

<?php
$conn = new mysqli("localhost", "root", "", "school");

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

$sql = "SELECT id, name, course FROM students";
$result = $conn->query($sql);
?>

Fetching Results

`fetch_assoc()` pulls one row at a time from the result set as an associative array, keyed by column name. Looping while `fetch_assoc()` returns a row is the most common pattern for displaying query results.

<?php
while ($row = $result->fetch_assoc()) {
    echo $row["name"] . " - " . $row["course"] . "\n";
}
?>

Amit Verma - PHP Development
Sara Khan - Web Design
Rahul Singh - Database Systems

If you would rather have every row at once instead of looping manually, `fetch_all(MYSQLI_ASSOC)` returns the entire result set as an array of associative arrays.

<?php
$students = $result->fetch_all(MYSQLI_ASSOC);

foreach ($students as $student) {
    echo $student["name"] . "\n";
}
?>

Amit Verma
Sara Khan
Rahul Singh

Closing the Connection

Once you are done with the database, close the connection to free up its resources. PHP does this automatically when a script ends, but it is good practice to close it explicitly, especially in longer-running scripts.

<?php
$conn->close();
// Procedural equivalent: mysqli_close($conn);
?>

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

The mysqli extension is the bridge between your PHP code and a MySQL database: connect, check for errors, run a query, fetch the results, and close the connection.

In this lesson, you learned how to open a MySQLi connection, verify it succeeded, run a query, and read back the rows it returned. Next, you will put these building blocks to work performing full CRUD operations against a real table.