How to connect to a MySQL database in PHP

Create a PHP script that connects to a MySQL database using the PDO extension. Use environment variables to store credentials and prevent hardcoded secrets. This guide targets PHP 8.3.x, MySQL 8.0.x, and Ubuntu 24.04.

Prerequisites

• Ubuntu 24.04 or Debian 12 server
• PHP 8.3.x installed with PDO MySQL extension enabled
• MySQL 8.0.x or MariaDB 10.11 installed and running
• A database user created with specific privileges (SELECT, INSERT, UPDATE, DELETE)
• Access to the server via SSH

Step 1: Create the database and user

Log in to the MySQL server using the root account to create a new database and a dedicated user. This ensures your application does not run with root privileges.

mysql -u root -p

Enter the root password when prompted. Run the following SQL commands inside the MySQL prompt:

CREATE DATABASE app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'YourSecurePassword123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'localhost';
FLUSH PRIVILEGES;

You will see a message like "Query OK" for each command, confirming the objects were created successfully.

Step 2: Configure environment variables

Store your database credentials in environment variables instead of hardcoding them into the script. This prevents secrets from appearing in version control systems or server logs.

export DB_HOST="localhost"
export DB_NAME="app_db"
export DB_USER="app_user"
export DB_PASS="YourSecurePassword123!"

Run these commands in your terminal session. If you are deploying via a web server like Apache or Nginx, you should also create a file named `.env` in your project root and load it using the `vlucas/phpdotenv` package or a similar loader, but for this tutorial, we assume a simple CLI or local environment where `putenv()` is sufficient.

Step 3: Create the connection script

Create a new file named `db_connect.php` in your project directory. This file will contain the logic to establish a connection using the PDO extension. PDO is preferred over MySQLi because it supports prepared statements by default, which helps prevent SQL injection attacks.

db_connect.php

Add the following code to the file. This script checks if the PDO extension is loaded and throws a fatal error if it is missing.

<?php

// Load environment variables if using a .env file
// require_once __DIR__ . '/vendor/autoload.php';
// $dotenv = Dotenv\Dotenv::createImmutable(__DIR__);
// $dotenv->load();

// Get connection parameters from environment
$host = getenv('DB_HOST') ?: 'localhost';
$db   = getenv('DB_NAME') ?: 'app_db';
$user = getenv('DB_USER') ?: 'app_user';
$pass = getenv('DB_PASS') ?: '';
$charset = 'utf8mb4';

// Build the DSN (Data Source Name)
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";

// Set the PDO error mode to exception
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    // Create a new PDO instance
    $pdo = new PDO($dsn, $user, $pass, $options);

    // Optional: Log the connection success (remove in production)
    error_log("Database connected successfully");

    echo "Connection successful. Database: " . $db . "\n";
} catch (PDOException $e) {
    // Log the full error for debugging, but show a generic message
    error_log("Connection failed: " . $e->getMessage());
    echo "Connection failed. Please check your credentials.\n";
}
?>

Step 4: Execute a query to test the connection

Modify the script to run a simple SELECT query. This confirms that the connection is not only established but also that the database has read permissions.

<?php
// ... (previous connection code) ...

try {
    // Execute a simple query
    $stmt = $pdo->query("SELECT DATABASE() as current_db");
    $result = $stmt->fetch();

    echo "Connected to: " . $result['current_db'] . "\n";
} catch (PDOException $e) {
    echo "Query failed: " . $e->getMessage() . "\n";
}
?>

Save the file and run it from the command line using PHP CLI.

php db_connect.php

You should see output similar to:

Connected to: app_db

If you see an error message, proceed to the Troubleshooting section.

Verify the installation

Run the script again to ensure the connection remains stable. Open your web browser and navigate to the URL where `db_connect.php` is hosted. You should see the text "Connected to: app_db". If the script is protected by a web server, you might only see the output in the server logs. Check the Apache or Nginx error log to confirm the connection details.

Troubleshooting

Error: "SQLSTATE[HY000] [1045] Access denied for user 'app_user'@'localhost'

This error occurs when the username or password is incorrect, or the user lacks the necessary privileges. Verify the credentials in your environment variables match the user created in MySQL. Ensure the user was granted permissions for the specific database name.

Error: "SQLSTATE[42000] [42000] [1049] Unknown database 'app_db'

This indicates the database name defined in your environment variable does not exist on the server. Log in to MySQL and run `SHOW DATABASES;` to list available databases. Create the missing database or update the `DB_NAME` variable in your script.

Error: "PDO driver not loaded"

This means the PHP PDO MySQL extension is not enabled. Run the following command to enable it:

sudo dpkg-reconfigure php-pdo-mysql

Restart the PHP-FPM service or Apache after making changes:

sudo systemctl restart php8.3-fpm

Error: "Connection refused"

This usually means the MySQL service is not running. Check the status of the MySQL service:

sudo systemctl status mysql

If the service is inactive, start it with:

sudo systemctl start mysql

If you are connecting from a different host than localhost, ensure the MySQL user has remote access privileges and that the `bind-address` in `/etc/mysql/mysql.conf.d/mysqld.cnf` allows connections from your client IP. For local development, ensure the `DB_HOST` variable is set to `127.0.0.1` or `localhost`.