MySQL Connect

Connecting PHP to MySQL

In simple terms: Before PHP can read or save data, it must open a connection to the MySQL server — like dialing a phone number before you can talk. You provide the server's address, a username, a password, and the database to use. If anything is wrong (wrong password, server down), the connection fails and your code must handle that gracefully.

Technically: With the MySQLi extension, a connection is an instance of the mysqli class. Its constructor connects immediately. Since PHP 8.1, MySQLi reports errors by throwing mysqli_sql_exception by default. After connecting, the character set must be set to utf8mb4 so that all text — including Tamil, Hindi, and emoji — is transferred correctly.

A Basic Connection

<?php
$host = '127.0.0.1';
$username = 'gocourse_app';
$password = 'change-this-app-password';
$database = 'gocourse_shop';
$port = 3306;

$db = new mysqli($host, $username, $password, $database, $port);
$db->set_charset('utf8mb4');

echo "Connected to MySQL " . $db->server_info . "\n";
echo "Connection: " . $db->host_info . "\n";

Output (versions depend on your server)

Connected to MySQL 8.4.6
Connection: 127.0.0.1 via TCP/IP

How It Works

1. new mysqli(...) connects to the server with the given credentials and selects the database.
2. set_charset('utf8mb4') tells MySQL how text is encoded on this connection. Without it, non-English characters may be stored or displayed as ? or garbled text.
3. server_info shows the MySQL server version; host_info describes the connection type.

localhost vs 127.0.0.1

If localhost fails with "No such file or directory", PHP cannot find the socket file; using 127.0.0.1 (or configuring the socket path) usually solves it. With Docker, use the container or service name as the host.

Handling Connection Errors

Connections can fail for many reasons: wrong credentials, the server is not running, a firewall blocks the port. Never let the raw error reach the user — it may reveal the host, username, or database structure.

<?php
try {
    $db = new mysqli('127.0.0.1', 'gocourse_app', 'wrong-password', 'gocourse_shop');
    $db->set_charset('utf8mb4');
} catch (mysqli_sql_exception $e) {
    error_log('Database connection failed: ' . $e->getMessage() . ' (code ' . $e->getCode() . ')');
    echo "Sorry, the service is temporarily unavailable.\n";
}

Output

Sorry, the service is temporarily unavailable.

What Gets Logged

Database connection failed: Access denied for user 'gocourse_app'@'localhost' (using password: YES) (code 1045)

Common Error Codes

Error Reporting Mode

Since PHP 8.1, the default mode is MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT, which throws exceptions for failed connections and failed queries. In older versions, failures returned false with warnings. For code that may run on older versions, enable exceptions explicitly before connecting:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

Exceptions are the safest mode: a failed query can never be silently ignored.

Keeping Credentials Out of Code

Database credentials differ between development, staging, and production, and they must never be committed to version control. Read them from environment variables:

<?php
// config/database.php
return [
    'host' => getenv('DB_HOST') ?: '127.0.0.1',
    'port' => (int) (getenv('DB_PORT') ?: 3306),
    'database' => getenv('DB_DATABASE') ?: 'gocourse_shop',
    'username' => getenv('DB_USERNAME') ?: 'gocourse_app',
    'password' => getenv('DB_PASSWORD') ?: '',
];

Environment variables are set by the server configuration, the hosting platform, Docker, or a .env file loaded by a library such as vlucas/phpdotenv (see \$_ENV). The .env file must be listed in .gitignore.

A Reusable Connection Function

An application should open one connection per request and reuse it:

<?php
// src/db.php
function db(): mysqli
{
    static $connection = null;

    if ($connection === null) {
        $config = require __DIR__ . '/../config/database.php';

        $connection = new mysqli(
            $config['host'],
            $config['username'],
            $config['password'],
            $config['database'],
            $config['port'],
        );
        $connection->set_charset('utf8mb4');
    }

    return $connection;
}

<?php
// public/products.php
require __DIR__ . '/../src/db.php';

try {
    $result = db()->query('SELECT COUNT(*) AS total FROM products');
    echo 'Products in catalog: ' . $result->fetch_assoc()['total'] . "\n";
} catch (mysqli_sql_exception $e) {
    error_log($e->getMessage());
    http_response_code(500);
    echo "Sorry, something went wrong.\n";
}

Output (with three products in the table)

Products in catalog: 3

How It Works

1. The static variable keeps the connection between calls within the same request; the connection is created only the first time db() is called.
2. Every part of the application calls db() instead of creating new connections.
3. Errors are logged with details, while the visitor sees a generic message with status 500.

In larger applications, the connection is usually created by a dependency injection container and passed to the classes that need it.

Procedural Style

MySQLi also offers procedural functions, common in older code:

<?php
$db = mysqli_connect('127.0.0.1', 'gocourse_app', 'change-this-app-password', 'gocourse_shop');
mysqli_set_charset($db, 'utf8mb4');

echo mysqli_get_server_info($db) . "\n";
mysqli_close($db);

Both styles use the same connection object and behave identically. This tutorial uses the object-oriented style, which is more concise and consistent with modern PHP.

Closing Connections

PHP closes the connection automatically when the script ends. Calling $db->close() explicitly is only useful in long-running scripts (for example, a worker that should release the connection while it waits).

Persistent connections (prefixing the host with p:, as in 'p:127.0.0.1') keep connections open between requests to save connection time. They can cause problems — leftover transactions, locks, or session variables carried into the next request — so use them only when you understand the trade-offs; with modern MySQL on the same network, connection setup is usually fast enough.

Secure Connections to Remote Databases

When the database runs on another server (for example, a managed cloud database), the traffic travels over a network and should be encrypted with TLS:

<?php
$db = mysqli_init();
$db->ssl_set(null, null, '/etc/ssl/certs/db-ca.pem', null, null);
$db->real_connect('db.example.com', 'gocourse_app', getenv('DB_PASSWORD'), 'gocourse_shop', 3306, null, MYSQLI_CLIENT_SSL);
$db->set_charset('utf8mb4');

The CA certificate (provided by the database host) allows PHP to verify that it is talking to the real server.

Common Mistakes

Forgetting set_charset('utf8mb4')

Effect: Non-English text and emoji are stored or displayed incorrectly.

Fix: Set the character set immediately after connecting.

Showing Connection Errors to Users

Effect: Hostnames, usernames, and database names are exposed.

Fix: Catch mysqli_sql_exception, log the details, and show a generic message.

Hard-Coding Credentials

Effect: Passwords end up in version control and in every copy of the code.

Fix: Use environment variables.

Opening a New Connection for Every Query

Effect: Slow pages and exhausted connection limits.

Fix: Create one connection per request and reuse it.

Using the Old utf8 Character Set

Effect: MySQL's utf8 (an alias for utf8mb3) cannot store emoji and some characters.

Fix: Always use utf8mb4.

Best Practices

• Connect with new mysqli(...) and immediately call set_charset('utf8mb4').
• Rely on exceptions for errors, and handle them by logging and showing a safe message.
• Store credentials in environment variables and use a least-privilege database user.
• Reuse a single connection per request through a function or a DI container.
• Encrypt connections to remote database servers with TLS.

Official reference: https\://www.php.net/manual/en/mysqli.construct.php