Connect MySQL Database

Connecting a MySQL database to your PHP application is the very first step in building any dynamic, data-driven website. PHP provides two main ways to communicate with MySQL - the MySQLi extension and PDO. In this tutorial we will use MySQLi (MySQL Improved), which offers a procedural API that is easy to understand and widely used.

Rather than writing raw connection code everywhere, we will build a clean connection.php file with five reusable helper functions that you can include in any PHP page of your project.

What is MySQLi?

MySQLi stands for MySQL Improved. It was introduced in PHP 5 as a replacement for the older mysql_* functions which are now deprecated and removed. MySQLi supports:

  • Both procedural and object-oriented coding styles
  • Prepared statements to prevent SQL injection attacks
  • Multiple statement execution
  • Transaction support
  • Character set / encoding configuration
Note: The older mysql_connect() function was removed in PHP 7. Always use mysqli_connect() or PDO for all new projects.

Prerequisites

Before you begin, make sure you have the following setup:

  • PHP 7.4 or higher (PHP 8.x recommended)
  • MySQL 5.7 or higher (or MariaDB)
  • A local server like XAMPP, WAMP, or Laragon (or a live web host)
  • A MySQL database already created (via phpMyAdmin or the MySQL CLI)

Creating the connection.php File

Create a new file named connection.php in your project root (or inside a config/ folder). This file will hold all database-related helper functions. You will include or require this file on every page that needs to access the database.

Step 1 – db_connect() : Establish the Database Connection

The db_connect() function creates a connection to your MySQL server and returns the connection object. It uses a static variable to ensure that only one connection is created per request — this is a lightweight form of connection pooling.

How it works

  • static $conn — keeps the connection alive inside the function across multiple calls without creating a new connection every time.
  • mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT) — tells MySQLi to throw exceptions on errors instead of silently failing, which makes debugging much easier.
  • mysqli_connect() — opens the connection using host, username, password, and database name.
  • mysqli_connect_errno() — checks if the connection failed and stops execution with a clear error message.
  • After connecting, mysqli_set_charset($connect, "utf8mb4") is called to ensure all data is handled in UTF-8 with full emoji support.
connection.php – db_connect()
<?php
function db_connect()
{
    static $conn;
    $servername = "localhost";
    $username   = "root";
    $password   = "";           // Your MySQL password
    $dbname     = "your_db_name"; // Replace with your database name

    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

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

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

// Global connection + charset setup
$connect = db_connect();
mysqli_set_charset($connect, "utf8mb4");
Why utf8mb4? The utf8mb4 charset supports the full Unicode character set including emojis (4-byte characters), while the older utf8 in MySQL only supports 3-byte characters. Always use utf8mb4 for modern applications.

Step 2 – db_query() : Run Prepared Queries

The db_query() function prepares a SQL query using MySQLi prepared statements. Instead of executing the query directly, it prepares it and returns a statement object ($stmt) which you can then bind parameters to and execute — keeping your application safe from SQL injection attacks.

connection.php – db_query()
function db_query($query)
{
    $connection = db_connect();
    $result = mysqli_prepare($connection, $query);
    return $result;
}

Example – SELECT query

example_select.php
<?php
require_once "connection.php";

// Prepare the query
$stmt = db_query("SELECT id, name, email FROM users WHERE status = ?");

// Bind parameter (s = string)
$status = "active";
mysqli_stmt_bind_param($stmt, "s", $status);

// Execute
mysqli_stmt_execute($stmt);

// Get result
$result = mysqli_stmt_get_result($stmt);

while ($row = mysqli_fetch_assoc($result)) {
    echo $row['name'] . " – " . $row['email'] . "<br>";
}

mysqli_stmt_close($stmt);

Example – INSERT query

example_insert.php
<?php
require_once "connection.php";

// Prepare INSERT query
$stmt = db_query("INSERT INTO users (name, email, password) VALUES (?, ?, ?)");

$name     = "John Doe";
$email    = "john@example.com";
$password = password_hash("secret123", PASSWORD_BCRYPT);

// Bind parameters (s = string)
mysqli_stmt_bind_param($stmt, "sss", $name, $email, $password);

// Execute
mysqli_stmt_execute($stmt);

echo "New user added successfully!";
mysqli_stmt_close($stmt);
Security tip: Never place user input directly into a SQL string. Always use prepared statements with mysqli_stmt_bind_param() to prevent SQL injection. The ? placeholder ensures user data is never interpreted as SQL code.

Step 3 – db_query_last_id() : Get the Last Inserted ID

After an INSERT query, you often need to know the auto-incremented ID of the newly inserted row — for example, to redirect to a profile page or link a related record. The db_query_last_id() function retrieves this ID from the statement object.

connection.php – db_query_last_id()
function db_query_last_id($stmt)
{
    $last_id = mysqli_stmt_insert_id($stmt);
    return $last_id;
}

Usage Example

example_last_id.php
<?php
require_once "connection.php";

$stmt = db_query("INSERT INTO users (name, email) VALUES (?, ?)");

$name  = "Jane Smith";
$email = "jane@example.com";
mysqli_stmt_bind_param($stmt, "ss", $name, $email);
mysqli_stmt_execute($stmt);

// Get the new row's ID
$new_id = db_query_last_id($stmt);
echo "New user created with ID: " . $new_id;

mysqli_stmt_close($stmt);

Step 4 – db_error() : Retrieve Statement Errors

The db_error() function returns the error message from a prepared statement. This is useful during development to diagnose why a query is failing. It prepares the given SQL query and returns any associated error string.

connection.php – db_error()
function db_error($stmt)
{
    $connection = db_connect();
    return mysqli_stmt_error(mysqli_prepare($connection, $stmt));
}
Development vs Production: Display detailed error messages only during development. On a live/production server, log errors to a file instead of showing them in the browser to avoid exposing sensitive database information.

Step 5 – db_close() : Close a Statement

Once you are done with a prepared statement, it is good practice to free the resources by closing it. The db_close() function closes the statement to release memory on the server.

connection.php – db_close()
function db_close($stmt)
{
    $connection = db_connect();
    mysqli_stmt_close(mysqli_prepare($connection, $stmt));
}

Full connection.php File

Here is the complete connection.php file with all five helper functions combined, ready to copy into your project:

connection.php – Complete File
<?php

/**
 * connection.php
 * Reusable MySQL database helper functions using MySQLi.
 * Include this file on any page that needs database access.
 */

/**
* Establish a MySQL database connection.
* Uses a static variable so only one connection is made per request.
* @return mysqli The active connection object.
*/

function db_connect()
{
    static $conn;
    $servername = "localhost";
    $username   = "root";
    $password   = "";             // Your MySQL password
    $dbname     = "your_db_name"; // Replace with your database name

    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

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

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

// Global connection with UTF-8 charset
$connect = db_connect();
mysqli_set_charset($connect, "utf8mb4");

/**
* Prepare a SQL query and return the statement object.
* @param  string $query SQL query with ? placeholders.
* @return mysqli_stmt Prepared statement object.
*/

function db_query($query)
{
    $connection = db_connect();
    $result = mysqli_prepare($connection, $query);
    return $result;
}

/**
* Get the auto-generated ID from the last INSERT query.
* @param  mysqli_stmt  $stmt The executed statement object.
* @return int Last inserted row ID.
*/

function db_query_last_id($stmt)
{
    $last_id = mysqli_stmt_insert_id($stmt);
    return $last_id;
}

/**
* Return the error text from a prepared statement. 
* @param string $stmt SQL query string.
* @return string Error message string.
*/

function db_error($stmt)
{
    $connection = db_connect();
    return mysqli_stmt_error(mysqli_prepare($connection, $stmt));
}

/**
* Close a prepared statement to free resources. 
* @param string $stmt SQL query string.
*/

function db_close($stmt)
{
    $connection = db_connect();
    mysqli_stmt_close(mysqli_prepare($connection, $stmt));
}

How to Use These Functions in Your Pages

To use the helper functions on any PHP page, simply include connection.php at the top using require_once. Here is a complete example showing how to fetch users from a users table and display them in an HTML table:

users_list.php
<?php
require_once "connection.php";
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Users List</title>
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css">
</head>
<body>
<div class="container mt-4">
  <h2>All Users</h2>
  <table class="table table-bordered table-striped">
    <thead class="table-dark">
      <tr>
        <th>ID</th><th>Name</th><th>Email</th>
      </tr>
    </thead>
    <tbody>
      <?php
        // Prepare and execute the query
        $stmt = db_query("SELECT id, name, email FROM users ORDER BY id DESC");
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);

        if (mysqli_num_rows($result) > 0) {
            while ($row = mysqli_fetch_assoc($result)) {
                echo "<tr>";
                echo "<td>" . $row['id'] . "</td>";
                echo "<td>" . htmlspecialchars($row['name']) . "</td>";
                echo "<td>" . htmlspecialchars($row['email']) . "</td>";
                echo "</tr>";
            }
        } else {
            echo "<tr><td colspan='3'>No users found.</td></tr>";
        }
        mysqli_stmt_close($stmt);
      ?>
    </tbody>
  </table>
</div>
</body>
</html>

Function Reference

A quick reference of all five helper functions defined in connection.php:

Function Parameter Returns Description
db_connect() — mysqli Opens and returns the database connection. Reuses the same connection via a static variable.
db_query($query) string $query mysqli_stmt Prepares a SQL query and returns the statement object ready for parameter binding.
db_query_last_id($stmt) mysqli_stmt $stmt int Returns the auto-incremented ID of the last inserted row.
db_error($stmt) string $stmt string Returns the error message associated with a prepared statement.
db_close($stmt) string $stmt void Closes a prepared statement to free server resources.

Best Practices

Follow these tips to keep your PHP-MySQL code secure, clean, and maintainable:

Always use Prepared Statements
Never concatenate user input directly into SQL strings. Use ? placeholders and mysqli_stmt_bind_param() to prevent SQL injection.
Store credentials in a config file
Move your database username, password and name to a separate config.php file and keep it outside the web root on production servers.
Always use utf8mb4 charset
Set the charset to utf8mb4 on both the connection and your MySQL database tables to correctly handle all Unicode characters including emojis.
Enable error reporting during development
Use mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT) while developing so that MySQLi throws exceptions on every error instead of failing silently.

 

Summary

In this tutorial, you learned:

  • What MySQLi is and why it is the recommended way to connect MySQL in PHP.
  • How to write a reusable db_connect() function using a static variable for efficient connection management.
  • How db_query() wraps mysqli_prepare() to keep all queries safe with prepared statements.
  • How to retrieve the last inserted row ID using db_query_last_id().
  • How to check for errors with db_error() and free resources with db_close().
  • How to include connection.php in other pages with require_once and use the functions for SELECT and INSERT operations.
Next Steps: Now that your database is connected, check out our tutorial on CRUD Operations with PHP & MySQL – The Right Way to learn how to Create, Read, Update and Delete records using these same helper functions.