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 (1) How to Connect to MySQL Using PHP (1)](https://i0.wp.com/phoenixnap.com/kb/wp-content/uploads/2021/04/connect-to-mysql-using-php.png)
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
![How to Connect to MySQL Using PHP (2) How to Connect to MySQL Using PHP (2)](https://i0.wp.com/phoenixnap.com/kb/wp-content/uploads/2024/04/php-modules-mysqli-and-pdo-installed-terminal-output.png)
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 themysqli
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 statusif ($conn->connect_error) { die("Connection failed. Reason: " . $conn->connect_error);}echo "Connected!\n";?>
![How to Connect to MySQL Using PHP (3) How to Connect to MySQL Using PHP (3)](https://i0.wp.com/phoenixnap.com/kb/wp-content/uploads/2024/04/php-mysqli-connection-object-oriented-example-code.png)
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 statusif (!$conn) { die("Connection failed. Reason: " . mysqli_connect_error());}echo "Connected!\n";?>
![How to Connect to MySQL Using PHP (4) How to Connect to MySQL Using PHP (4)](https://i0.wp.com/phoenixnap.com/kb/wp-content/uploads/2024/04/php-mysqli-connection-procedural-example-code.png)
The provided database connection details are parameters for themysqli_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();}?>
![How to Connect to MySQL Using PHP (5) How to Connect to MySQL Using PHP (5)](https://i0.wp.com/phoenixnap.com/kb/wp-content/uploads/2024/04/php-pdo-mysql-connection-example-code.png)
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
![How to Connect to MySQL Using PHP (6) How to Connect to MySQL Using PHP (6)](https://i0.wp.com/phoenixnap.com/kb/wp-content/uploads/2024/04/mysql-service-active-running-terminal-output.png)
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 onhow to fix the missing PHP MySQL extension in WordPress.