How to Connect to MySQL Using PHP

April 23, 2024

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.

how to connect to mysql using php

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.

Note: The mysql_connect is a previously popular extension for connecting to a MySQL database. The extension is deprecated as of PHP version 5.5.0 and removed in version 7.0.0. Use one of the two previously suggested methods instead.

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
PHP modules MySQLi and PDO installed terminal output

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";

?>
PHP MySQLi connection object oriented example code

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";

?>
PHP MySQLi connection procedural example code

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();
}

?>
PHP PDO MySQL connection example code

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
MySQL service active(running) terminal output

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. 

Note: To create a new user and grant privileges, see our guide on how to create a new MySQL user and grant privileges. If the user already exists, see how to show user privileges.

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.

Was this article helpful?
YesNo
Milica Dancuk
Milica Dancuk is a technical writer at phoenixNAP with a passion for programming. With a background in Electrical Engineering and Computing, coupled with her teaching experience, she excels at simplifying complex technical concepts in her writing.
Next you should read
How To Install PHP 7, 7.2 & 7.3 On CentOS 7
May 24, 2019

PHP is a programming language that's often used to automate server tasks. It's part of the LAMP (Linux,...
Read more
How to Connect to MySQL from Windows Command Line
April 23, 2024

MySQL is a popular and open-source relational database application. Many servers make use of MySQL, to...
Read more
How To Install PHP On Ubuntu 20.04 or 22.04
November 24, 2022

PHP is a script-based server-side programming language. PHP is often used to automate server tasks and is the...
Read more
How to Install MySQL 8.0 in Ubuntu 18.04
December 12, 2018

MySQL is an open-source relational database server tool for Linux operating systems. It is widely used in...
Read more