How to Connect to MySQL Using PHP

Introduction

Connecting to MySQL using PHP is an essential web development task that enables access to a website database. It provides a way to communicate with the database and forward information to the front-end.

As a server-side scripting language, PHP has different ways to connect and interact with MySQL databases.

This guide shows how to connect to MySQL via PHP using various methods.

Prerequisites

• Command line access.
• MySQL database and credentials (database name, username, and password).
• A web server setup with PHP and MySQL. The guide uses the LAMP stack.

How to Connect to MySQL Database Using PHP

Several different extensions enable connecting to a MySQL database through PHP. The two most common ones are:

• PHP MySQLi.
• PHP Data Objects (PDO).

Each has advantages and is suitable for different use cases. The sections below show how to install and use these two extensions to connect to MySQL via PHP.

Install MySQLi and PDO

The extensions install alongside PHP if it was installed via a package manager. Check if they are installed with the following commands:

php -m | grep mysqli

php -m | grep pdo

The command outputs installed modules and uses grep to filter by name. If nothing prints to the terminal, the extensions are not installed.

To install them, update the package repository with the following apt command:

sudo apt update

Then, run the following to install MySQLi and PDO:

sudo apt install php-mysql php-mysqlnd php-pdo

The command also installs any required dependencies.

PHP MySQLi Script (Object-Oriented)

An object-oriented approach provides a clean, organized way to connect to a MySQL database. It's suitable for complex database operations and provides various error-handling mechanisms.

To create a connection using this method, instantiate a MySQLi object using the mysqli constructor. For example:

<?php

// Connection details
$server = "localhost";
$username = "myuser";
$password = "Mypassword123!";
$db = "mydatabase";

// Create connection
$conn = new mysqli($server, $username, $password, $db);

// Check connection status
if ($conn->connect_error) {
    die("Connection failed. Reason: " . $conn->connect_error);
}
echo "Connected!\n";

?>

The code snippet contains the connection object stored in the $conn variable. It initializes a new MySQLi object based on the provided database connection details. The connect_error property from the $conn object contains an error message, if any. The property helps verify the success of the connection.

PHP MySQLi Script (Procedural)

The procedural method is a straightforward way to connect to a MySQL database using PHP. It is simpler than the object-oriented approach but robust enough to provide various database operations.

To create a connection using PHP MySQLi using a procedural approach, use the mysquli_connect() function and pass the database connection details as function parameters. For example:

<?php

// Connection details
$server = "localhost";
$username = "myuser";
$password = "Mypassword123!";
$db = "mydatabase";

// Create connection
$conn = mysqli_connect($server, $username, $password, $db);

// Check connection status
if (!$conn) {
    die("Connection failed. Reason: " . mysqli_connect_error());
}
echo "Connected!\n";

?>

The provided database connection details are parameters for the mysqli_connect() function, which returns a connection object. It stores the connection in the $conn variable.

If the connection is not established, the code uses the mysqli_connect_error() function to return the error message.

PDO Script

The PHP PDO script is a database-independent, flexible interface for creating database connections. It enables linking to MySQL and other database systems (such as PostgreSQL) with minimal code changes.

To start a connection with a MySQL database using PHP PDO, see the following example script:

<?php

//Connection details
$server = "localhost";
$username = "myuser";
$password = "Mypassword123!";
$db = "mydatabase";

try {
    // Create connection
    $conn = new PDO("mysql:host=$server;dbname=$db", $username, $password);
    // Set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected!\n";
} catch(PDOException $e) {
    echo "Connection failed. Reason: " . $e->getMessage();
}

?>

The technique uses a data source name (DSN) string to state all the database details required for the connection. The connection details are passed to the PDO constructor to create a connection object stored in a variable ($conn). The connection is in a try-catch block to catch a PDO exception in case of an error.

The setAttribute() method sets the error mode to ERRMODE_EXCEPTION, which enables PDO to throw exceptions for errors.

How to Terminate PHP MySQL Connection

Closing a MySQL connection is essential for efficient resource management and security. The termination commands differ depending on how the connection was established:

• MySQLi (Object Oriented):

$conn->close();

• MySQLi (procedural):

mysqli_close($conn);

• PDO:

$conn = null;

Close a connection after finishing all necessary query executions and transactions to prevent any backdoor vulnerabilities or data leaks.

Connecting to MySQL via PHP: Common Errors

Errors can occur when connecting to MySQL via PHP for various reasons, leading to a failure in connecting to the MySQL database. The sections below address some common errors and provide potential solutions when connecting to MySQL in PHP.

Incorrect Credentials

Incorrect credentials cause an authentication failure error and prevent establishing a database connection. To resolve the error, ensure the database parameters are correct (server, username, password, or database name).

Unable to Connect to MySQL Server

This error occurs when PHP cannot connect to the MySQL server. Various issues can block a connection, including an incorrect server address or network/firewall issues.

The most common reason for this error is the MySQL server status. To see the status, check the MySQL service status using the following command:

sudo systemctl status mysql

The service should show as active (running). If not, start the MySQL service with:

sudo systemctl start mysql

Retry connecting to the MySQL server to see if the error persists.

Database Name Not Specified (PDO)

The database name not specified error is a PDO error that indicates the database name is missing from the DSN string. PHP PDO cannot identify the target database if the name is incorrect or not provided.

Double-check the DSN string to confirm the database name is present and correct to resolve the issue.

Insufficient Privileges

The provided database user requires access privileges to the database to establish a connection. If the user has insufficient privileges, MySQL denies access.

Ensure the permissions allow the user to perform the required MySQL commands (such as SELECT, UPDATE, INSERT, DELETE, etc.) for the specified database. 

Conclusion

This guide detailed two ways to connect to a MySQL database using PHP. Both MySQLi and PDO have advantages and enable connecting to a MySQL database. However, MySQLi is only compatible with MySQL databases, whereas PDO works with different DBMS.

If you're using WordPress to connect to MySQL via PHP, the MySQL extension may be missing or misconfigured. To fix it, check out our guide on how to fix the missing PHP MySQL extension in WordPress.